With this project I needed to insert data into table1 from table2 while simultaneously retrieving the identity field of table1, then insert that identity into table3 which mapped to table2 on a key that table1 did not have.
The OUTPUT clause when used with the input statment would not facilitate this because it could not return the identity and the source key in the same row. The result was to use the SQL Server 2008 Merge statement with the output clause.
Traditionally I would have looped the insert one record at a time and returned back both the values into variables, then inserted the identity into table3 by joining the keys on table2 and table 3.
CREATE TABLE #MatchIDs ( Table_1_EmployeeID int, Table_2_EmployeeID int ) INSERT INTO #MatchIDs ( Table_1_EmployeeID, Table_2_EmployeeID ) SELECT mrg.Table_1_EmployeeID ,mrg.Table_2_EmployeeID FROM( MERGE dbo.Table_1 t1 USING dbo.Table_2 t2 ON 1 = 0 WHEN NOT MATCHED THEN INSERT ( FirstName ) VALUES ( t2.FirstName ) OUTPUT $action ,t2.EmployeeID ,t1.EmployeeID ) AS mrg ( output_action ,Table_1_EmployeeID ,Table_2_EmployeeID ) WHERE mrg.output_action = 'INSERT'
The result is a statement that actually fools the MERGE by intentionally not matching any rows and just inserting all the records into the table.
Popular search terms:
Here is the simplied version of that… enjoy…
CREATE TABLE #MatchIDs
(
UpdateAction varchar(50),
Table_1_EmployeeID int,
Table_2_EmployeeID int
)
MERGE INTO Table_1 AS TARGET
USING Table_2 AS SOURCE
ON TARGET.id = SOURCE.id
WHEN MATCHED THEN
UPDATE SET TARGET.FristName = SOURCE.FIRSTNAME
, TARGET.LASTNAME = SOURCE.LASTNAME
WHEN NOT MATCHED THEN
INSERT (FirstName, LASTNAME)
VALUES (SOURCE.FIRSTNAME, SOURCE.LASTNAME)
OUTPUT $action, t2.EmployeeID ,t1.EmployeeID into #MatchIDs;
I tried this, but I am getting the following error: Msg 4104, Level 16, State 1, Line 30
The multi-part identifier “t1.EmployeeID” could not be bound.
Any ideas what could be wrong. Thanks in advance.
Please try ‘Inserted.EmployeeID’ instead of ‘t1.EmployeeID’
briliant piece of code!
Hi,
I really need to do this but am using SQL 2005 – is there any way that this can be done?
Hi Ben, sorry for the late reply. Unfortunately I don’t think there is.. At least that I know of.