0

I am trying to insert into TableB and TableC from TableA. There are approximately 30 columns in Table A out of which let's say 10 goes to TableB and 20 goes to TableC.

The data has to go I believe row by row because. I have to insert one row in TableB which will generate TableID as a auto increment PK which is FK in TableC. I need the TableID to be inserted with rest of the columns into TableC.

Below is the code I am using. With this code, my TableB works fine and all the columns and rows are inserted in TableC except the ID is all same in TableC.

There are thousands of rows in TableA.

DECLARE @c BIGINT

INSERT INTO dbo.TableB (--TableBId - column value is auto-generated
                        FirstName, MiddleName, LastName, PhoneNumber,
                        CreatedBy, Created, Updatedby, Updated, IsActive)
    SELECT 
        MSFIRST, MSMI, MSLAST, MSPHONE, 
        9999, GETDATE(), 9999, GETDATE(), 1 
    FROM 
        TableA

SET @C = SCOPE_IDENTITY()

INSERT INTO [dbo].[TableC] (ClientID, TableBId, SSN, 
                            CreatedBy, Created, UpdatedBy, Updated)
    SELECT  
        400, @C, MSSS, 
        9999, GETDATE(), 9999, (GETDATE()) 
    FROM 
        TableA
-- DROP table #tt


SELECT * FROM TableB
SELECT * FROM dbo.TableC
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Troy
  • 1
  • 2
  • Is this a one time load or something that's going to reoccur? I ask, because your limitation of the auto increment TableBId could be worked around. – S3S Aug 04 '19 at 03:44
  • Is there no natural key? Which should be implemented as a unique constraint or unique index. – SMor Aug 04 '19 at 03:50
  • WEll, the `SET @C = SCOPE_IDENTITY()` of course will **only** retrieve the **last** `ID` inserted - so if your `INSERT` inserted 1000 rows, you only get back the **last** (1000th) generated ID - and then you're using that for each and every row you're inserting into `TableC` - so **of course** all the values for `TableBId` will be the same - since you really only have **ONE** `TableBId` value....... – marc_s Aug 04 '19 at 06:07
  • It is possible to do it in an efficient set-based way using `OUTPUT` clause together with the `MERGE` statement. Here is one similar question [Cascade copy a row with all child rows and their child rows, etc](https://stackoverflow.com/questions/41595802/cascade-copy-a-row-with-all-child-rows-and-their-child-rows-etc) – Vladimir Baranov Aug 04 '19 at 12:05
  • This will be an ongoing process. We will be getting the files like that on a monthly basis. There is no natural key in TableA. – Troy Aug 04 '19 at 12:46

0 Answers0