Sql-server – Loading Data from CSV file or Staging Table to Multiple tables – SQL Server 2014

sql serversql server 2014

Hi i am trying to load data from CSV file to multiple sql server tables and some of the tables having Reference to parent tables so i can get the FK column value but trying to load via INSERT INTO sql, i am having issue when i have to load data from staging table and get the keyID from Parent tables, it's only getting last value from that parent table instead of all the parent IDs.
Ex.
PARENT 1 table during load – P1ID is from 1 to 799
PARENT 2 Table during load – P2ID is from 2 to 799
but Child Table during same load – C1ID is only 800 repeating for all the 799 rows and same for other P2ID should load all the 799 but repeating 800 value for all 799 rows.
My TableS:

CREATE TABLE [dbo].[StagingTbl](          -- Source MAster  Table
    [D_Cat] [nvarchar](50) NOT NULL,
    [QAP] [nvarchar](50) NOT NULL,
    [F_Cat] [nvarchar](50) NOT NULL,
    [Prog] [nvarchar](150) NOT NULL,
    [DP_Owner] [nvarchar](50) NULL,
    [DC_Owner] [nvarchar](50) NULL,
    [DPRev] [nvarchar](50) NULL,
    [D_Name_Cat] [nvarchar](100) NOT NULL,
    [DWName] [nvarchar](250) NOT NULL,
    INS [nvarchar](2050) NULL,
    [SRC] [nvarchar](50) NOT NULL,
    [STG] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DCat](
    [DCatID] [int] IDENTITY(1,1) NOT NULL,
    [DCatName] [nvarchar](150) NOT NULL,
    [DCatDesc] [nvarchar](250) NULL,
    CONSTRAINT [PK_DCat] PRIMARY KEY CLUSTERED 
)
GO

CREATE TABLE [dbo].[FCat](
    [FCatID] [int] IDENTITY(1,1) NOT NULL,
    [FCatName] [nvarchar](60) NOT NULL,
    [GWName] [nvarchar](100) NOT NULL,
    [Ins] [nvarchar](max) NULL,
    CONSTRAINT [PK_FCat] PRIMARY KEY CLUSTERED 
)
GO

CREATE TABLE [dbo].[DNameCat](
    [DNameCatID] [int] IDENTITY(1,1) NOT NULL,
    [DCatID] [int] NOT NULL,
    [FCatID] [int] NOT NULL,
    [DWName] [nvarchar](250) NOT NULL,
    CONSTRAINT [PK_DCatName] PRIMARY KEY CLUSTERED 
)
GO

CREATE TABLE [dbo].[Proj](
    [ProjID] [int] IDENTITY(1,1) NOT NULL,
    [PGType] [nvarchar](50) NULL,
    [PGName] [nvarchar](50) NULL,
    CONSTRAINT [PK_Proj] PRIMARY KEY CLUSTERED 
)
GO

CREATE TABLE [dbo].[Stage](
    [StgID] [int] IDENTITY(1,1) NOT NULL,
    [Active] [nvarchar](40) NOT NULL,
    [StgSrc] [nvarchar](10) NOT NULL,   
    CONSTRAINT [PK_Stage] PRIMARY KEY CLUSTERED 
)
GO

CREATE TABLE [dbo].[DML](
    [DMLID] [int] IDENTITY(1,1) NOT NULL,
    [DCatID] [int] NOT NULL,
    [FCatID] [int] NOT NULL,
    [DNameCatID] [int] NOT NULL,
    [ProjID] [int] NOT NULL,
    [StageID] [int] NOT NULL,   
    CONSTRAINT [PK_DML] PRIMARY KEY CLUSTERED 
)
GO

