Export data (rows) functionality in SQL 2005

Few days I was struggling with problem of copying data from one table to another table in SQL Server 2005.

Below is the script to copy data (rows) from one database table to another database table.

Syntax:

------------------------------------------------------------------------------
INSERT INTO destination_database_name.DBO.DESTINATION_TABLE_NAME

(
COL1, COL2, COL3
)

SELECT

COL1, COL2, COL3

FROM source_database_name.DBO.SOURCE_TABLE_NAME

WHERE

----------------------------------------------------------------------------

Example:

----------------------------------------------------------------------------

INSERT INTO DBNAME_QA.DBO.COMPANIES --Source Database Table

(
COMPANY_NAME,REGION_ID,ACTIVE,CUSTOMER_NO
)

SELECT

COMPANY_NAME ,REGION_ID,ACTIVE,CUSTOMER_NO

FROM
DBNAME_DEV.DBO.COMPANIES --Destination databse Table

WHERE ACTIVE = 1

-------------------------------------------------------------------------------------
NOTE:

1. Data Type of COL1 should match with data type of COL1
2. You can select desired number of columns.
3. Column with Primary key can not be copied.


It may help you !!!