July 20, 2010 at 9:25 am
Q1. 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-sql
July 20, 2010 at 4:07 pm
I'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?
July 20, 2010 at 11:57 pm
You can use Output clause for that task.
Actually it is one of the features I love in t-sql
Please check for examples at T-SQL OUTPUT Clause Sample and T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command
Please also check the below code
/*
CREATE TABLE IDTable1 (
Id int identity(1,1),
username varchar(10)
)
GO
CREATE TABLE IDLogTable (
IDLogTable_Id int identity(1,1),
IDTable1_Id int
)
*/
insert into IDTable1
output inserted.id into IDLogTable(IDTable1_Id)
values ('eralper')
I hope that helps,
July 21, 2010 at 12:42 am
mpradeesh,
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:
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.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 21, 2010 at 2:02 pm
While 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.
USE [tempdb]
GO
CREATE 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')
GO
IF OBJECT_ID('tempdb.dbo.#M') IS NOT NULL
BEGIN
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 #M
SELECT 'a'
INSERT INTO dbo.Detail (MID,Col_Descr)
SELECT M.ID, sot.Descr
FROM #M m
INNER JOIN dbo.[SomeOtherTable] sot ON m.[Col] = sot.Col
SELECT * FROM dbo.[Master]
SELECT * FROM dbo.[Detail]
/*
id col
----------- ----
1 a
Id MID Col_Descr
----------- ----------- --------------------------------------------------
1 1 This is a better description
*/
GO
--cleanup
DROP TABLE dbo.Detail
DROP TABLE dbo.[Master]
DROP TABLE dbo.[SomeOtherTable]
July 22, 2010 at 2:18 am
If the restrictions I mentioned don't apply, the following extension of Adam's code illustrates a single-step alternative:
Setup:
USE tempdb;
GO
CREATE 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');
Single step solution:
INSERT dbo.DetailRecord
(master_id, col_desc)
SELECT INS.master_id, INS.data
FROM (
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;
Results and cleanup:
SELECT * FROM dbo.MasterRecord
SELECT * FROM dbo.DetailRecord
-- Cleanup
DROP TABLE dbo.DetailRecord;
DROP TABLE dbo.MasterRecord;
DROP TABLE dbo.SomeOtherTable;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply