Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our users want to refresh a QA database from production but want two tables (lets call them T1 and T2) to retain the original QA data. So I copied two tables from QA (DB1) to a temp QA database (DB2). Then refreshed DB1 from production. After the refresh, I want to overwrite T1 and T2 data from DB2 to DB1 so it can contain pre-refresh QA values.

I have done the following:

  1. Use

    select * 
    INTO D1.dbo.T1
    FROM D2.dbo.T1
    
  2. Then refreshed D1 from prod

  3. Then truncate T1 with the following step:

    SELECT COUNT(*) AS BeforeTruncateCount
    FROM T1;
    GO
    TRUNCATE TABLE T1;
    GO
    SELECT COUNT(*) AS AfterTruncateCount
    FROM T1;
    GO
    
  4. Now when I go back to copy data from D2.T1 to D1.T1, I get the error that there is already an object named T1 in the database.

Should I drop the table and copy?

Or is there any better method for the whole procedure?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

To combine the answers from @Kin and @DavidSpillett.

First an assumption. DB1 and DB2 are on the same instance. It certainly sounds like they are but it never hurts to state the obvious (particularly since I've been wrong with what I thought was obvious before.)

Given that, if the data is large, say multiple millions of rows then use an SSIS package. This has one specific feature that you will want to use. You can specify a batch size. This way your transaction size will be smaller. The downside to the SSIS package is that it adds a level of complexity to your process. It isn't a big one, but if you are working with a smaller table you don't need it.

Which leads us to the other option. If however the data is in the several hundred thousand range or smaller then use code like this.

USE D1;
GO
TRUNCATE TABLE dbo.T1;
GO
INSERT INTO D1.dbo.T1
SELECT * 
FROM D2.dbo.T1;
GO

And last but not least it if it is somewhere on the boarder then I would try both and see what works best for you.

NOTE: You might also consider not using a SELECT * on the INSERT INTO and instead specify a field list.

That structure would look like this

INSERT INTO D1.dbo.T1 (Field1, Field2)
SELECT Field1, Field2
FROM D2.dbo.T1;
share|improve this answer
    
Thanks for the info. Yes, the table has only 7800 rows so it is fairly small and on the same instance. I have tried using INSERT INTO (without select) and it worked perfectly. Thank you sharing this. Also I have tested with Import Data with SSMS. That worked great too. –  Rizwan Jul 30 '13 at 13:41
add comment

IIRC with the SELECT <stuff> INTO <target> FROM ... syntax SQL Server always assumes you are wanting to create the destination table from scratch. When the table already exists try INSERT <target> SELECT <stuff> FROM ... instead.

share|improve this answer
2  
In addition, David's answer is also better route than a DROP and SELECT INTO when moving the data back to D1, because if you dropped the D1 tables, you would lose any child objects (ie. foreign keys, primary keys, triggers, etc.) –  Delux Jul 30 '13 at 12:35
    
I agree with @Delux. Another important thing that you will lose is explicit permissions. If a user has permissions specific to that table (rather than say db_datareader) then if you drop the table then you lose those permissions. –  Kenneth Fisher Jul 30 '13 at 13:04
    
That is a great tip. I didn't understand ramifications of DROP till now. Thank you! –  Rizwan Jul 30 '13 at 13:42
add comment

Best is to use SSIS (data Import/export) to do this. It has the functionality to just transfer selected tables from source to destination. Optionally, you can even save the package so that it can be reused for future/repeated transfers.

EDIT: Adding more screenshots for clarity.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

share|improve this answer
    
That is a great tip. Thank you –  Rizwan Jul 30 '13 at 13:43
    
wow.. thank you for great screenshots.. they are a great help –  Rizwan Jul 31 '13 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.