Migrate data from one database table to another one on another server in MSSQL

Question

I'm currently maintaining a website done with HTML/CSS/Javascript/PHP that uses a mssql database. We have one database for development purposes on a dev server and another one on a production server. On both the dev and prod server, there is a data.DailyNormalData. I would like to take every row in the dev database and put them in the existing table in the prod database (after erasing everything in the prod database) as the data in the prod database is not up to date.

My question is, how could I achieve this using Microsoft SQL Server Management Studio 2012? I have searched on how to do this, but have not found exactly a solution for my specific needs.

score 1 · Accepted Answer · answered Jun 17 '14 at 15:08

1

You should use the Import/Export wizard in Management Studio. Your dev database will be your source, and your production database will be your destination. The data.DailyNormalData table should be shown in the list. (If you like, you can clear out the destination table with DELETE FROM data.DailyNormalData or TRUNCATE TABLE data.DailyNormalData.)

answered Jun 17 '14 at 15:08

Banana Boat

86
3

Thanks for the answer. Just wanted to add that I had to follow another step in order to get it working. See this links for details: http://stackoverflow.com/questions/20948819/error-0xc0202049-data-flow-task-1-failure-inserting-into-the-read-only-column – Choub890 Jun 17 '14 at 15:48

score 1 · Answer 2 · answered Jun 17 '14 at 15:09

1

Try this:

right click on Database -> Tasks -> Export Data -> Follow instructions

answered Jun 17 '14 at 15:09

Narti

181
7

score 0 · Answer 3 · answered Jun 17 '14 at 15:42

0

You could create linked servers http://msdn.microsoft.com/en-us/library/ff772782.aspx

answered Jun 17 '14 at 15:42

MarianP

2,719
18
17

Migrate data from one database table to another one on another server in MSSQL

3 Answers3