none
Migrating data from production database to new database

    Question

  • Let's say I have productionDB and testDB

    in productionDB

    I have 3 tables: tb1,tb2,tb3

    in testDB

    I have 4 tables:tb1,tb2,tb3,tb4(tb1,2,3 have exactly the same schema as in productionDB)

    I will want to migrate only the data from tb1,2,3 in productionDB to testDB

    How do I do it?

    Saturday, September 07, 2013 3:20 PM

Answers

  • You can use INSERT..SELECT just these three tables. like this:

    INSERT Testdb.Tb1 
    	VALUES (<columns>)
    SELECT <columns>
    FROM Productdb.Tb1


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    Saturday, September 07, 2013 3:27 PM
  • Hi,

    If both DB's are in same server , try like this

    INSERT INTO testDB.schemaname.tablename
    SELECT * FROM productionDB.schemaname.tablename

    If on different server , try with Linked Server / OPENQUERY

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, September 07, 2013 3:48 PM

All replies

  • You can use INSERT..SELECT just these three tables. like this:

    INSERT Testdb.Tb1 
    	VALUES (<columns>)
    SELECT <columns>
    FROM Productdb.Tb1


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    Saturday, September 07, 2013 3:27 PM
  • Hi,

    If both DB's are in same server , try like this

    INSERT INTO testDB.schemaname.tablename
    SELECT * FROM productionDB.schemaname.tablename

    If on different server , try with Linked Server / OPENQUERY

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, September 07, 2013 3:48 PM
  • how about if they are in a diff server
    Thursday, September 19, 2013 2:49 AM
  • How to select all rows from a table in 1 data base in one computer and migrate over to another  database in another computer

    • Edited by z0802361 Tuesday, October 08, 2013 5:40 AM
    Tuesday, October 08, 2013 1:33 AM
  • i found that using sql export wizard is another way to migrate

    so far it can export data from sql server 2008 instance to sql server 2008 r2

    wonder if this works from sql server 2008 to 2012

    Thursday, October 24, 2013 3:46 AM
  • Several ways,

    use export import wizard or

    if both the servers are on same network  or domain or whatever then

    mention the servername before db name in the same above query and run or

    add one server as linked server and use point to or use openquery or

    write ur own ssis package to do that (step one does the same thing) 

    Thursday, October 24, 2013 4:50 AM