0

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.

2

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.

  • So U says, IF i use my script with yur version of SCOPE_IDENTITY will not work what i want ? If not then your suggested solution should i use? My DocumentsMasterListEdited3 table is same DM table i forgot change that short name in my above script for other tables. My Staging table (DM) and all other destination tables (DC, FC, DNC, MDL, PS, PP) doesn't have much to join as a common condition except parent -child table has ids and staging tables is just like a file data that goes into above destination tables, MDL tables contains all other tables FK values plus staging table some fields values – pds Mar 16 at 22:16
  • If you want all of the identity values that were generated during an insert, instead of just the last one that was generated; then you must use the output clause. – Mr.Brownstone Mar 16 at 22:26
  • I tried your modified solution of my version but it didn't work so you are right. – pds Mar 16 at 22:43
  • I am trying to your another example, as per join I should skip right? As Staging table is just files with fields as loading into different tables, another 2 table has FK ref to another tables and one contains all other 5 tables FK plus some other fields from Staging table, what do you think? – pds Mar 16 at 22:45
  • You can do anything you like with the id values once you have them, I was just showing you how to obtain them :-) – Mr.Brownstone Mar 16 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.