January 2, 2015 at 11:17 am
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
Please let me know if there is a set-based approach to this.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
January 2, 2015 at 12:14 pm
Jeffery Williams (1/2/2015)
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
Please let me know if there is a set-based approach to this.
Quick suggestion, use the output clause, consider the following sample code
😎
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TBL_DETAIL') IS NOT NULL DROP TABLE dbo.TBL_DETAIL;
IF OBJECT_ID('dbo.TBL_HEADER') IS NOT NULL DROP TABLE dbo.TBL_HEADER;
IF OBJECT_ID('dbo.TBL_IMPORT') IS NOT NULL DROP TABLE dbo.TBL_IMPORT;
/* The simplest of headers, the HEADER_INT */
CREATE TABLE dbo.TBL_HEADER
(
HEADER_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
CREATE TABLE dbo.TBL_DETAIL
(
DETAIL_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_ID INT NOT NULL CONSTRAINT FK_DBO_TBL_DETAIL_DBO_TBL_HEADER_HEADER_ID FOREIGN KEY REFERENCES dbo.TBL_HEADER(HEADER_ID)
,DETAIL_INT INT NOT NULL
);
/* RAW Document table */
CREATE TABLE dbo.TBL_IMPORT
(
HEADER_INT INT NOT NULL
,DETAIL_INT INT NOT NULL
);
/* Sample "document" data*/
INSERT INTO dbo.TBL_IMPORT (
HEADER_INT
,DETAIL_INT
)
VALUES (1001,11001)
,(1001,11001)
,(1002,12002)
,(1002,12002)
,(1003,13003)
,(1003,13003)
,(1004,14004)
,(1004,14004)
,(1005,15005)
,(1005,15005);
/* Table variable to store the identity values */
DECLARE @HEADER_IDENTITY TABLE
(
HEADER_ID INT NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
/* Insert the "document" HEADERS */
INSERT INTO dbo.TBL_HEADER(HEADER_INT)
OUTPUT inserted.HEADER_ID
,inserted.HEADER_INT
INTO @HEADER_IDENTITY(HEADER_ID,HEADER_INT)
SELECT DISTINCT SD.HEADER_INT
FROM dbo.TBL_IMPORT SD
/* Insert the DETAILs */
INSERT INTO dbo.TBL_DETAIL (HEADER_ID,DETAIL_INT)
SELECT
HI.HEADER_ID
,TI.DETAIL_INT
FROM dbo.TBL_IMPORT TI
INNER JOIN @HEADER_IDENTITY HI
ON TI.HEADER_INT = HI.HEADER_INT;
SELECT
TH.HEADER_ID AS HEADER_HEADER_ID
,TH.HEADER_INT AS HEADER_HEADER_INT
,DT.DETAIL_ID AS DETAIL_DETAIL_ID
,DT.HEADER_ID AS DETAIL_HEADER_ID
,DT.DETAIL_INT AS DETAIL_DETAIL_INT
FROM dbo.TBL_HEADER TH
INNER JOIN dbo.TBL_DETAIL DT
ON TH.HEADER_ID = DT.HEADER_ID;
Results
HEADER_HEADER_ID HEADER_HEADER_INT DETAIL_DETAIL_ID DETAIL_HEADER_ID DETAIL_DETAIL_INT
---------------- ----------------- ---------------- ---------------- -----------------
1 1001 1 1 11001
1 1001 2 1 11001
2 1002 3 2 12002
2 1002 4 2 12002
3 1003 5 3 13003
3 1003 6 3 13003
4 1004 7 4 14004
4 1004 8 4 14004
5 1005 9 5 15005
5 1005 10 5 15005
January 2, 2015 at 12:19 pm
Thank you, I will play with this. I have not used the OUTPUT clause yet.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
January 2, 2015 at 8:04 pm
I could be wrong but that looks like it presupposes what the identities will be.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2015 at 1:31 am
Does your source header data include a natural key which you could use for lookup purposes after writing to the parent table?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 1:35 am
By the way, this is not a great idea:
just set @@Identity to a variable and do a second insert statement.
Rather than @@Identity, you would be better off using Scope_Identity(). @@Identity is not limited by scope.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 1:38 am
Jeff Moden (1/2/2015)
I could be wrong but that looks like it presupposes what the identities will be.
It doesn't as the output clause retrieves the identity values generated in the first insert, in this case into a table variable, then uses the insert set's key values to join to the variable in the second insert for the identity values (foreign key in this case).
😎
The sample data is somewhat flawed but the method is good;-), below is a correction and slightly more details in the code's comments.
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TBL_DETAIL') IS NOT NULL DROP TABLE dbo.TBL_DETAIL;
IF OBJECT_ID('dbo.TBL_HEADER') IS NOT NULL DROP TABLE dbo.TBL_HEADER;
IF OBJECT_ID('dbo.TBL_IMPORT') IS NOT NULL DROP TABLE dbo.TBL_IMPORT;
/* The simplest of headers, the HEADER_INT
Identity seed and increment set to values
geater than 1 for demonstration purposes
*/
CREATE TABLE dbo.TBL_HEADER
(
HEADER_ID INT IDENTITY(12345,137) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
/* The detail table has a foreign key to the
identity value of the header, again the
identity seed and increment set to values
geater than 1 for demonstration purposes
*/
CREATE TABLE dbo.TBL_DETAIL
(
DETAIL_ID INT IDENTITY(54321,11) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_ID INT NOT NULL CONSTRAINT FK_DBO_TBL_DETAIL_DBO_TBL_HEADER_HEADER_ID FOREIGN KEY REFERENCES dbo.TBL_HEADER(HEADER_ID)
,DETAIL_INT INT NOT NULL
);
/* RAW Document table */
CREATE TABLE dbo.TBL_IMPORT
(
HEADER_INT INT NOT NULL
,DETAIL_INT INT NOT NULL
);
/* Sample "document" data, combination
of header-detail numbers
*/
INSERT INTO dbo.TBL_IMPORT (
HEADER_INT
,DETAIL_INT
)
VALUES (1001,11011)
,(1001,11021)
,(1002,12032)
,(1002,12042)
,(1003,13053)
,(1003,13063)
,(1004,14074)
,(1004,14084)
,(1005,15095)
,(1005,15005);
/* Table variable to temporarely store the identity values */
DECLARE @HEADER_IDENTITY TABLE
(
HEADER_ID INT NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
/* Insert the "document" HEADERS from the "import"
table, the output clause then inserts the same
and the identity values generated in the insert
into the table variable
*/
INSERT INTO dbo.TBL_HEADER(HEADER_INT)
OUTPUT inserted.HEADER_ID
,inserted.HEADER_INT
INTO @HEADER_IDENTITY(HEADER_ID,HEADER_INT)
/* In the sample data, each header has one or more
details, only the distinct set is needed for
the insert clause
*/
SELECT DISTINCT SD.HEADER_INT
FROM dbo.TBL_IMPORT SD
/* Insert the data into the DETAILs, joining the
table variable to get the identity values from
the header table
*/
INSERT INTO dbo.TBL_DETAIL (HEADER_ID,DETAIL_INT)
SELECT
HI.HEADER_ID
,TI.DETAIL_INT
FROM dbo.TBL_IMPORT TI
INNER JOIN @HEADER_IDENTITY HI
ON TI.HEADER_INT = HI.HEADER_INT;
/* Verifying the results:
1. Record count
2. Value Pairs
*/
SELECT
TH.HEADER_ID AS HEADER_HEADER_ID
,TH.HEADER_INT AS HEADER_HEADER_INT
,DT.DETAIL_ID AS DETAIL_DETAIL_ID
,DT.HEADER_ID AS DETAIL_HEADER_ID
,DT.DETAIL_INT AS DETAIL_DETAIL_INT
,IP.HEADER_INT AS IMPORT_HEADER_INT
,IP.DETAIL_INT AS IMPORT_DETAIL_INT
FROM dbo.TBL_HEADER TH
INNER JOIN dbo.TBL_DETAIL DT
ON TH.HEADER_ID = DT.HEADER_ID
RIGHT OUTER JOIN dbo.TBL_IMPORT IP
ON DT.DETAIL_INT = IP.DETAIL_INT;
Results
HEADER_HEADER_ID HEADER_HEADER_INT DETAIL_DETAIL_ID DETAIL_HEADER_ID DETAIL_DETAIL_INT IMPORT_HEADER_INT IMPORT_DETAIL_INT
---------------- ----------------- ---------------- ---------------- ----------------- ----------------- -----------------
12345 1001 54321 12345 11011 1001 11011
12345 1001 54332 12345 11021 1001 11021
12482 1002 54343 12482 12032 1002 12032
12482 1002 54354 12482 12042 1002 12042
12619 1003 54365 12619 13053 1003 13053
12619 1003 54376 12619 13063 1003 13063
12756 1004 54387 12756 14074 1004 14074
12756 1004 54398 12756 14084 1004 14084
12893 1005 54409 12893 15095 1005 15095
12893 1005 54420 12893 15005 1005 15005
January 3, 2015 at 1:47 am
Phil Parkin (1/3/2015)
By the way, this is not a great idea:just set @@Identity to a variable and do a second insert statement.
Rather than @@Identity, you would be better off using Scope_Identity(). @@Identity is not limited by scope.
Quick thought, the identity type functions are not suitable for a set based approach as their results are limited to a single value forcing a RBAR approach.
😎
From BOL:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
January 3, 2015 at 1:59 am
Eirikur Eiriksson (1/3/2015)
Phil Parkin (1/3/2015)
By the way, this is not a great idea:just set @@Identity to a variable and do a second insert statement.
Rather than @@Identity, you would be better off using Scope_Identity(). @@Identity is not limited by scope.
Quick thought, the identity type functions are not suitable for a set based approach as their results are limited to a single value forcing a RBAR approach.
😎
From BOL:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Thanks, but I know this 🙂
The quote was from the original post & I just wanted to throw this in as an aside, not as a suggested solution in this case.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 3, 2015 at 2:28 am
Phil Parkin (1/3/2015)
Thanks, but I know this 🙂The quote was from the original post & I just wanted to throw this in as an aside, not as a suggested solution in this case.
Was quite certain you did:-D, meant my input for completeness and to empathize on the set based approach.
😎
January 3, 2015 at 9:30 am
Eirikur Eiriksson (1/3/2015)
Jeff Moden (1/2/2015)
I could be wrong but that looks like it presupposes what the identities will be.It doesn't as the output clause retrieves the identity values generated in the first insert, in this case into a table variable, then uses the insert set's key values to join to the variable in the second insert for the identity values (foreign key in this case).
😎
The sample data is somewhat flawed but the method is good;-), below is a correction and slightly more details in the code's comments.
USE tempdb;SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TBL_DETAIL') IS NOT NULL DROP TABLE dbo.TBL_DETAIL;
IF OBJECT_ID('dbo.TBL_HEADER') IS NOT NULL DROP TABLE dbo.TBL_HEADER;
IF OBJECT_ID('dbo.TBL_IMPORT') IS NOT NULL DROP TABLE dbo.TBL_IMPORT;
/* The simplest of headers, the HEADER_INT
Identity seed and increment set to values
geater than 1 for demonstration purposes
*/
CREATE TABLE dbo.TBL_HEADER
(
HEADER_ID INT IDENTITY(12345,137) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
/* The detail table has a foreign key to the
identity value of the header, again the
identity seed and increment set to values
geater than 1 for demonstration purposes
*/
CREATE TABLE dbo.TBL_DETAIL
(
DETAIL_ID INT IDENTITY(54321,11) NOT NULL PRIMARY KEY CLUSTERED
,HEADER_ID INT NOT NULL CONSTRAINT FK_DBO_TBL_DETAIL_DBO_TBL_HEADER_HEADER_ID FOREIGN KEY REFERENCES dbo.TBL_HEADER(HEADER_ID)
,DETAIL_INT INT NOT NULL
);
/* RAW Document table */
CREATE TABLE dbo.TBL_IMPORT
(
HEADER_INT INT NOT NULL
,DETAIL_INT INT NOT NULL
);
/* Sample "document" data, combination
of header-detail numbers
*/
INSERT INTO dbo.TBL_IMPORT (
HEADER_INT
,DETAIL_INT
)
VALUES (1001,11011)
,(1001,11021)
,(1002,12032)
,(1002,12042)
,(1003,13053)
,(1003,13063)
,(1004,14074)
,(1004,14084)
,(1005,15095)
,(1005,15005);
/* Table variable to temporarely store the identity values */
DECLARE @HEADER_IDENTITY TABLE
(
HEADER_ID INT NOT NULL PRIMARY KEY CLUSTERED
,HEADER_INT INT NOT NULL
);
/* Insert the "document" HEADERS from the "import"
table, the output clause then inserts the same
and the identity values generated in the insert
into the table variable
*/
INSERT INTO dbo.TBL_HEADER(HEADER_INT)
OUTPUT inserted.HEADER_ID
,inserted.HEADER_INT
INTO @HEADER_IDENTITY(HEADER_ID,HEADER_INT)
/* In the sample data, each header has one or more
details, only the distinct set is needed for
the insert clause
*/
SELECT DISTINCT SD.HEADER_INT
FROM dbo.TBL_IMPORT SD
/* Insert the data into the DETAILs, joining the
table variable to get the identity values from
the header table
*/
INSERT INTO dbo.TBL_DETAIL (HEADER_ID,DETAIL_INT)
SELECT
HI.HEADER_ID
,TI.DETAIL_INT
FROM dbo.TBL_IMPORT TI
INNER JOIN @HEADER_IDENTITY HI
ON TI.HEADER_INT = HI.HEADER_INT;
/* Verifying the results:
1. Record count
2. Value Pairs
*/
SELECT
TH.HEADER_ID AS HEADER_HEADER_ID
,TH.HEADER_INT AS HEADER_HEADER_INT
,DT.DETAIL_ID AS DETAIL_DETAIL_ID
,DT.HEADER_ID AS DETAIL_HEADER_ID
,DT.DETAIL_INT AS DETAIL_DETAIL_INT
,IP.HEADER_INT AS IMPORT_HEADER_INT
,IP.DETAIL_INT AS IMPORT_DETAIL_INT
FROM dbo.TBL_HEADER TH
INNER JOIN dbo.TBL_DETAIL DT
ON TH.HEADER_ID = DT.HEADER_ID
RIGHT OUTER JOIN dbo.TBL_IMPORT IP
ON DT.DETAIL_INT = IP.DETAIL_INT;
Results
HEADER_HEADER_ID HEADER_HEADER_INT DETAIL_DETAIL_ID DETAIL_HEADER_ID DETAIL_DETAIL_INT IMPORT_HEADER_INT IMPORT_DETAIL_INT---------------- ----------------- ---------------- ---------------- ----------------- ----------------- -----------------
12345 1001 54321 12345 11011 1001 11011
12345 1001 54332 12345 11021 1001 11021
12482 1002 54343 12482 12032 1002 12032
12482 1002 54354 12482 12042 1002 12042
12619 1003 54365 12619 13053 1003 13053
12619 1003 54376 12619 13063 1003 13063
12756 1004 54387 12756 14074 1004 14074
12756 1004 54398 12756 14084 1004 14084
12893 1005 54409 12893 15095 1005 15095
12893 1005 54420 12893 15005 1005 15005
Apologies, I misspoke. I'm not talking about the actual identities. I'm talking about the pre-established relationship of header to detail row afforded by the following in your good code.
/* Sample "document" data, combination
of header-detail numbers
*/
INSERT INTO dbo.TBL_IMPORT (
HEADER_INT
,DETAIL_INT
)
VALUES (1001,11011)
,(1001,11021)
,(1002,12032)
,(1002,12042)
,(1003,13053)
,(1003,13063)
,(1004,14074)
,(1004,14084)
,(1005,15095)
,(1005,15005);
I'll see if I can come up with a simplified example similar to the data I've had to deal with in the past makes it impossible to do the classic Invoice/Invoice detail problem without the introduction of a special column in the header table.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2015 at 12:09 pm
Jeff Moden (1/3/2015)
I'll see if I can come up with a simplified example similar to the data I've had to deal with in the past makes it impossible to do the classic Invoice/Invoice detail problem without the introduction of a special column in the header table.
I would be very grateful if you did, would like to compile some real world samples/solutions.
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply