| Author |
Copy data from one DB to another
|
|
| sd_eds 2005-12-09, 11:23 am |
| I have a prodoction database and a test DB. I want to copy the data
from the production to the test. I have already recreated the tables on
the test server with a script. I now just need an easy way to move the
data. I could export the data and then import but wouldnt that create
duplicate tables in the database?
Thanks in advance
| |
|
| insert into testDB.dbo.tablename
select * from
productionDB.dbo.tablename
just do it for all the tables
If you do the following below it will also create the tables for you
(without the indexes and constraints)
select * into testDB.dbo.tablename
from
productionDB.dbo.tablename
You could also backup the production DB and restore it as the test DB
(this is the easiest way in my opinion)
http://sqlservercode.blogspot.com/
| |
| sd_eds 2005-12-09, 11:23 am |
| Sorry but I forgot to mention that the production and test DBs reside
on seperate servers
| |
|
| then create a linked server between them use this
insert into TestServer.testDB.dbo.tablename
select * from
Productionserver.productionDB.dbo.tablename
Basically ServerName.DatabaseName.ObjectOwner.Object
If you server name is SdEds and you want to use the pubs DB you would
use SdEds .pubs.dbo.publishers
http://sqlservercode.blogspot.com/
| |
| Andrew J. Kelly 2005-12-09, 11:23 am |
| Why not do a backup and restore? That will give you an exact duplicate of
the database including the data with minimal effort.
--
Andrew J. Kelly SQL MVP
"sd_eds" <sd_eds@hotmail.com> wrote in message
news:1134142957.766301.313030@o13g2000cwo.googlegroups.com...
>I have a prodoction database and a test DB. I want to copy the data
> from the production to the test. I have already recreated the tables on
> the test server with a script. I now just need an easy way to move the
> data. I could export the data and then import but wouldnt that create
> duplicate tables in the database?
>
> Thanks in advance
>
| |
| sd_eds 2005-12-09, 11:23 am |
| Would I have to blow away the current db on the test server?
| |
|
| >>Would I have to blow away the current db on the test server?
No, you can create another one (test2 for example) and restore it as
that DB
http://sqlservercode.blogspot.com/
| |
| Andrew J. Kelly 2005-12-09, 1:24 pm |
| I thought you wanted a copy of production? Yes you can restore it to
another db name but now you still have to get the data from that db to the
test one. If you only want a select few tables you might be better off
using DTS or SSIS to copy just those tables.
--
Andrew J. Kelly SQL MVP
"sd_eds" <sd_eds@hotmail.com> wrote in message
news:1134145919.200856.148870@g43g2000cwa.googlegroups.com...
> Would I have to blow away the current db on the test server?
>
|
|
|
|