InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:23:30 GMT20http://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxIf the restrictions I mentioned don't apply, the following extension of Adam's code illustrates a single-step alternative:[i]Setup:[/i][code="sql"]USE tempdb;GOCREATE TABLE dbo.MasterRecord ( master_id INTEGER IDENTITY PRIMARY KEY, col CHAR(1) NOT NULL, );CREATE TABLE dbo.DetailRecord ( detail_id INTEGER IDENTITY NOT NULL, master_id INTEGER NOT NULL, col_desc VARCHAR(50) NOT NULL, ); CREATE TABLE dbo.SomeOtherTable ( col CHAR(1) NOT NULL, data VARCHAR(50) NOT NULL, ); INSERT dbo.SomeOtherTable (col, data) VALUES ('a','This is a better description');[/code][i]Single step solution:[/i][code="sql"]INSERT dbo.DetailRecord (master_id, col_desc)SELECT INS.master_id, INS.dataFROM ( MERGE dbo.MasterRecord MR USING ( SELECT SOT.col, SOT.data FROM (VALUES ('a')) New (data) JOIN dbo.SomeOtherTable SOT ON SOT.col = New.data ) MS ON (MS.col = MR.col) WHEN NOT MATCHED BY TARGET THEN INSERT (col) VALUES (MS.col) OUTPUT INSERTED.master_id, INSERTED.col, MS.data ) INS;[/code][i]Results and cleanup:[/i][code="sql"]SELECT * FROM dbo.MasterRecordSELECT * FROM dbo.DetailRecord-- CleanupDROP TABLE dbo.DetailRecord;DROP TABLE dbo.MasterRecord;DROP TABLE dbo.SomeOtherTable;[/code]Thu, 22 Jul 2010 02:18:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxWhile the OUTPUT clause does have limitations is it perfect for this type of action. You cannot directly insert into a table with keys as suggested, but you can insert the data into an intermediate local temp table. You can then use this table to drive your joins and insert into the detail table.[code]USE [tempdb]GOCREATE TABLE [Master](id INT IDENTITY PRIMARY KEY CLUSTERED,col CHAR(1))CREATE TABLE [Detail](Id INT IDENTITY, MID INT REFERENCES [Master](Id), Col_Descr VARCHAR(50));CREATE TABLE [SomeOtherTable](Col CHAR(1), Descr VARCHAR(50))INSERT INTO dbo.[SomeOtherTable] VALUES ('a','This is a better description')GOIF OBJECT_ID('tempdb.dbo.#M') IS NOT NULLBEGIN DROP TABLE #M;END;CREATE TABLE #M(ID INT PRIMARY KEY CLUSTERED,Col CHAR(1));INSERT INTO dbo.[Master] OUTPUT INSERTED.Id, INSERTED.Col INTO #MSELECT 'a'INSERT INTO dbo.Detail (MID,Col_Descr)SELECT M.ID, sot.DescrFROM #M mINNER JOIN dbo.[SomeOtherTable] sot ON m.[Col] = sot.ColSELECT * FROM dbo.[Master]SELECT * FROM dbo.[Detail]/*id col----------- ----1 aId MID Col_Descr----------- ----------- --------------------------------------------------1 1 This is a better description*/GO--cleanupDROP TABLE dbo.Detail DROP TABLE dbo.[Master]DROP TABLE dbo.[SomeOtherTable][/code]Wed, 21 Jul 2010 14:02:21 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxmpradeesh,It would help if you could provide some sample data to illustrate exactly what you want to happen.There are a number of alternatives.Be aware though that using the OUTPUT clause to directly insert rows to a permanent table has a few restrictions. Specifically, the destination table cannot:[li]Have any enabled triggers[/li][li]Have any CHECK constaints or enabled rules[/li][li]Participate in a FOREIGN KEY relationship (parent or child)[/li]Those restrictions often make it impossible to use the OUTPUT clause in this way. If the number of rows is small, consider using a table variable as an intermediate step. Even if those restrictions do not apply, you should know that your code will break if, for example, someone adds a trigger to the table at any point in the future.The sample data would help us to give you an accurate solution.PaulWed, 21 Jul 2010 00:42:30 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxYou can use Output clause for that task.Actually it is one of the features I love in t-sqlPlease check for examples at [url=http://www.kodyaz.com/articles/output-clause-sample-code-with-insert-update-delete-sql-server-2005.aspx]T-SQL OUTPUT Clause Sample[/url] and [url=http://www.kodyaz.com/articles/insert-into-two-tables-using-t-sql-output-clause.aspx]T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command[/url]Please also check the below code[code="sql"]/*CREATE TABLE IDTable1 ( Id int identity(1,1), username varchar(10))GOCREATE TABLE IDLogTable ( IDLogTable_Id int identity(1,1), IDTable1_Id int)*/insert into IDTable1output inserted.id into IDLogTable(IDTable1_Id)values ('eralper')[/code]I hope that helps,Tue, 20 Jul 2010 23:57:17 GMTEralperhttp://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxI'm something of a newby to the forums, but here's a suggestion:I haven't seen a way to join the inserted table in an OUTPUT clause (maybe I'm just missing it), but what if you OUTPUTted your INSERT results to a temp table or table variable and then executed a second insert statement that selects results from the temp table/table variable joined to your additional table?Tue, 20 Jul 2010 16:07:13 GMTpbarnthsonhttp://www.sqlservercentral.com/Forums/Topic955691-392-1.aspxQ1. Is it possible to use any other table apart from Inserted in the Output clause in an insert into a table.I have a master table with Identity key as primary key and want to use this identity key to insert into another table with additional columns form another table in a bulk insert t-sqlTue, 20 Jul 2010 09:25:44 GMTmpradeesh