February 10, 2010 at 8:18 am
Just for the sake of completeness, here are two more alternatives:
- Create a view that combines the master and detail table into 1 and use an instead of insert trigger. In the trigger: insert into the first table, retrieve the inserted id (using the output clause) and do the insert into the second table; I don't think this is much better than your cursor based approach...
- Use two staging tables. First insert everything into the staging tables using pink_panthers approach, then copy it over (without the extra column containing the 'old' IDs) to your production tables and empty the staging tables. If you can use 'delete from' on the staging tables (instead of truncate), your identity columns will not be reset. Otherwise you can use DBCC CHECKIDENT with the RESEED option.
February 10, 2010 at 3:02 pm
I'll give it one last shot:
-- Create a temporary table for the assignments (old ID, new ID)
SELECT CAST(0 AS BIGINT) AS ID, CAST(0 AS BIGINT) AS PrevID, CAST(0 AS BIGINT) AS Ord INTO #t
-- Insert dummy rows into prod.tblMaster extracting their IDs
INSERT INTO
prod.tblMaster
OUTPUT
INSERTED.ID,
INSERTED.ID AS PrevID,
INSERTED.ID AS Ord
INTO #t
SELECT
tblMaster.Column1,
tblMaster.Column2 -- Insert only "NOT NULL" columns
FROM tblMaster
DECLARE@Order AS BIGINT
SELECT@Order = 0
-- Set an (arbitrary) order in #t's rows
UPDATE #t SET @Order = Ord = @Order + 1 FROM tblMaster
-- Assign each tblMaster's ID to one of the IDs of the dummy rows in prod.tblMaster
UPDATE #t SET PrevID = t.ID
FROM
#t
INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS Ord FROM tblMaster) t
ON #t.Ord = t.Ord
-- Set the right rows from tblMaster to prod.tblMaster
UPDATE
prod.tblMaster
SET
prod.tblMaster.Column1 = tblMaster.Column1,
prod.tblMaster.Column2 = tblMaster.Column2,
...
FROM
prod.tblMaster
INNER JOIN #t ON prod.tblMaster.ID = #t.ID
INNER JOIN tblMaster ON tblMaster.ID = #t.PrevID
-- Insert the children rows
INSERT INTO
prob.tblChild
SELECT
tblChild.Column1,
tblChild.Column2,
... -- All except ID,
#t.ID
FROM
tblChild
INNER JOIN #t
ON tblChild.ID = #t.PrevID
DROP TABLE #t
Regargs,
Elias
February 10, 2010 at 11:48 pm
pink_panther (2/10/2010)
I'll give it one last shot:
-- Create a temporary table for the assignments (old ID, new ID)SELECT CAST(0 AS BIGINT) AS ID, CAST(0 AS BIGINT) AS PrevID, CAST(0 AS BIGINT) AS Ord INTO #t
-- Insert dummy rows into prod.tblMaster extracting their IDs
INSERT INTO
prod.tblMaster
OUTPUT
INSERTED.ID,
INSERTED.ID AS PrevID,
INSERTED.ID AS Ord
INTO #t
SELECT
tblMaster.Column1,
tblMaster.Column2 -- Insert only "NOT NULL" columns
FROM tblMaster
DECLARE@Order AS BIGINT
SELECT@Order = 0
-- Set an (arbitrary) order in #t's rows
UPDATE #t SET @Order = Ord = @Order + 1 FROM tblMaster
-- Assign each tblMaster's ID to one of the IDs of the dummy rows in prod.tblMaster
UPDATE #t SET PrevID = t.ID
FROM
#t
INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS Ord FROM tblMaster) t
ON #t.Ord = t.Ord
-- Set the right rows from tblMaster to prod.tblMaster
UPDATE
prod.tblMaster
SET
prod.tblMaster.Column1 = tblMaster.Column1,
prod.tblMaster.Column2 = tblMaster.Column2,
...
FROM
prod.tblMaster
INNER JOIN #t ON prod.tblMaster.ID = #t.ID
INNER JOIN tblMaster ON tblMaster.ID = #t.PrevID
-- Insert the children rows
INSERT INTO
prob.tblChild
SELECT
tblChild.Column1,
tblChild.Column2,
... -- All except ID,
#t.ID
FROM
tblChild
INNER JOIN #t
ON tblChild.ID = #t.PrevID
DROP TABLE #t
Regargs,
Elias
PS Comments:
1. The method used for creating the #t table leaves a row in it. CREATE TABLE statement should be used instead.
2. The two UPDATE #t statements could be merged in one.
3. The whole code must be enclosed in a transaction.
Regards,
Elias
February 12, 2010 at 7:32 am
Is there any reason that SET IDENTITY_INSERT could not be used here? If it's in the same database (or server, I guess) then if an identity column is the problem, just SET INDENTIY_INSERT ON/OFF on the target table(s) and do a simple insert.
Or did I miss something?
February 12, 2010 at 7:39 am
e.g. (assuming child table does not have identity, if it does then SET IDENTITY_INSERT there)
SET IDENTITY_INSERT tblMaster ON
INSERT INTO prod.tblMaster SELECT * FROM tblMaster
SET IDENTITY_INSERT tblMaster OFF
INSERT INTO prod.tblChild SELECT * FROM tblChild
February 12, 2010 at 11:55 am
The reason is that the IDs of tblMaster and the already existing IDs of prod.tblMaster may be the same.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply