ZkqP1dz1

1IF NOT EXISTS(
2	SELECT * FROM INFORMATION_SCHEMA.TABLES
3	WHERE TABLE_NAME = 'Users'
4	AND TABLE_SCHEMA = 'dbo'
5)
6BEGIN
7	CREATE TABLE Users(UserID INT IDENTITY(1,1), UserName VARCHAR(50));
8END
9
10DECLARE @NewUsers TABLE(pUserID int, UserID int, UserName varchar(50));
11
12INSERT INTO  @NewUsers( pUserID, UserID, UserName )
13VALUES (1,0,'Bob'),(2,0,'Sally'),(3,0,'Jim'),(4,0,'Sam'),(5,0,'Jeff')
14
15/*
16Goal:
17Run one thing to insert the contents of @NewUsers 
18into Users and return Users.UserID associated with
19the newly inserted Users for use elsewhere.
20*/
21
22
23INSERT INTO Users(UserName)
24SELECT UserName
25FROM @NewUsers
26ORDER BY pUserID DESC;
27
28WITH InsertedUsers AS(
29	SELECT
30		u.UserID,
31		ROW_NUMBER() OVER(ORDER BY u.UserID DESC) AS pUserID
32	FROM dbo.Users u
33)
34UPDATE @NewUsers
35SET UserID = iu.UserID
36FROM @NewUsers nu
37JOIN InsertedUsers iu ON iu.pUserID = nu.pUserID
38
39SELECT * FROM @NewUsers