Copy One Table

[pthalacker]

I have a test database that I use backup and restore to keep updated with production data. But if I just want to update the data in one table, what is the quickest, easiest, down-and-dirty way.

pamela

 

[SQLDenis]

copy table from enterprise manager would be the easiest
right click the table that you want to 'import' select all tasks-->export data from then on it's an easy wizard

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[pthalacker]

Hi SQLDenis,

That gives me the same error I get when I try to use BCP. I get a uniqueness of primary key violation error on the first row and nothing is copied. A number of rows have been added to the production table and I want to add them to the test table.

pamela

 

[SQLDenis]

2 ways (of course there are more)
1 delete the table before moving the data over or delete the data from the table (make sure you don't delete the production table)

or bcp into a staging table and do
1 left join
2 not exists
3 not in

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Ok you may have several problems. First, you may simply need to go to the transform (on the select source tables and views page of teh wizard)and select drop and recreate the table to clear out all the old records first. By default it selects append. ANd clearly you can't append an exisitng record based on the PK you have defined. Or you could select query intstead of table and write a query left joining the two tables and excluding the recrods which exist in both tables. If you need to update those records, either drop and recreate the table or run an update and then insert statement in QA rather than use the wizard to send data.

Another possible problem is that you may run into is trying to import records which do not have exsisting records in a parent table. If this is happening, you need to update teh paraent table first before trying to refresh this table.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[pthalacker]

SQLDenis

or bcp into a staging table and do
1 left join
2 not exists
3 not in

That sounds like what I want to do. Can you give me a few more details to get me started.

SQLSister - I can't delete the data from the existing table because there is a constraint that won't let you delete rows that are referenced in another table (as they all are)

Deleting the table completely might be a possiblity.

pamela

 

[SQLDenis]

sure
select * into StagingTable from YourBackupTable
where 1 =0

this will create an identical copy of the backup table without constraints, indexes etc

then BCP into this table

then left join or use not exists to grab the new rows

insert into BackupTable
select b.*
from BackupTable b
where not exists(select * from StagingTable s where b.Keycolumn =s.Keycolumn)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/