ALTER TABLE [dbo].[DML]  
WITH CHECK ADD  
CONSTRAINT [FK_DMLt_DCat_DCatID] 
FOREIGN KEY([DCatID]) REFERENCES [dbo].[DCat] ([DCatID])
GO
ALTER TABLE [dbo].[DML] CHECK CONSTRAINT [FK_DMLt_DCat_DCatID]
GO
ALTER TABLE [dbo].[DML]  
WITH CHECK ADD  
CONSTRAINT [FK_DMLt_DNameCat_DNameCatID] 
FOREIGN KEY([DNameCatID]) REFERENCES [dbo].[DNameCat] ([DNameCatID])
GO
ALTER TABLE [dbo].[DML] CHECK CONSTRAINT [FK_DMLt_DNameCat_DNameCatID]
GO
ALTER TABLE [dbo].[DML]  
WITH CHECK ADD  
CONSTRAINT [FK_DMLt_FCat_FCatID] 
FOREIGN KEY([FCatID]) REFERENCES [dbo].[FCat] ([FCatID])
GO
ALTER TABLE [dbo].[DML] CHECK CONSTRAINT [FK_DMLt_FCat_FCatID]
GO
ALTER TABLE [dbo].[DML]  
WITH CHECK ADD  
CONSTRAINT [FK_DMLt_Proj_ProjID] 
FOREIGN KEY([ProjID]) REFERENCES [dbo].[Proj] ([ProjID])
GO
ALTER TABLE [dbo].[DML] CHECK CONSTRAINT [FK_DMLt_Proj_ProjID] 
GO
ALTER TABLE [dbo].[DML]  
WITH CHECK ADD  
CONSTRAINT [FK_DMLt_Stage_StageID] 
FOREIGN KEY([StageID]) REFERENCES [dbo].[Stage] ([StageID])
GO
ALTER TABLE [dbo].[DML] CHECK CONSTRAINT [FK_DMLt_Stage_StageID]
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Document Category Primary Key - System Generated Number' , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'DCat', 
  @level2type=N'COLUMN',
  @level2name=N'DCatID'
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Document Category Primary Key - System Generated Number' ,   
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'DMLt', 
  @level2type=N'COLUMN',
  @level2name=N'DMLtID'
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Document Category Primary Key - System Generated Number' , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'DNameCat', 
  @level2type=N'COLUMN',
  @level2name=N'DNameCatID'
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Folder Category Primary Key - System Generated Number' , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'FCat', 
  @level2type=N'COLUMN',
  @level2name=N'FCatID'
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Document Category Primary Key - System Generated Number' , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'Proj', 
  @level2type=N'COLUMN',
  @level2name=N'ProjID'
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Document Category Primary Key - System Generated Number' , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'Stage', 
  @level2type=N'COLUMN',
  @level2name=N'StageID'
GO
USE [master]
GO
ALTER DATABASE [iConnect] SET  READ_WRITE 
GO

I have tried following code:

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
        @PPID INT, 
        @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]

-- DCID is Referencing to Dc table and FCID Ref to FC Table
INSERT INTO [dbo].[DNC] (DCID, FCID, DNameCat, DWeb)
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.

Example:

DC Table:

1
2
3

FC Table

1
2
3

DNC table

1 3 3
2 3 3
3 3 3

It should pick up 1 and 2 from DC and FC table but not.

Best Answer

I noticed a slight error in your script. This section:

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]

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

Should probably be more like the below (because you never actually pick up the scoped identity for the FC table in your original script):

INSERT INTO [dbo].[DC] ([DCName])
SELECT [DC_Cat]   
  FROM [dbo].[DM]

   SET @DCID  = SCOPE_IDENTITY() -- @@IDENTITY

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

   SET @FCID  = SCOPE_IDENTITY() -- @@IDENTITY

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

   SET @DNCID = SCOPE_IDENTITY()
   SET @PPID  = SCOPE_IDENTITY()
   SET @PSID  = SCOPE_IDENTITY()

The call to SCOPE_IDENTITY must always come after you have inserted into the table that you wish to obtain the identity for.

With that being said, unless you are inserting one row at a time, it will never do what you want it to. From the documentation for SCOPE_IDENTITY():

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

So, in short - SCOPE_IDENTITY only ever returns the last IDENTITY value that was inserted.

If you need all of the IDENTITY values that were generated, you can use the OUTPUT clause of the INSERT statement.

For example:

DECLARE @DCID TABLE
(
    [Id] INT NOT NULL PRIMARY KEY
);

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

DECLARE @FCID TABLE 
(
    ID NOT NULL PRIMARY KEY
);

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

Then you can use the list of ID's later on like so:

-- DCID is Referencing to Dc table and FCID Ref to FC Table
INSERT INTO [dbo].[DNC] (DCID, FCID, DNameCat, DWeb)
SELECT DC.ID, FC.ID, DNameCat, DWeb
  FROM [dbo].[DM]
  JOIN @DCID AS DC ON -- (whatever your join condition will be)
  JOIN @FCID AS FC ON -- (whatever your join condition will be)

You can use the values in those tables as many times as you like.

Related Question