How do I copy the data from tables is one db to the same db on the test box?

Question

How do I copy the data from tables is one db to the same db on the test box?

Keith Knox

Mr or Mrs. 500

Points: 588
More actions
February 27, 2008 at 12:22 pm

#120044

How do I copy the data from tables is one db to the same db on the test box?
I tried the import wizard and got an error about a primary key contraint in a table.
I need to overwrite the data in the existing tables on the db on the test server.
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jsheldon SSCertifiable Points: 5250 More actions · Answer 1

Keith try this:

1. In the source server via Studio Management, highlight the table, right-click and choose Script Table As Create To New Query

2. Save this query as a .sql file

3. Open Studio management for the test server

4. Do a file open and select the sql file, adjust to your db

5. Execute the sql script

6. You know have a copy of the production table infrastructure into text box

7. Go back to source and highlight the table run the export/import wizard

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 2

Thanks. However.

It doesn't run due the tables already existing. Is there a problem with the import overwriting existing tables? I guess I could do a backup/restore as well, it just seemed easier if the import would work.

jsheldon SSCertifiable Points: 5250 More actions · Answer 3

There is an option in the export/import to append data but if this is test, I feel it is much easier to do a backup of production, then do a restore to test

Then reset your user ID orphans

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Two possibilities:

1. use SSIS. your source would be Prod, your destination would be Test. You can then use a lookup and a conditional split to then figure out what to copy over.

2. add a linked server to one of the boxes, and run the loading as a regular T-SQL statement over the linked server connection.

2. is likely less of a learning curve. 1. might be better if you intend to do this a lot and you want a lot of options.

If you don't mind a straight "overwrite, then drop the destination table first and recreate it as part of the move data solution you pick.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 5

Thanks for the good comments.

I tried deleting the tables in the gui but some have dependencies and gave foreign key contraint errors and did not delete. I may be due to a job that runs an ssis package. Anyway, how can I delete these few tables that have issues. thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

How do I copy the data from tables is one db to the same db on the test box?
I tried the import wizard and got an error about a primary key contraint in a table.
I need to overwrite the data in the existing tables on the db on the test server.

This means the DDL is deferent for the tables. Are you sure you want to have different integrity for the destination data? Meaning your source table must not have the same constraints as the destination. Are you sure you want to allow the use of duplicate keys, in the destination?

Thanks for the good comments.
I tried deleting the tables in the gui but some have dependencies and gave foreign key contraint errors and did not delete. I may be due to a job that runs an ssis package. Anyway, how can I delete these few tables that have issues. thanks

Drop the constraints, drop the table, create the new table, recreate the constraints

My blog: http://jahaines.blogspot.com