HELP WITH AUTOGENERATED INSERT

Question

Post reply

HELP WITH AUTOGENERATED INSERT

ishaan99

SSCertifiable

Points: 6261
More actions
September 27, 2007 at 3:22 pm

#121718

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(??????,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Why don't you just make a linked server betweeen the two environments and do the insert from the linked server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mengus Ten Centuries Points: 1187 More actions · Answer 2

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

ishaan99 SSCertifiable Points: 6261 More actions · Answer 3

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

Vladan SSC-Insane Points: 22072 More actions · Answer 4

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.

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mengus Ten Centuries Points: 1187 More actions · Answer 6

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