CODEWITHSUNDEEP
sql-serverssis
Josien
Josien

Reputation: 13907

Create a copy of a table within the same database with SSIS

I want to create a copy of a table, say TestTable, with a new name, say TestTableNew, in the same database with the use of an SSIS package. I've created a "Transfer SQL Server Objects Task" for this with the source database specified as both the SourceDatabase and the DestinationDatabase. When I run this task, the original table TestTable is overwritten with a new -empty- TestTable.

This might well be something really obvious that I've overlooked, but can I somehow specify another name for the destination table somewhere in this transfer task? Or should I solve this in another way?

Upvotes: 2

Views: 6768

Answers (2)

Diego
Diego

Reputation: 36176

You can't use the "Transfer SQL Server Objects Task" to copy a table to the same database because there isn't an option to specify the new table name. You would be copying table "TestTable" to table "TestTable", which will fail because they both have the same name.

You can set the "DropObjectsFirst" property to true, but that will make you lose your original table and its data, which I think you did on your test, otherwise you would have received a failure message.

The best option here is to use an "Execute SQL Task" to create the structure of your TestTableNew based on your TestTable and then do a simple OleDBSource -> OleDBDestination transformation to load all the data from one table to another.

Upvotes: 3

Chris Moutray
Chris Moutray

Reputation: 18399

My knowledge of SSIS is very limited but I assume you can run sql commands passing in parameters and therefore generating something like the following dynamically 

select *
insert into TestTableNew
from TestTable

Upvotes: 0

Related Questions