We help IT Professionals succeed at work.

Transfer database records into another database

238 Views
Last Modified: 2010-04-21
I spent some time on my db-schema to have all ID-PK-cols as Identity columns for replication. Before I had these rowguid colums.

But how do I transfer all my current database records into the new schema. There must be a solution out there!
The schema is the same. Only row guid cols are missing now. Tablenames, Fieldnames, Constraints are still the same. Any idea??
Chris
Comment
Watch Question

Commented:
I would temporarily keep the rowguid in the new database, just not as the PK.  Start by copying the tables with no foreign keys. Being identity cols,  the new pks will be automatically generated as you go.  Then copy first-level child tables.  The PK's in those tables will be automatically generated as before.  The FK's in those tables can be poulated from the new parent table by joining on rowguid during the copy. Proceed the next level child tables until done.  Then, remove the rowguid columns.






Author

Commented:
OK but I have to keep the IDs.
Yes, I can go the manual way.
I use http://vyaskn.tripod.com/code/generate_inserts.txt for that.
But I hope to find something where I have the possibility to automate.
Chris

Commented:
Assuming this is a one time affair, I think I'd shoot for somewhere in between manual and automated. The goal is to get spend as little time as possible and come up with a process that is reliably repeatable.  I expect you could automate it somewhat with a DTS package, but I'm not experienced enough to advise you on that.

That's an "interesting" insert generator, to say the least.  I think the intent is very good, unfortunately, the implementation suffers from some design issues. Maybe I'm missing something, but in my tests it didn't even generate valid SQL. Furthermore, it generated the insert script using print statements, but generated the actual insert statements using a select.  The result is that the insert script doesn't do any inserts and the inserts are not executable. Close, but no cigar!

Most curious, it generates an insert statement for every row of the table containing the values to be inserted for that row.  In effect, the script (if it were actually outputting the insert statements to the script) would be a storage medium for the data. To me this is a strange design choice, when the entire table could be copied in a single Insert statement!!!

See next frame for followup.


 


 




 With a little more work you can build a quasi-automated superscript that uses that one for the work horse.

In the superscript, run the insert generator for each table in the appropriate order (as described in previous post).



Commented:
OK, ignore that stuff at the end, I meant to delete it, but it had scrolled off my screen.

Anyway, I've revised the insert generator somewhat. It's by no means a finished product or fully tested.  But it should give you the idea.  In particular, it generates a script with a single-statement insert that copies from one table to another. Right now, it can only copy from one database to another on the same server.  To be robust, it needs a parameter to name the target server.

The hardest part of your job is to convert the foreign keys that used to be rowguid's to their new values.  With a little effort you could enhance the script generator to lookup the foreign keys and do that part of the conversion, as well.




Author

Commented:
Hi dqmq,
actually, the insert script is only supposed to create insert statements and not to actually do the inserts.
I'm using that to script the initial db-content when creating the db at the clients server. For that it is pretty useful.

>To me this is a strange design choice, when the entire table could be copied in a single Insert statement!!!
How would you do that?
Beeing able to copy a complete table with one statement, I would just create the tables with! the old rowguid-field and delete these fields later! Right now SQLserver 2005 would not let me delete a rowguid-col since these are used for replication. A new database would not be flagged for replication and I would be able to delete these cols before activating the replication again.
Chris

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Oh man, you put quite a lot time in it.
I'll not be able to review and test it this week due to a roll-out at a customer.
Thanks so far.
Chris

Author

Commented:
Hi,
I just want to come back on this topic. I'll be in charge of solving this issue end of Jan 08.
Thanks for your time so far, I really appreciate it and want to check it.
Chris

Author

Commented:
thanks for your support. It definitely helped. Right now it is still open if I really have to implement it. Changed so many times :-). You put some time in. Time to close the topic! Thanks again, Chris

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.