Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: Clear data out of multiple tables.

    I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
    Thanks!
    Vic

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    (1) to create a blank DB from an existing DB
    generate a full script from the existing DB and run it. it is that easy
    (2) to populate data
    use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
    (3) reporting differences
    there r tools that can compare 2 DB and generate a report of differences

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
    Thanks,

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In SQL Server Management Studio:
    Management >> Legacy >> Data Transformation Services.
    They're fairl self explanatory...
    Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
    You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.

    Hope that's of some help. Feel free to ask away!
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I just went back and re-read my original post and I see where I need to clarify a few points.

    I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
    Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.

    Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better!

    So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.

    Thanks in advance for helping.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:

    http://www.elsasoft.org/tools.htm

    it will also script all the data out (optionally) using bcp.exe.

    I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •