COPY records from one table of a DB to another table of another DB

[micjohnson]

On my SQL Server, i have 2 databases, DBS1 and DBS2. On DBS1, i have 5000 records on table TBL1. On DBS2, i have a table TBL2 as same columns of TBL1 plus 5 extra columns.

My need is, i need to copy all the records from DBS1.TBL1 to DBS2.TBL2 thru SQL Server query analzer or some other way.

Appreciate your help.

Regards
micJ

 

[jgpasko]

Use Data Transformation Services that is included with the SQL Server bundle. This is the ETL utility that ships with it. You can create a package and map each table field to field while performing other tasks such as populating default values for the additional fields that don't exist in the original table. Once the package is built it is reusable and can be executed as needed.

John Pasko
"No matter where you go, there you are." -- Buckaroo Bonzai

 

[SQLSister]

Are the five extra columns required? ANd are the databases both onthe same server?

if they aren't required and on the same server, then it would be something like:

insert into servername..DBS2 (col1, col2, col3)
select col1, col2, col3 fromservername..DBS1

If they are required and ha ve no default value, it';s something like:

insert into servername..DBS2 (col1, col2, col3)
select col1, col2, 'some value here' fromservername..DBS1

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

 

[micjohnson]

Both databases on the same server. We required the extra 5 columns. Yes.

Regards
micJ