I have 2 tables (parent/child relationship). The parent table has an identity key that is automatically generated upon inserting a record. The identity key is also the primary key. I am performing a batch insert from a raw data table (lots of records) into the parent table, but need to be able to individually grab the identity key value of each newly inserted record and insert that value into the child table along with some other data. I am assuming that this will require a nested insert statement (i.e. Insert first record from raw data table into parent table, grab identity key from parent table, insert identity key and other data into child table, move to next record in raw data table). Could someone please help me with the syntax on a nested insert statement? Better yet, am I approaching this problem the correct way? Thanks for any help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
nested insert statement 2
- Thread starter ccampbell
- Start date
I have a tendancy to use Stored Procedures for such work...
CREATE PROCEDURE insert_new_pers_wr_records
@act int, @wr int, @pid int, @JanNew real, @FebNew real, @MarNew real, @AprNew real, @MayNew real, @JunNew real, @JulNew real, @AugNew real, @SepNew real, @OctNew real, @NovNew real, @DecNew real
AS
Declare @planID integer
Declare @actID integer
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
Set @planID = @@Identity
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
Set @actID = @@Identity
INSERT INTO personmonths (id_activitycodes, id_years_planned, id_years_actual, id_workrequests, id_personnel, [timestamp])
VALUES (@act, @planID, @actID, @wr, @pid, 123456789)
GO
CREATE PROCEDURE insert_new_pers_wr_records
@act int, @wr int, @pid int, @JanNew real, @FebNew real, @MarNew real, @AprNew real, @MayNew real, @JunNew real, @JulNew real, @AugNew real, @SepNew real, @OctNew real, @NovNew real, @DecNew real
AS
Declare @planID integer
Declare @actID integer
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
Set @planID = @@Identity
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
Set @actID = @@Identity
INSERT INTO personmonths (id_activitycodes, id_years_planned, id_years_actual, id_workrequests, id_personnel, [timestamp])
VALUES (@act, @planID, @actID, @wr, @pid, 123456789)
GO
- Thread starter
- #3
Wow, I appreciate the post. I do have a couple of questions for you. I have never use variables before in SQL but I can see that power in doing so. What is the difference between the functionality between
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
AND
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
If I understand this correctly, it looks like you are passing variables into the procedure...
CREATE PROCEDURE insert_new_pers_wr_records
@act int, @wr int, @pid int, @JanNew real, @FebNew real, @MarNew real, @AprNew real, @MayNew real, @JunNew real, @JulNew real, @AugNew real, @SepNew real, @OctNew real, @NovNew real, @DecNew real
then inserting those variables into the years table...
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
grabbing the identity from the years table...
Set @planID = @@Identity
(This is where I don't quite understand what is going on)
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
Then you insert the identity values into the child table...
INSERT INTO personmonths (id_activitycodes, id_years_planned, id_years_actual, id_workrequests, id_personnel, [timestamp])
VALUES (@act, @planID, @actID, @wr, @pid, 123456789)
Is this correct? Right now my inserts look something like this
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
WHERE XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER != NULL
This would represent the child table insert and LICENSE_SEQ_NUMBER is the identity key that I would have to grab from the previous insert statement. How do I get the values from the XML... table to the PLATE table by using variables as you have shown?
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
AND
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
If I understand this correctly, it looks like you are passing variables into the procedure...
CREATE PROCEDURE insert_new_pers_wr_records
@act int, @wr int, @pid int, @JanNew real, @FebNew real, @MarNew real, @AprNew real, @MayNew real, @JunNew real, @JulNew real, @AugNew real, @SepNew real, @OctNew real, @NovNew real, @DecNew real
then inserting those variables into the years table...
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (@JanNew, @FebNew, @MarNew, @AprNew, @MayNew, @JunNew, @JulNew, @AugNew, @SepNew, @OctNew, @NovNew, @DecNew)
grabbing the identity from the years table...
Set @planID = @@Identity
(This is where I don't quite understand what is going on)
INSERT INTO years (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])
VALUES (0,0,0,0,0,0,0,0,0,0,0,0)
Then you insert the identity values into the child table...
INSERT INTO personmonths (id_activitycodes, id_years_planned, id_years_actual, id_workrequests, id_personnel, [timestamp])
VALUES (@act, @planID, @actID, @wr, @pid, 123456789)
Is this correct? Right now my inserts look something like this
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
WHERE XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER != NULL
This would represent the child table insert and LICENSE_SEQ_NUMBER is the identity key that I would have to grab from the previous insert statement. How do I get the values from the XML... table to the PLATE table by using variables as you have shown?
In the example SP I pasted I insert 2 records into years for each new person...the first is their planned hours and the second is just a ZERO placeholder for their actual worked hours...
Your SP might look something like this:
CREATE PROCEDURE insert_something
@license_seq_number int
AS
Declare @newID integer
INSERT INTO table1 (field2)
VALUES (@license_seq_number)
Set @newID = @@Identity
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
WHERE XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER != @newID
Your SP might look something like this:
CREATE PROCEDURE insert_something
@license_seq_number int
AS
Declare @newID integer
INSERT INTO table1 (field2)
VALUES (@license_seq_number)
Set @newID = @@Identity
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
WHERE XML_VEHICLE_SUMMARY.LICENSE_SEQ_NUMBER != @newID
- Thread starter
- #5
- Thread starter
- #6
One last glitch. I have followed the format that you suggested
Declare @newID integer
INSERT INTO table1 (field2)
VALUES (@license_seq_number)
Set @newID = @@Identity
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT @newID,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
The insert into the first table works great it inserts 5000 records, the insert into the PLATE table inserts 5000 records, but all 5000 records have the same LICENSE_SEQ_NUMBER. How can I make it so that I get the right LICENSE_SEQ_NUMBER associated with the correct record?
Declare @newID integer
INSERT INTO table1 (field2)
VALUES (@license_seq_number)
Set @newID = @@Identity
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT @newID,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_NUMBER,
XML_VEHICLE_SUMMARY.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY
The insert into the first table works great it inserts 5000 records, the insert into the PLATE table inserts 5000 records, but all 5000 records have the same LICENSE_SEQ_NUMBER. How can I make it so that I get the right LICENSE_SEQ_NUMBER associated with the correct record?
-
1
- #7
The methodology given above only works well for single inserts as @@identity only contains one identity value. (incidentally you shoud use scope_identity() instead as that makes sure it gets the last identity from the current scope not one some other user created in the meantime.)
How can you tell what record is the parent to the subordinate records before you create the identity key? Do a join on that field(s) and then bring in the ID number that way in the second insert. If youcan tell me how you tell the records are related before you add the identity key, then I can help you write the insert to perform this function.
Alternatively you can process the records one at a time via a cursor, but that methodolgy will be extremely slow and you will still need to have a way to identify which record in the second set of data goes with which record in the first set of data.
How can you tell what record is the parent to the subordinate records before you create the identity key? Do a join on that field(s) and then bring in the ID number that way in the second insert. If youcan tell me how you tell the records are related before you add the identity key, then I can help you write the insert to perform this function.
Alternatively you can process the records one at a time via a cursor, but that methodolgy will be extremely slow and you will still need to have a way to identify which record in the second set of data goes with which record in the first set of data.
- Thread starter
- #8
SQLSister,
I am not sure I understand what you mean by "How can you tell what record is the parent to the subordinate records before you create the identity key?" My identity key is already created before I perform the insert. My insert into the parent table looks like this...
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS
FROM XML_VEHICLE_SUMMARY V
Upon performing the above insert, it creates an identity key called LICENSE_SEQ_NUMBER. I need to get the LICENSE_SEQ_NUMBER and then insert it into the child table with something similar to the to following insert statement.
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT /*LICENSE_SEQ_NUMBER needs to go here*/,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
Does that make sense? All of the records that I am inserting into my production tables come from a single working XML table. Your help is greatly appreciated. I need to get this thing up and running.
I am not sure I understand what you mean by "How can you tell what record is the parent to the subordinate records before you create the identity key?" My identity key is already created before I perform the insert. My insert into the parent table looks like this...
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS
FROM XML_VEHICLE_SUMMARY V
Upon performing the above insert, it creates an identity key called LICENSE_SEQ_NUMBER. I need to get the LICENSE_SEQ_NUMBER and then insert it into the child table with something similar to the to following insert statement.
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT /*LICENSE_SEQ_NUMBER needs to go here*/,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
Does that make sense? All of the records that I am inserting into my production tables come from a single working XML table. Your help is greatly appreciated. I need to get this thing up and running.
No the identity key is created as part of the insert. The problem is how to relate it back to the data you have to insert into the other table.
Look at a simplified data example
You have
Start Date Date Total INSPS LiscensePlateNo State
5/31/2003 6/27/2003 112 5 JXK107 VA
5/21/2003 2/27/2003 157 5 JXK108 VA
5/31/2003 6/27/2003 112 5 JKC516 CA
5/31/2003 6/27/2003 114 6 JXK107 NJ
5/31/2003 6/27/2003 112 5 JXK109 VA
Here I have three records which have the same values in the fields I sent to the first table. How do I tell SQL Server which record goes with which ID assiged when I did the insert?
It appears to me that you will need to write a cursor to process one record at a time unless you have a way to identify which record from the table with the indentity field belongs to which orginal record. Is this a one time insert or will this have to happen on a regular basis? I can think of a workaround if you are only going to do this once. But if this is a repeated process, then you will have difficulty doing this without the use of a cursor which will slow processing considerably.
To me it appears that you have a design structure which is fundamentally flawed. Your unique identifiers for the record appear to be the items you are putting in the subordinate table (Liscense plate number and State).
I am not the right person to ask to write a cursor as I never use them. Perhaps someone else who is experienced at writing them will make a suggestion.
Look at a simplified data example
You have
Start Date Date Total INSPS LiscensePlateNo State
5/31/2003 6/27/2003 112 5 JXK107 VA
5/21/2003 2/27/2003 157 5 JXK108 VA
5/31/2003 6/27/2003 112 5 JKC516 CA
5/31/2003 6/27/2003 114 6 JXK107 NJ
5/31/2003 6/27/2003 112 5 JXK109 VA
Here I have three records which have the same values in the fields I sent to the first table. How do I tell SQL Server which record goes with which ID assiged when I did the insert?
It appears to me that you will need to write a cursor to process one record at a time unless you have a way to identify which record from the table with the indentity field belongs to which orginal record. Is this a one time insert or will this have to happen on a regular basis? I can think of a workaround if you are only going to do this once. But if this is a repeated process, then you will have difficulty doing this without the use of a cursor which will slow processing considerably.
To me it appears that you have a design structure which is fundamentally flawed. Your unique identifiers for the record appear to be the items you are putting in the subordinate table (Liscense plate number and State).
I am not the right person to ask to write a cursor as I never use them. Perhaps someone else who is experienced at writing them will make a suggestion.
- Thread starter
- #10
I will need to perform this function on a regular basis. Here is the work around that I have come up with.
1. Retrieve data from tmp XML table and insert into CAR table (upon insertion the identity key is created)
2. After the bulk insert is complete, update the tmp XML table with the identity key from the CAR table.
3. Insert data (including the identity key) from the tmp XML table to the PLATE table.
I don't know if this is the right way to do it, but I think that it will return the correct results without having the time delay involved with a curser. Any ideas on this method?
1. Retrieve data from tmp XML table and insert into CAR table (upon insertion the identity key is created)
2. After the bulk insert is complete, update the tmp XML table with the identity key from the CAR table.
3. Insert data (including the identity key) from the tmp XML table to the PLATE table.
I don't know if this is the right way to do it, but I think that it will return the correct results without having the time delay involved with a curser. Any ideas on this method?
-
1
- #11
Hi,
Does this TSQL give wht u r looking for.... Hope it helps
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS
FROM XML_VEHICLE_SUMMARY V
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT C.LICENSE_SEQ_NUMBER,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
INNER JOIN CAR C ON C.START_DATE=X.START_DATE AND C.Date=X.Date AND C.Total = V.Total AND C.INSPS = X.INSPS
Sunil
Does this TSQL give wht u r looking for.... Hope it helps
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS
FROM XML_VEHICLE_SUMMARY V
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT C.LICENSE_SEQ_NUMBER,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
INNER JOIN CAR C ON C.START_DATE=X.START_DATE AND C.Date=X.Date AND C.Total = V.Total AND C.INSPS = X.INSPS
Sunil
Sunil I agree with you that this should work if the actual four fields he is inserting are unique. I suspect given the field names that they will not necessarily be unique. The real unique identifier of a car is the liscense plate and state which isn't inserted til the second insert. If the four fields create a unique record, I would go with your code as the most efficient. I could see three cars bought onthe same day that are the same model might have the initial four fields be the same. And that is my concern with the import.
Chris, will the records in fact be unique in a combination of all the fields sent to the first table? Have you checked to see if they are? If they are not, what happens when you return the ID code to the holding table for those records which are unique?
If they aren't something you could try is to create a unique key on the holding table and add it as an additional field to the fianl table. Then you could use a join on that field with theholding table to get the records you want and ID the real identity field that you want to use.
Something like:
Create field called HoldingID inthe XML_VEHICLE_SUMMARY table and the Car table.
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS,
HOLDINGID)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS,
V.HOLDINGID
FROM XML_VEHICLE_SUMMARY V
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT C.LICENSE_SEQ_NUMBER,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
INNER JOIN CAR C ON C.HoldingID=X.HoldingID
This should be faster than your proposed methodology. If you just truncate the data from the holding table it will not be a problem becasue it will continue to number from where it left off. But if you drop the table and bring in a new one, it will start over again at 1 and so you will need to get rid of the old holdingIds before you start the import process or you will have two or more records which have a holding ID of 1 which will create a problem for getting the real Identity key.
Chris, will the records in fact be unique in a combination of all the fields sent to the first table? Have you checked to see if they are? If they are not, what happens when you return the ID code to the holding table for those records which are unique?
If they aren't something you could try is to create a unique key on the holding table and add it as an additional field to the fianl table. Then you could use a join on that field with theholding table to get the records you want and ID the real identity field that you want to use.
Something like:
Create field called HoldingID inthe XML_VEHICLE_SUMMARY table and the Car table.
INSERT INTO CAR (START_DATE,
DATE,
TOTAL,
INSPS,
HOLDINGID)
SELECT DISTINCT V.START_DATE,
V.DATE,
V.TOTAL,
V.INSPS,
V.HOLDINGID
FROM XML_VEHICLE_SUMMARY V
INSERT INTO PLATE (LICENSE_SEQ_NUMBER,PLATE_NUMBER, PLATE_STATE)
SELECT DISTINCT C.LICENSE_SEQ_NUMBER,
X.LICENSE_PLATE_NUMBER,
X.LICENSE_PLATE_STATE
FROM XML_VEHICLE_SUMMARY X
INNER JOIN CAR C ON C.HoldingID=X.HoldingID
This should be faster than your proposed methodology. If you just truncate the data from the holding table it will not be a problem becasue it will continue to number from where it left off. But if you drop the table and bring in a new one, it will start over again at 1 and so you will need to get rid of the old holdingIds before you start the import process or you will have two or more records which have a holding ID of 1 which will create a problem for getting the real Identity key.
- Thread starter
- #13
The fields in the CAR table do represent a unique field, and the T-SQL statement in the inner join presented by sunila7 works great. I am still in the process of testing but so far so good. I will let you know if I have any questions. THanks for all of your help.
- Status
Similar threads
- Locked
- Question
