Friday 12 December 2014

SQL - How to capture multiple identities from INSERT INTO SELECT

-- Example Source table
CREATE TABLE #SourceTable ( Contact CHAR(30) );

INSERT #SourceTable VALUES ( 'User1' )
INSERT #SourceTable VALUES ( 'User2' )
INSERT #SourceTable VALUES ( 'User3' )

-- Example Dest table
CREATE TABLE #ImportedUsers ( Id       INT IDENTITY(1, 1) NOT NULL
,                             UserName CHAR(30) );

-- Imported users + new ID table
DECLARE @IDs TABLE ( Id       INT
,                    UserName CHAR(30) )

INSERT #ImportedUsers
 OUTPUT inserted.Id
 ,      inserted.UserName
 INTO @IDs ( Id
 ,           UserName )
SELECT DISTINCT Contact
FROM #SourceTable

SELECT *
FROM @IDs

No comments:

Post a Comment