Loading data from CSV file into Multiple Tables

Article
03/14/2019

Question

_{Thursday, March 14, 2019 2:32 AM}

Hi,
I‌ have to load the data from .csv file into few sql server tables which have referential integrity also.
W‌hat's the best way to load data without any referential integrity or other issues.
I‌s it through SSMS import task easy or scripting way?

T‌able 1 ==> PK col + some columns
T‌able 2 ==> PK+ some columns + FK to TABLE1
T‌able 3 ==> PK+ some columns
T‌able 4 ==> PK + some columns + FK to TABLE2
T‌able 5 ==> PK + some columns + FK to TABLE2 and TABLE3
T‌able 6 ‌‌‌‌==> Kind of MAster Table which contains all other tables PK columns as FK + some other columns
N‌ote: Some of other columns i have default value also like User (SUSER) and DAteCol is GETDATE()
Y‌our input greatly appreciated!

T‌hanks‌

All replies (15)

_{Thursday, March 14, 2019 2:59 AM}

The best way to load it into a temporary table then process from there

Please refer this : https://stackoverflow.com/questions/14247057/import-csv-into-sql-multiple-tables

Hope this helps!

Please don't forget to “markthe replies as answers”if they helped, also set "like" it’s a boost for us to keep blogging J

Click here to learn more. Visit the dedicated Community forum to share, explore and talk to experts about Microsoft Kaizala.

_{Thursday, March 14, 2019 10:39 AM}

Thanks Ganesan.

I see that and already i have loaded into Temp Staging table but i was also looking Insert script example to load into multiple tables from the staging table.

_{Thursday, March 14, 2019 10:46 AM}

If you want to maintain referential integrity and do population best way is to do it in two steps

1. get flat file data as is to a staging table

2. have a sql procedure where you take data from staging and populate each of the tables, starting with master table and working down the child tables

The logic will look like below except for first step which will be a standard data flow task instead of XML source in the example below

https://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Thursday, March 14, 2019 12:40 PM}

Thanks Ganesan.

I see that and already i have loaded into Temp Staging table but i was also looking Insert script example to load into multiple tables from the staging table.

OK then, definitely please have a look at Vikash' s Blog, that's solution forward.

_{Friday, March 15, 2019 6:13 PM}

I have created staging table and trying to load data from Staging table to different tables but for some of child tables FK columns not matching with parent table's parent key column. I have set up Parent key column as IDENTITY column.

Please see the following:

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.

_{Saturday, March 16, 2019 1:09 PM}

Hello,

Any help greatly appreciated as tried with following but still picking up same last records for child table.

Instead of SCOPE_IDENTITY(), I tried to use @@IDENTITY but still same results.

_{Saturday, March 16, 2019 1:57 PM}

Hello,

Any help greatly appreciated as tried with following but still picking up same last records for child table.

Instead of SCOPE_IDENTITY(), I tried to use @@IDENTITY but still same results.

Did you see my link at all?

For batch insertion, you should be using OUTPUT clause to get related keys for the parent tables.

Like how I have done here

https://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Saturday, March 16, 2019 8:37 PM}

Visakh,

I see your link but i have started with the which ones i mentioned here and i thought it will work but it worked with two tables and after that i started to add another table which has FK ref to those tables and then i got stuck so i thought if one insert works then i can move to add another table which has all other parent tables REf keys.

I will also look into but just curios it will work for multiple tables and if table has 5 Foreign keys then how it will work?

I can post the Create table script for reference if it can help for me.

Thanks for your great help!

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

_{Sunday, March 17, 2019 8:54 AM}

Visakh,

I will also look into but just curios it will work for multiple tables and if table has 5 Foreign keys then how it will work?

I can post the Create table script for reference if it can help for me.

Thanks for your great help!

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

It will work for any level of child tables as long as you start with master and work your way down to the child level tables.

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Sunday, March 17, 2019 2:51 PM}

Visakh,

I tried to follow your script but not much understood so if you can help me out, i will really appreciated!

Thanks for your kind help!

_{Monday, March 18, 2019 2:51 AM}

I have also posted table script, help will be greatly appreciated,

_{Monday, March 18, 2019 6:46 AM}

I also want to understand your script.

_{Thursday, March 21, 2019 2:53 AM}

Any help greatly appreciated please!

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 DCat and FCat
I tried this way"
INSERT INTO [dbo].[DNameCat]
([DCatID], [FCatID], DNameCat, DWebName)
SELECT
(SELECT [DCatID] FROM dbo.[DCat] WHERE [DCat].[DCatName] = [dbo].[StagingTbl].[DatCName]) ,

(SELECT [FCatID] FROM [FCat] WHERE [FCat].[FCatName] = [dbo].[ StagingTbl].[FCatName]),
[DNameCat], [DWebName]
FROM [dbo].[StagingTbl]
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.

Thanks for your help!

_{Thursday, March 21, 2019 6:54 AM}

Any help greatly appreciated please!

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 DCat and FCat
I tried this way"
INSERT INTO [dbo].[DNameCat]
([DCatID], [FCatID], DNameCat, DWebName)
SELECT
(SELECT [DCatID] FROM dbo.[DCat] WHERE [DCat].[DCatName] = [dbo].[StagingTbl].[DatCName]) ,

(SELECT [FCatID] FROM [FCat] WHERE [FCat].[FCatName] = [dbo].[ StagingTbl].[FCatName]),
[DNameCat], [DWebName]
FROM [dbo].[StagingTbl]
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.

Thanks for your help!

Did you check my link at all?

Where are you using OUTPUT clause here to capture values as I suggested?

It should be something like below

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

DECLARE @INSERTED_DCAT table
(
[DCatID] int,
[D_Cat] [nvarchar](50)
)

DECLARE @INSERTED_FCAT table
(
[FCatID] int,
[F_Cat] [nvarchar](50)
)


DECLARE @INSERTED_Proj table
(
ProjID int,
[PGName] [nvarchar](50),
PGType [nvarchar](50)
)



DECLARE @INSERTED_Stage table
(
StgID  int,
Active [nvarchar](50),
StgSrc  [nvarchar](50)
)



DECLARE @INSERTED_DNameCat table
(
DNameCatID int,
DCatID int,
[FCatID] int,
DwName [nvarchar](250)
)




INSERT [dbo].[DCat] ([DCatName])
SELECT [D_Cat]
OUTPUT INSERTED.[DCatID],INSERTED.[D_Cat] INTO @INSERTED_DCAT
FROM [dbo].[StagingTbl]

INSERT [dbo].[FCat] ([FCatName],[GWName])
SELECT [F_Cat],<column corresponding to GWName>
OUTPUT INSERTED.[FCatID],INSERTED.[FCatName] INTO @INSERTED_FCAT
FROM [dbo].[StagingTbl]

INSERT [dbo].Proj ([PGType],[PGName])
SELECT <column corresponding to PGType>,Proj
OUTPUT INSERTED.[ProjID],INSERTED.[PGName],INSERTED.PGType INTO @INSERTED_Proj
FROM [dbo].[StagingTbl]

INSERT [dbo].Stage (Active,StgSrc)
SELECT STG,SRC
OUTPUT INSERTED.[StgID],INSERTED.Active,INSERTED.StgSrc INTO @INSERTED_Stage
FROM [dbo].[StagingTbl]


INSERT [dbo].[DNameCat] (DCatID,FcatID,DWName)
SELECT d.DCatID,f.FCatID,s.DWName
OUTPUT INSERTED.[DNameCatID],INSERTED.[DCatID],INSERTED.[FCatID],INSERTED.[DwName] INTO @INSERTED_DNameCat
FROM [dbo].[StagingTbl] s
JOIN @INSERTED_DCAT d
ON d.[D_Cat] = s.[D_Cat]
JOIN @INSERTED_FCAT f
ON f.[F_Cat] = s.[F_Cat]


INSERT [dbo].DML ([DCatID], [FCatID] ,  [DNameCatID] ,  [ProjID],   [StageID])
SELECT d.DCatID,f.FCatID,dn.DNameCatID,p.ProjID,s.StageID
FROM [dbo].[StagingTbl] s
JOIN @INSERTED_DCAT d
ON d.[D_Cat] = s.[D_Cat]
JOIN @INSERTED_FCAT f
ON f.[F_Cat] = s.[F_Cat]
JOIN @INSERTED_DNameCat dn
ON dn.DCatID = d.DCatID
AND dn.FCatID = f.FCatID
AND dn.DwName = s.DwName
JOIN @INSERTED_Proj p
ON p.PGName = s.PGName
AND p.PGTYpe = s.<Column for PGType>
JOIN @INSERTED_Stage stg
ON stg.StgSrc = s.SRC
AND stg.Active = s.STG

See how I've used OUTPUT tables above to capture generated ID values and use it for further table population

Wherever column name is not clear I've placed place holders. Make sure you replace them with correct column names instead

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Thursday, March 21, 2019 7:24 AM}

Hi pdsqsql,

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

The reason for this error is that you directly insert the subquery as a result into [DNameCat], which causes the database to fail to correspond.

You can use the following statement:

INSERT INTO [dbo].[DNameCat]

([DCatID], [FCatID], DNameCat, DWebName)

select c.[DCatID],b.[FCatID],a.[DNameCat], a.[DWebName]

from [dbo].[StagingTbl] a

join [FCat] b on a.[FCatName]=b.[FCatName]

join [DCat] c on a.[DCatName]=c.[DCatName]

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Share via

Loading data from CSV file into Multiple Tables

Question

All replies (15)

Additional resources