CODEWITHSUNDEEP
sqlsql-serverssis
Elias
Elias

Reputation: 2632

Use SSIS To Copy A Table's Structure And Data With A Different Name

Using SSIS and the example tables myTable and Test_myTable I would like to:

Is there a particular task that does this? I was looking into the property Expression Editor in the Transfer SQL Server Objects Task Editor and thought that putting an expression on the CopyAllObjects property would work but I'm not sure if that is the correct path.

Upvotes: 0

Views: 661

Answers (1)

M.Ali
M.Ali

Reputation: 69494

I guess you can make use of Execute Sql Task for this and simply execute the following statements inside your task.

Instead of Drop and Create simply Truncate table, as dropping table means you will have to give permission to users if you have some sort of restrictions and only some specific user who can access the data.

Without Dropping the table

TRUNCATE TABLE Test_myTable;
GO

INSERT INTO Test_myTable (Col1, Col2, Col3, .....)
SELECT Col1, Col2, Col3, .....
FROM myTable
GO

Drop Table and Create

If for some reason you have to drop table and re-create it again you could execute the following statements inside your execute sql task.

--Drop tables if exists

IF OBJECT_ID('dbo.Test_myTable', 'U') IS NOT NULL
  DROP TABLE dbo.Test_myTable
GO

--Create and populate table
SELECT Col1, Col2, Col3, .....
INTO dbo.Test_myTable
FROM myTable
GO

Upvotes: 2

Related Questions