Copying Data From One Database To Another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Copying Data From One Database To Another

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-06-11 : 15:07:20

We are in the process of moving data from our test database to our production database. Any suggestions on which tool/script I could use in order to copy data from one database to another? Will it also give me an option of either overwriting old data or appending data? Any help would be greatly appreciated. Thanks.

Brillix
Starting Member

3 Posts

Posted - 2009-06-11 : 16:06:12

Right click on the database you want to move and select export - the export wizard will show - use it

Oded Raz
www.dbsnaps.com
www.orbiumsoftware.com

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-06-11 : 17:09:43

Thanks. This is an excellent tool for our purposes then. One other question, if I were to use the ff statement -

INSERT INTO JB_Inventory (
[SS_Item_Location_ID],
[SS_Item_ID],
[SSRoomID],
[On_Hand],
[Item],
[Room_Name],
[Catalog_Number])
SELECT SS_Item_Location_ID,
SS_Item_ID,
SSRoomID,
On_Hand,
Item,
Room_Name,
Catalog_Number
FROM Apollo_Test.dbo.JB_Inventory
WHERE SSRoomID = 7

Apollo_Test being the database name and I need to copy selected records from test database to prod database from a PC workstation to a laptop, can I use the ip address and qualify the FROM clause in my insert statement?

INSERT INTO JB_Inventory (
.
.
.
FROM 172.xx.xx.xxx.Apollo_Test.dbo.JB_Inventory
WHERE SSRoomID = 7

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-06-15 : 03:52:11

quote:
Originally posted by josh2009

Thanks. This is an excellent tool for our purposes then. One other question, if I were to use the ff statement -

INSERT INTO JB_Inventory (
[SS_Item_Location_ID],
[SS_Item_ID],
[SSRoomID],
[On_Hand],
[Item],
[Room_Name],
[Catalog_Number])
SELECT SS_Item_Location_ID,
SS_Item_ID,
SSRoomID,
On_Hand,
Item,
Room_Name,
Catalog_Number
FROM Apollo_Test.dbo.JB_Inventory
WHERE SSRoomID = 7

Apollo_Test being the database name and I need to copy selected records from test database to prod database from a PC workstation to a laptop, can I use the ip address and qualify the FROM clause in my insert statement?

INSERT INTO JB_Inventory (
.
.
.
FROM 172.xx.xx.xxx.Apollo_Test.dbo.JB_Inventory
WHERE SSRoomID = 7

you do not need specify IP address at the query level, when u run the export wizard it will ask you to connect the source and destination database; also u have an option to transfer the data by specifying a query.

Regards,
Ahmad Osama

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources