imaginary dought in sql joins

aditya immadi

10y1.1kSQL

i have small dought .if im inserting record into DB (record may have multple values) and record will be inserted into 2 tables .in first table after record insertion i need to capture ID which is recently incremented (first table ID has Identity) .and i need to insert that value into second table in same time.is it possible without single join query .or without joins (which one is best).and i would like to done this in single code block execution.any leads

Thanks and Regards

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bikesh Srivastava9y ago
Yes 100%
just you need to create relational database with foreign key between two table.
it will insert in both table an can be get eaisly .
0
Manish Kumar9y ago
Here we go...................

Try below code

create table employee
(empid int,empname varchar(100),empcity varchar(100))
create table employee_details
(empid int,emp_depart varchar(100),emp_city varchar(100))
insert into employee (empid,empname,empcity) OUTPUT inserted.empid,inserted.empcity into employee_details (empid,emp_city)
values (1,'Manish','Delhi')
select * from employee
select * from employee_details
0
Ananth G10y ago
1. DECLARE @ID BIGINT
3. INSERT INTO dbo.TABLE_ID (Table_NAME)
4. SELECT 'Table_Products'
6. SELECT @ID = SCOPE_IDENTITY()
8. INSERT INTO dbo.Table_Products (ID, Product_Name)
9. SELECT @ID, 'SomeProduct'
use this way it is better then other way
+1

Nitin Sontakke10y ago

Notice the words "in a single button click" not the "in a single query" which are hugely different things.

You can write a stored procedure and do it. Nothing very complicated. It is being done routinely by several developers.

If you need further assistance, please do let me know.

aditya immadi10y ago

yes sir .i dont think i can achieve it with single query .but one of my friend achieved it.so i m trying it for past 2 hours .at last i m here

there is no concern about errors and all.he said while inserting 2 tables first table ID which incremented is captured and insert that one in to second table .in a single button click .

Amit Gupta10y ago

You can get the last id by using select Max (id) from table1

Nitin Sontakke10y ago

All you have to do is make it a part of the transaction and roll it back in case of the error. Please be aware that the identity values once incremented cannot be rolled back.

I will be surprised if it can be done in a single query.