Loading Data From Staging Table to Parent and child Tables

1

DECLARE @DCID INT, -- Parent Key Column with Identity in DC Table
@FCID INT , -- Parent Key column with Identity in FC Table
@DNCID INT -- Parent Key Column with Identity in DNC Table
-- @PPIDINT
-- @PSID INT

BEGIN TRAN

INSERT INTO [dbo].[DC] ( [DCName])

SELECT  [DC_Cat]   
FROM [dbo].[DM]

  SET @DCID  =	SCOPE_IDENTITY() -- @@IDENTITY
  	  SET @FCID  =	SCOPE_IDENTITY() -- @@IDENTITY
  SET @DNCID =	SCOPE_IDENTITY()
  SET @PPID  = SCOPE_IDENTITY()
  SET @PSID  = SCOPE_IDENTITY()


INSERT INTO [dbo].[FC] ([FCName], [GWName], [Ins])
SELECT FC_Name, G_Name4_W , Ins
FROM [dbo].[DM]

INSERT INTO [dbo].[DNC] (DCID, FCID, DNameCat, DWeb) -- DCID is Referencing to Dc table and FCID Ref to FC Table
SELECT @DCID, @FCID, DNameCat, DWeb
FROM [dbo].[DocumentsMasterListEdited3]

COMM TRAN

-- when Laoding into only tables DC and FC, its runs fine as no dependent parent Table for both
But when Loading all three tables together DC, FC and DNC then loads but both DCID and FCID not incrementing and just pickup only last values
Ex.
DC Table:

1
2
3

FC Table

1
2
3

DNC table

1 3 3
2 3 3
3 3 3

It shoud pick up 1 and 2 from DC and FC table but not.
I have couple more tables also which i have load data but it's having only last PK value from parent table in first child table so i haven't added another tables

2

Do you see what the issue is the way you are using SCOPE_IDENTITY() ?

image
image.png550×501 16.9 KB

3

Nope, I think that's the issue which i can't catch?

4

All those scope identity variables refer to the last INSERT hence will all be identical

5

Thanks Yosiasz.
I tried with also IDentity @@ but same results.
Appreciated your help!

6

You have to put each variable after each of its corresponding insert or do the following or do the following.
the following way might be better as it handles the case where there are more than one rows inserted

DECLARE @DC  
(  
    DCID INT,  
    DCName VARCHAR(32)  
); 

INSERT INTO [dbo].[DC] ( [DCName])
OUTPUT INSERTED.DCID, INSERTED.DCName INTO @MyTableVar
SELECT  [DC_Cat]   
FROM [dbo].[DM]
7

Thanks again.
I never used this but when i tried this way then getting error:
DECLARE @DC
(
DCID INT,
DCName VARCHAR(50)
);

INSERT INTO [dbo].[DC] ([DCName])
OUTPUT INSERTED.DCID, INSERTED.DCName INTO @MyTableVar
SELECT  [DC_Cat]   
FROM [dbo].[DM]

DECLARE @FC
(
FCID INT,
FCName VARCHAR(50),
GWName VARCHAR(150),
Ins VARCHAR(150)
);

INSERT INTO [dbo].[FC] ( [FCName]), [GWName], [Ins])
OUTPUT INSERTED.FCID, INSERTED.FCName, INSERTED.GWName, INSERTED.Ins INTO @MyTableVar
SELECT F_Cat, GW_Name , Instr
FROM [dbo].[DM]

8

Oops should be

DECLARE @DC Table
(
DCID INT,
DCName VARCHAR(50)
);

9

It's complaining about @MyTableVar

10

Any idea?
I tried with different way but getting some errors. Problem is when i try to load Data from StagingTbl with another two table's PK value then my any logic is not working. Any help will be great help for me as i have to load into another table also and that table contains five Table's PK value along with
StagingTbl's fields.

My query is: The First Two insert works fine for DC and FC
I tried this way"
INSERT INTO [dbo].[DCName]
([DCatID], [FCatID], DNameCat, DWebName)
SELECT
(SELECT [DCID] FROM dbo.[DC] WHERE [DC].[DCName] = [dbo].[ DM ].[DCName]) ,
(SELECT [FCID] FROM [FC] WHERE [FC].[FCName] = [dbo].[ DM ].[FCName]),
[DNameCat], [DWeb]
FROM [dbo].[DM]
i am getting error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My query is: The First Two insert works fine for DCat and Fcat