I don't think this is possible, but I thought I'd ask just in case. Let's say I have a simple table.CREATE TABLE [table1] ( [table1_id] INT identity(1,1) NOT NULL, [name] VARCHAR (150) NOT NULL, [other_column] VARCHAR (10) NOT NULL CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([table1_id] ASC))
I need to copy some records and get the source id and the new id. At first I thought I might be able to do something like this: DECLARE @tableVar table( sourceId int NOT NULL, targetId int NOT NULL);insert into table1 (name, other_column) OUTPUT table1.table1_id,/*what can i put here?*/ INSERTED.table1_id INTO @tableVar select name, other_column from table1 where other_column = 'X'
That doesn't work, of course, because I'm not selecting table1.table1_id in the select statement. And I don't think I can select it because it's an identity column and I can't pull an extra column as far as I know when doing an insert into..select. Can I use a merge statement? Any suggestions? I sincerely appreciate any help. Apologies if I missed this question in my search.Thanks,Nick
Output clause to get new id and source id
Posted - 2014-11-23 : 22:49:04
