SQL SCOPE_IDENTITY or OUTPUT with data from another table

Question

I'm trying to get the ID of each row inserted from a mass insert and create a mapping table with an ID from my temp table #InsertedClients. I'm using the same temp table for the initial insert but the PDATClientID is not part of the insert. I can't seem to figure out how to do this correctly. When I inserting into the Client_Mapping table using SCOPE_IDENTITY it only grabs the ID from the first insert and puts it along with all of my PDATClientIDs. After doing a lot of Googling I believe I should be using OUTPUT, but can't seem to figure how to put the INSERTED.CLID for each record together with each PDATClientID. I should also say that I can't use a cursor there are too rows. Any help is greatly appreciated!

            IF NOT EXISTS (
                            SELECT ID
                            FROM sysobjects
                            WHERE id = object_id(N'[Client_Mapping]')
                                AND OBJECTPROPERTY(id, N'IsUserTable') = 1
                            )
                        CREATE TABLE [Client_Mapping] (
                            ClientMappingID INT Identity(1, 1) NOT NULL
                            ,PDATClientID INT NOT NULL
                            ,CLID INT NOT NULL
                            ,AUDITDATE DATETIME NOT NULL
                            ,CONSTRAINT [PK_Client_Mapping] PRIMARY KEY (ClientMappingID)
                            )

            -- Begin Inserts

            SELECT 
                First_Name
                ,Middle_Name
                ,Last_Name
                ,CONVERT(DATETIME, Date_Of_Birth) AS DOB
                ,a.Address_Line as Address1 
                ,a.Address_Line_2 as Address2 
                ,a.Zip_Code as  ZipCode -- ??? Do I need to account for zipcode + 4  
                ,REPLACE(REPLACE(REPLACE(cphp.Number_Or_Address, '(', ''), ')', '-'), ' ', '') AS HomePhone
                ,REPLACE(REPLACE(REPLACE(cpcp.Number_Or_Address, '(', ''), ')', '-'), ' ', '') AS CellPhone
                ,cpem.Number_Or_Address AS EMAIL
                ,c.Client_ID as PDATClientID
                ,c.Action AS [ClientAction]
                ,ca.Action as [ClientAddressAction]
                ,a.Action AS [AddressAction]
                ,cphp.Action AS [HomePhoneAction]
                ,cpcp.Action AS [CellPhoneAction]
                ,cpem.Action AS [EmailAction]

            INTO #InsertClients 

            FROM Client c
            LEFT JOIN Client_Address ca ON ca.Client_ID = c.Client_ID
            LEFT JOIN Address a ON a.Address_ID = ca.Address_ID
            LEFT JOIN Client_Phone cphp ON cphp.Client_ID = c.Client_ID
                AND cphp.Phone_Email_Type = 'HP'
                AND cphp.Start_Date = (
                    SELECT MAX(Start_Date)
                    FROM Client_Phone
                    WHERE Client_ID = c.Client_ID
                    )
            LEFT JOIN Client_Phone cpcp ON cpcp.Client_ID = c.Client_ID
                AND cpcp.Phone_Email_Type = 'CP'
                AND cpcp.Start_Date = (
                    SELECT MAX(Start_Date)
                    FROM Client_Phone
                    WHERE Client_ID = c.Client_ID
                    )
            LEFT JOIN Client_Phone cpem ON cpem.Client_ID = c.Client_ID
                AND cpem.Phone_Email_Type = 'EM'
                AND cpem.Start_Date = (
                    SELECT MAX(Start_Date)
                    FROM Client_Phone
                    WHERE Client_ID = c.Client_ID
                    )
            where c.action ='I' 


            BEGIN TRY
                    BEGIN TRAN

                    INSERT INTO [dbo].[Clients] (
                        [FName]
                        ,[MiddleInitial]
                        ,[LName]
                        ,[DOB]
                        ,[Address1]
                        ,[Address2]
                        ,[ZipCode]
                        ,[HomePhone]
                        ,[CellPhone]
                        ,[Email]
                        ,[AuditStaffID]
                        ,[AuditDate]
                        ,[DateCreated]
                        )
                        Select
                        First_Name
                        ,CASE when Middle_Name = '' THEN NULL ELSE Middle_Name END
                        ,Last_Name
                        ,DOB
                        ,Address1
                        ,Address2
                        ,ZipCode
                        ,HomePhone
                        ,CellPhone
                        ,EMail
                        ,496 AS [AuditStaffID]
                        ,CURRENT_TIMESTAMP AS [AuditDate]
                        ,CURRENT_TIMESTAMP AS [DateCreated]
                        FROM
                        #InsertClients
                        Where
                        [ClientAction] = 'I'



                        INSERT INTO [Client_Mapping]
                        (
                        PDATClientID
                        ,CLID
                        ,AUDITDATE
                        )
                        SELECT
                        PDATClientID
                        ,SCOPE_IDENTITY()
                        ,CURRENT_TIMESTAMP
                        FROM #InsertClients


                    COMMIT

                    END TRY

                    BEGIN CATCH

                        ROLLBACK

                    SELECT ERROR_NUMBER() AS ErrorNumber
                        ,ERROR_SEVERITY() AS ErrorSeverity
                        ,ERROR_STATE() AS ErrorState
                        ,ERROR_PROCEDURE() AS ErrorProcedure
                        ,ERROR_LINE() AS ErrorLine
                        ,ERROR_MESSAGE() AS ErrorMessage

                    END CATCH

