Copy data in Sql Server table A to B on same server - Identical schemas

Greetings,

I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)

Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination

Thanks,

Black Cat Bone

Answer this question

Copy data in Sql Server table A to B on same server - Identical schemas

Lawrence Parker
Create an OLE DB Source and perform your data select within that. Then simply attach it to an OLE DB Destination, where you select your destination table. Easy and it will allow you to change your destination later when you move TABLE B to another server, or whatever.
Stojance
I would add to Phil's comment: Create separate connection managers for Source and destination components. If both tables are in the same server, having a single connection manager is fine; but once you move table B to a new server, an additional connection manager will be needed; so do it from the begining so you don't have to modify the package later.
Chaz Clover

Phil's right. That's exactly what it is. Its explained a bit more here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/4344.aspx)

-Jamie

kleinma

Rafael Salas wrote:
I would add to Phil's comment: Create separate connection managers for Source and destination components. If both tables are in the same server, having a single connection manager is fine; but once you move table B to a new server, an additional connection manager will be needed; so do it from the begining so you don't have to modify the package later.

Yep, that's what I had in my head, but didn't state it. Thanks, Rafael!

stephane - Montpellier
Hello,

I did not explain well. Inserting rows from table A into table B is just one small piece of the overall SSIS package. I've got the two tables on the same server now just to get the package running in a "semi-realistic" setting - long run, tables A and B will be on different servers. I don't have permission to access the other database/server yet so I'm approximating the eventual package by having the two tables in the same server. This will allow me to get the other tasks operating properly.

Can you provide guidance on the best way to insert rows from A into B within SSIS

Thanks,

BCB
Terry A. King
Thank you - it worked when I used the OLE DB source and destination. I am just wondering if this could have worked with the SQL Server Destination, which is what I was trying to use. I'm also wondering to myself why there is no SQL Server Source.

Regards,

BCB
nordwindranger
You could use the SQL Server destination provided that the package is being executed on the SQL Server itself, as my understanding is that the SQL Server destination is really an in-memory hook to SQL Server.
Dylan Beattie

If they are in the same database then a SQL statement should do it. You don't need SSIS.

INSERT INTO TableB SELECT <column-list> FROM TableB

-Jamie
Copy data in Sql Server table A to B on same server - Identical schemas

Answer this question