October 9, 2009 at 8:47 am
Does anyone know of a set based T-SQL solution to insert data into a parent table and related data in another table at the same time? The problem is not knowing the primary key values to link the data in related table. Below is a simplified version of what I'm trying to do. The temp table represents data gathered from a query from other tables in the database.
I have searched high and low for a solution but have not found anything. Any help is appreciated.
CREATE TABLE Parent
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)
)
CREATE TABLE Related
(
ID INT IDENTITY(1,1),
ParentID INT,
Code2 CHAR(2),
CONSTRAINT PK_Related_ID PRIMARY KEY (ID),
CONSTRAINT FK_Related_ParentID
FOREIGN KEY(ParentID)
REFERENCES Parent (ID)
)
CREATE TABLE #TmpOther
(
Code1 CHAR(1),
Code2 CHAR(2)
)
INSERT #TmpOther VALUES('A','A1')
INSERT #TmpOther VALUES('D','D2')
INSERT #TmpOther VALUES('J','J2')
INSERT #TmpOther VALUES('F','F3')
INSERT Parent
SELECT Code1
FROM #TmpOther
SELECT * FROM Parent
-- Set based INSERT for table Related??
DROP TABLE #TmpOther
DROP TABLE Related
DROP TABLE Parent
/*
data for table Parent
ID Code1
1 A
2 D
3 J
4 F
Desired data for table Related
ID ParentID Code2
1 1 A1
2 2 D2
3 3 J2
4 4 F3
*/
October 9, 2009 at 8:56 am
Assuming that the codes are unique
insert into related(ParentId,Code2)
Select Parent.Id,
Code2
from Parent,
#TmpOther
where Parent.Code1 = #TmpOther.Code1
Else you can , apparently , do this using merge , are you on 2008? , you have posted to a 2000 forum.
October 9, 2009 at 9:44 am
Unfortunately, I'm still on 2000. Also, the codes will not be unique...bummer.
October 11, 2009 at 10:55 am
cy-513176 (10/9/2009)
the codes will not be unique...bummer.
Why not? What's the point of filling up a table with duplicate codes with different IDENTITY values? That goes against the very point of having a surrogate key in the first place.
I'd say the problem is with the design of your tables because if you make the code unique then the problem will surely be solved.
October 12, 2009 at 7:31 am
I'd say the problem is with the design of your tables because if you make the code unique then the problem will surely be solved.
Yes! The problem is with the table design. It if were up to me, I would not even have it. Unfortunately it is beyond my powers at this point in time to change it.
October 12, 2009 at 7:35 am
In the past, i have solved this sort of issue by having an extra column on the target table to store the source id. That way you have something to join back on, its not ideal but better than a cursor.
October 12, 2009 at 3:08 pm
Do you have to insert duplicates though - even though you can't declare a key?
Basically the design is unusable - you can insert rows to it but you can never retrieve them because there's no way to know what you've just inserted... unless maybe you take an exclusive table-level lock to prevent anyone else updating it for the duration of the transaction. Or perhaps a bit less drastic, you could use a trigger to return the just-inserted values.
Can't think of anything else. What a pain to have to work with such rubbish!
October 13, 2009 at 3:41 am
I have coped with this sort of rubbish in the past by using a temp table and identity insert:
DECLARE @MaxID int
SELECT @MaxID = MAX([ID])
FROM Parent
SELECT IDENTITY(int) AS [ID]
,*
INTO #temp
FROM #TmpOther
SET IDENTITY_INSERT Parent ON
INSERT INTO Parent([ID], Code1)
SELECT @MaxID + [ID], Code1
FROM #temp
SET IDENTITY_INSERT Parent OFF
INSERT INTO Related (ParentID, Code2)
SELECT @MaxID + [ID], Code2
FROM #temp
This works well for data conversions but may cause problems if concurrent access is required.
October 13, 2009 at 7:17 am
Thanks for all of your input. I'm currently negotiating a table re-design, so hopefully I won't have to deal with this. I will take your ideas into consideration if I'm not successful.
October 13, 2009 at 9:40 am
Hello 500,
If you cannot negotiate a redesign, I hope this workaround can help:
-- **** BEGIN PART WE DO NOT HAVE CONTROL OVER ****
CREATE TABLE Parent
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)
)
GO
CREATE TABLE Related
(
ID INT IDENTITY(1,1),
ParentID INT,
Code2 CHAR(2),
CONSTRAINT PK_Related_ID PRIMARY KEY (ID),
CONSTRAINT FK_Related_ParentID
FOREIGN KEY(ParentID)
REFERENCES Parent (ID)
)
GO
-- **** END PART WE DO NOT HAVE CONTROL OVER ****
-- *** BEGIN WORKAROUND SETUP ****
-- Create work table
CREATE TABLE TmpOther
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
Code2 CHAR(2)
)
GO
-- Add trigger to work table
CREATE TRIGGER [trTmpOther] ON [dbo].[TmpOther] FOR INSERT
AS
BEGIN
INSERT INTO TmpParent (Code1, TmpID)
SELECT Code1, ID
FROM inserted
INSERT INTO TmpRelated (Code2, TmpID)
SELECT Code2, ID
FROM inserted
END
GO
-- Create surrogate parent table
CREATE TABLE TmpParent
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
TmpID INT,
CONSTRAINT PK_TmpParent_ID PRIMARY KEY (ID)
)
GO
-- Create surrogate related table without foreign key constraint
CREATE TABLE TmpRelated
(
ID INT IDENTITY(1,1),
ParentID INT,
Code2 CHAR(2),
TmpID INT,
CONSTRAINT PK_TmpRelated_ID PRIMARY KEY (ID),
)
GO
-- **** END WORKAROUND SETUP ****
-- Insert records into work table
INSERT INTO TmpOther VALUES('A','A1')
INSERT INTO TmpOther VALUES('D','D2')
INSERT INTO TmpOther VALUES('J','J2')
INSERT INTO TmpOther VALUES('F','F3')
-- Fill in the ParentID column in TmpRelated
UPDATE TmpRelated
SET TmpRelated.ParentID = TmpParent.ID
FROM TmpRelated
JOIN TmpParent ON TmpRelated.TmpID = TmpParent.TmpID
-- See what's in our Tmp tables
SELECT * FROM TmpOther
SELECT * FROM TmpParent
SELECT * FROM TmpRelated
-- **** NOW WE CAN UPDATE OUR PERMANENT TABLES ****
SET IDENTITY_INSERT Parent ON
INSERT INTO Parent (ID, Code1)
SELECT ID, Code1
FROM TmpParent
SET IDENTITY_INSERT Parent OFF
SET IDENTITY_INSERT Related ON
INSERT INTO Related (ID, ParentID, Code2)
SELECT ID, ParentID, Code2
FROM TmpRelated
SET IDENTITY_INSERT Related OFF
-- See what we ended up with
SELECT * FROM Parent
SELECT * FROM Related
-- Clean up
DROP TRIGGER trTmpOther
DROP TABLE TmpOther
DROP TABLE TmpParent
DROP TABLE TmpRelated
DROP TABLE Related
DROP TABLE Parent
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply