September 27, 2007 at 3:22 pm
I am trying to build up an insert statement for a table which has the following structure. TableA has columns as
famid
Col1
col2
LastUpdated
old_famid.
I am using a script that generates a insert (..) values(...) for every single row. I need to use this insert to migrate some data from one env to the other. I have a few constraints
famid column is not null on target database.
famid could have the same value existing in target database where migration is taking place. So I need to get famid in TableA updated with famid from TableB where TableB.famid = TableA.old_famid.
Here TableB is populated before TableA so obviously will have the correct famid which needs to be used in TabA.
Is this something doable in the insert query itself? has anyone worked on a similar problem before. any help on this will be greatly appreciated.
Data in Table B before migration
famid fname lname
55 a a
66 b b
Data in Table B after migration
famid fname lname
100 a a
101 b b
Data migration of Table B is successful
Data in TableA before migration
famid col1 col2 lastupdated old_famid
55 a a a null
66 b b b null
Data in TableA after migration
famid col1 col2 lastupdated old_famid
100 a a a 55
101 b b b 66
Table A has famid as PK and notnull else an update would have been soln. What i am trying to get is teh right values when insertion happens
insert(famid, col1,col2,lastuopdated,old-famid) values(??????,
September 27, 2007 at 8:00 pm
Why don't you just make a linked server betweeen the two environments and do the insert from the linked server?
--Jeff Moden
October 1, 2007 at 10:40 am
ishaan99,
Do the two table have the same table structure? If so, you can use a tool like DBGhost or SQLCompare to script out the data for you, and once you have the file, just do a "Replace All" to find and replace name of TableA with TableB, or TableB with TableA, as required.
If the tables are different, please submit the DDLs, so we can investigate further. Generating INSERT Statements will not be difficult.
Thank you,
Wameng Vang
MCTS
October 2, 2007 at 7:42 am
Unfortunately teh structure is different . My process first get data into teh master table where new famid is generated. Now in all referece tables i need the new famid to replace the existing famid.
For ex in my reference tables if i have an insert as
INSERT INTO tableA (famid, metaname, tagvalue, LastUpdated)
VALUES (33, 'COND_CURRENT', 'test, '6/5/2007 7:07:26 AM')
GO
the value 33 needs to be replaced with
select famid from tableB where old_famid = 33
----------
famid
39
My insert should have
INSERT INTO tableA (famid, metaname, tagvalue, LastUpdated)
VALUES (39, 'COND_CURRENT', 'test, '6/5/2007 7:07:26 AM')
GO
Due to certain reasons i cant set up linked server also. My questions is, is it possible to have a subquery in the insert as
INSERT INTO tableA (famid, metaname, tagvalue, LastUpdated)
select familyid from familyprofile where old_familyid = 33,
select 'COND_CURRENT', 'test, '6/5/2007 7:07:26 AM', Can anyone help on this.
TIA
October 2, 2007 at 8:57 am
It depends on how the rest of the code is written and how are you getting the values to be inserted.
You can not use selects when using VALUES keyword.
You could get the value from a table if you use
INSERT INTO ....
SELECT ...
FROM table1
JOIN table2 ON .... etc.
October 2, 2007 at 9:15 am
we had a similar issue in our shop...we needed to merge two databases together, with each table having different data, but possibly the same identity() values.
we had to build an app, as the logic for changing foreign keys(ie one table had sp_fkey dependents in 50 + tables) was far too complex to try and keep it in TSQL.
this is what we had to do:
every table with an identity added a column 'NEWID', so that if the business logic determined it was going to be inserted into the new database, we got that Scope_Identity() and updated the fkeys realted to it.
every table had to have an evaluation formula, based on columns, to determine if it was new or a duplicate; and array was kept for every table that was dependant on the key for later updating.
for example, if both databases ahd a city table, the "CITYNAME" column was used to determine uniqueness, and ignored the Identity() column. if it didn't exist, it was inserted, and the new key was updated to the NEWID column. when dependant items were to be imported, the citykey from NEWID was used instead of the old when importing into the database. many tables had to use multiple columns, or even every column in order to test for uniqueness. lots ofdynamic sql building based on those values to see if the record already exists int he database to insert into.
it was a big job, and we update the xml which determines uniqueness for tables whenever there's new tables added, or any other schema changes/
Lowell
October 2, 2007 at 10:07 am
ishaan99,
I've read your most recent posting. Please submit the scripts for the table(s) structure. It would be nice to also get some sample data. This will allow me to dig into the code, and write some scripts, that will generate the insert statements for you. Without the column definitions for the associated tables, it is highly unlikely to be able to write a fully functional sample script for you to use.
Regards,
Wameng Vang
MCTS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply