March 15, 2019 at 12:24 pm
Hi,
I am trying to load data fro mSTaging table (No Primary Key as it's from the csv file) but in Child table and it's inserting all the Parent Kel column value, just picking up last value, see the code below:
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.
March 15, 2019 at 3:36 pm
You're thinking procedurally instead of set-based. The tables load all at once, not one row at a time. Look at the OUTPUT clause and study those examples.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 15, 2019 at 3:45 pm
Thanks but not sure about OUTPUT clause, I will google it but if you have example that will be great.
March 15, 2019 at 4:00 pm
pdsqsql - Friday, March 15, 2019 3:45 PMThanks but not sure about OUTPUT clause, I will google it but if you have example that will be great.
If you want an example, then provide sample data in a readily consumable format. The first link in my signature tells you how.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 15, 2019 at 10:41 pm
Thank so much Alen.
Please see the following Query
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, -- FK Ref to DCat Table
[FCatID] [int] NOT NULL, -- FK Ref to FCat Table
[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, -- FK Ref to DCat Table
[FCatID] [int] NOT NULL, -- FK Ref to FCat Table
[DNameCatID] [int] NOT NULL, -- FK Ref to DNameCat Table
[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
March 20, 2019 at 8:40 pm
Hi Alen,
I have posted table script, if it's what you are looking to help me out.
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].[DCName] = [dbo].[StagingTbl].[DNameCat]) ,
(SELECT [FCatID] FROM [FCat] WHERE [FCat].[FCatName] = [dbo].[StagingTbl].[FCatName]),
[D_Name_Cat], [D_Name_Web]
FROM [dbo].[StagingTbl]
March 21, 2019 at 9:13 am
pdsqsql - Wednesday, March 20, 2019 8:40 PMHi Alen,
I have posted table script, if it's what you are looking to help me out.
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].[DCName] = [dbo].[StagingTbl].[DNameCat]) ,
(SELECT [FCatID] FROM [FCat] WHERE [FCat].[FCatName] = [dbo].[StagingTbl].[FCatName]),
[D_Name_Cat], [D_Name_Web]
FROM [dbo].[StagingTbl]
You obviously didn't read the link in my signature. Please read it.
Drew (Not Allen, and definitely not Alen)
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2019 at 11:21 am
Sorry about it but your really appreciated!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply