imaginary dought in sql joins

Question

Hi 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

Ananth G · Answer

DECLARE @ID BIGINT INSERT INTO dbo.TABLE_ID (Table_NAME) SELECT 'Table_Products' SELECT @ID = SCOPE_IDENTITY() INSERT INTO dbo.Table_Products (ID, Product_Name) SELECT @ID, 'SomeProduct' use this way it is better then other way

Amit Gupta · Answer

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

Nitin Sontakke · Answer

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.

Bikesh Srivastava · Answer

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 .

Manish Kumar · Answer

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

aditya immadi · Answer

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 .

Nitin Sontakke · Answer

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.