Answer 1

you have a hell of a code in your question I can explain you in few line how you can retrieve all the inserted ids.

Yes SCOPE_IDENTITY() gives you the last IDENTITY value generated by the identity column. In you case you will need to use a table variable to get all the generated Identity values during your insert

/* Declare a table Vairable */

DECLARE @NewValues TABLE (IDs INT);

/* In Your Insert Statement use OUTPUT clause */

INSERT INTO Table_Name (Column1,Column2,Column3,....)
OUTPUT inserted.Identity_Column   INTO @NewValues (IDs)
SELECT Column1,Column2,Column3,....
FROM Table_Name2

/* Finally Select values from your table variable  */

SELECT * FROM @NewValues

Your Query

In your case your insert statement should look something like ...

INSERT INTO [dbo].[Clients] (
[FName]
,[MiddleInitial]
,[LName]
,[DOB]
,[Address1]
,[Address2]
,[ZipCode]
,[HomePhone]
,[CellPhone]
,[Email]
,[AuditStaffID]
,[AuditDate]
,[DateCreated]
)
OUTPUT inserted.[FName], inserted.[MiddleInitial] ,.... INTO @TableVarible(First_Name,[MiddleInitial].....) 

Select
First_Name
,CASE when Middle_Name = '' THEN NULL ELSE Middle_Name END
,Last_Name
,DOB
,Address1
,Address2
,ZipCode
,HomePhone
,CellPhone
,EMail
,496 AS [AuditStaffID]
,CURRENT_TIMESTAMP AS [AuditDate]
,CURRENT_TIMESTAMP AS [DateCreated]
FROM
#InsertClients
Where
[ClientAction] = 'I'

Answer 2

Solved the problem by using this example found here: http://sqlserverplanet.com/tsql/match-identity-columns-after-insert. The example from that site is below.

         -- Drop temp tables if they already exist
         IF OBJECT_ID('TempDB..#source', 'U') IS NOT NULL DROP TABLE #source;
         IF OBJECT_ID('TempDB..#target', 'U') IS NOT NULL DROP TABLE #target;
         IF OBJECT_ID('TempDB..#xref', 'U') IS NOT NULL DROP TABLE #xref;
         GO

         -- Create the temp tables
         CREATE TABLE #source (id INT PRIMARY KEY IDENTITY(1, 1), DATA INT);
         CREATE TABLE #target (id INT PRIMARY KEY IDENTITY(1, 1), DATA INT);
         CREATE TABLE #xref (ROW INT PRIMARY KEY IDENTITY(1, 1), source_id INT, target_id INT);
         GO

         -- If xref table is being reused, make sure to clear data and reseed by truncating.
         TRUNCATE TABLE #xref;

         -- Fill source table with dummy data (even numbers)
         INSERT INTO #source (DATA)
         SELECT 2 UNION SELECT 4 UNION SELECT 6 UNION SELECT 8;
         GO

         -- Fill target table with dummy data (odd numbers) to simulate existing records
         INSERT INTO #target (DATA)
         SELECT 1 UNION SELECT 3 UNION SELECT 5;
         GO

         -- Insert source table data into target table.  IMPORTANT: Inserted data must be sorted by the source table's primary key.
         INSERT INTO #target (DATA)
         OUTPUT INSERTED.id INTO #xref (target_id)
         SELECT DATA 
         FROM #source
         ORDER BY id ASC;
         GO

         -- Update the xref table with the PK of the source table.  This technique is used for data not captured during the insert.
         ;WITH src AS (SELECT id, ROW = ROW_NUMBER() OVER (ORDER BY id ASC) FROM #source)
         UPDATE x
         SET x.source_id = src.id 
         FROM #xref AS x
            JOIN src ON src.ROW = x.ROW;
         GO

         -- Verify data
         SELECT * FROM #source;
         SELECT * FROM #target;
         SELECT * FROM #xref;
         GO

         -- Clean-up
         IF OBJECT_ID('TempDB..#source', 'U') IS NOT NULL DROP TABLE #source;
         IF OBJECT_ID('TempDB..#target', 'U') IS NOT NULL DROP TABLE #target;
         IF OBJECT_ID('TempDB..#xref', 'U') IS NOT NULL DROP TABLE #xref;
         GO