March 18, 2019 at 2:30 am
Plenty of times I have encountered a request and/or a solution **) to export data from one database and import that data into another database (both SQL-server). One of the problems is that the Identies have to be resolved.
Question:
Is there a Generic solution for this ?
Preverable generating a script including the data and code, so that on the target machine the data gets imported and the identities resolved.
Ben
**)
Although I have encountered this problem multiple times, I haven't seen a Generic solution and most solutions I have seen where from not nice to very horrible.
March 18, 2019 at 4:14 am
Not a complete anwser/solution yet, but for readers of this thread and me this might help :
The OUTPUT Clause:
The OUTPUT Clause for UPDATE Statements
OUTPUT Clause (Transact-SQL)
The output clause can be used to make a 'resolve' table for identities. 🙂
But the question remains: Is there a generic solution for this problem ?
Ben
March 20, 2019 at 6:56 am
Doesn't seem likely to me that there could ever really be a generic solution beyond some means of matching the data and between the two databases to avoid duplication, and you haven't stated whether or not there's even going to be existing data on both sides. Of course, there's always turning IDENTITY INSERT ON so you are allowed to keep existing identity values, but again, is there existing data that might conflict? How any of that stuff could be done generically or even specified that way seems highly unlikely. Too much room for variations that could kill off any of a number of basic assumptions.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2019 at 8:20 am
sgmunson - Wednesday, March 20, 2019 6:56 AMDoesn't seem likely to me that there could ever really be a generic solution beyond some means of matching the data and between the two databases to avoid duplication, and you haven't stated whether or not there's even going to be existing data on both sides. Of course, there's always turning IDENTITY INSERT ON so you are allowed to keep existing identity values, but again, is there existing data that might conflict? How any of that stuff could be done generically or even specified that way seems highly unlikely. Too much room for variations that could kill off any of a number of basic assumptions.
I am looking for a generic solution, not a universal solution. Sometimes there is a possibility of choice for turning IDENTITY INSERT ON, which would give enough of a generic solution for me. Still al the problems you mention do not disappear with that, but that is another matter. But there are situations where you can not do that and even if you can normally you run into problems shortly after you have adapted this solution.
So the generic solution I am looking for is inserting data were all the identities get resolved.
All other problems just remain. So it is not a total solution.
Theoretically all identities can be resolved. And conceptually I can come up with a solution which just does that. But solving all problems (unique field problems, collation order problems, self referencing problems, circular references) does make this rather complex and in all situations in the past I just have solved the immediate problems for a specific case and never came to a generic solution.
Thanks for your anwser and participation,
Ben
April 2, 2019 at 7:31 am
The thread
using-the-output-clause-in-a-insert-statement
Partially describes the given problem. How to resolve the identities when data gets transported from one (SQL-server) system to another SQL-server system. And There is an identity involved, which is used as the field which is reverenced.
Here a simplistic example and a 'MERGE' solution how to create a 'resolve' table. Before or during inserting of a 'detail' table, which references the 'master' table, this resolve table can be used to update the references in the detail table.
Creating the reference table (as is shown) is part one of the solution.
Using the reference table while inserting the details is part two of the solution.
It is possible to generate scripts for a table. (Both the resolving of references and the generation of the resolve table).
It is even possible to generate scripts of a set of tables.
— Example
— Ben
— 20190401
—
— drop table A; drop table #tempA ; drop table resolveA
————————–
— Set up a table A
————————–
Create table A (id int identity(1000,1), texts varchar(200));
Create table resolveA (old_ID int, new_ID int);
INSERT INTO A (texts) values(‘hello’)
INSERT INTO A (texts) values(‘thanks’)
INSERT INTO A (texts) values(‘greetings’)
————————–
— Create a temp table, this is imported from an offsite SQL-server machine.
— Here table is is used to create this table.
————————–
select * into #tempA from A
update #tempA set texts = texts+’_new’
— thanks for your time and attention,
— Ben
-- Inserting from a 'source' and creating the Resolve rows in the resolve table:
The merge code for the given example.
------------------------------------------------------------------------
-- The Merge Statement.
------------------------------------------------------------------------
MERGE INTO A AS TGT
USING (SELECT * FROM #tempA ) AS SRC ON 1 = 2
WHEN NOT MATCHED THEN
INSERT(texts) VALUES(SRC.texts)
OUTPUT SRC.id, inserted.id
INTO resolveA(OLD_ID, NEW_ID);
-- Check the result
SELECT * FROM A -- With 'old' rows and the 'new' rows.
SELECT * FROM RESOLVEA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply