How to transfer data from one table to another table?
In software engineering profession everyday new challenges and problems turn up in the blink of an eye and as engineers we are obliged to resolve them ASAP. One of the biggest challenges in the IT industry today is handling data that is vast and increasing everyday. Data is normally stored in databases in the form of tables and a common situation while handling databases is to transfer data from one table to another.
Case I: If both tables reside in the same database server
Consider the case where you want copy the data from Table_1 under Schema_1 into Table_2 under Schema_2. A simple sql command to do this operation would be
INSERT INTO DB_Name.Schema_2.Table_2
SELECT * FROM DB_Name.Schema_1.Table_1 WHERE [condition]
If you want to insert only certain columns you can slightly tweak the command accordingly
INSERT INTO DB_Name.Schema_2.Table_2(COL1, COL2, ....COLn)
SELECT COL1, COL2, ....COLn FROM DB_Name.Schema_1.Table_1 WHERE [condition]
Case II: Load a table from a file
Consider the situation where you want to load a table from a delimited file. This can be done using the BULK INSERT command in Transact-SQL. In order to perform this operation you need access to the file present in the database server. CSV (Comma-seperated values) is a common format used to store the data from a table or spreadsheet. To load a database table from a .csv file use the following command
BULK INSERT Table_Name
FROM 'C:\Program Files\File.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Bulk insert command provides a slew of options to load a table from a simple delimited text file or even from an xml file.
Case III: If both tables reside in different database servers
The bcp Utility is an excellent command line tool to transfer table data between two different database servers swiftly across a network. You can use bcp to extract table data by querying the table and store it in a delimited file. You can then load the other table from this delimited file easily using a simple bcp command. You don't even have to login to the servers to access the files.
bcp DB1.SCHEMA1.TABLE1 out FILEPATH -n -S[server_name\instance_name] -T -e[error_filepath]
bcp DB2.SCHEMA2.TABLE2 in FILEPATH -n -S[server_name\instance_name] -T -e[error_filepath]
-n uses the native (database) data types
-S should be added before server name
-T using a trusted connection
-e should be added before filepath for error file that logs the failed rows
The first command extracts the rows from TABLE1 and stores in the appropriate FILEPATH (eg. C:\Program Files\File.csv) in the database server. The second command loads TABLE2 with the data present in the File.csv. You have to run the first command to create the file first and then run the second command to load the data into the table. Instead of selecting the entire table you can also include your own select query within the bcp command. BCP offers a legion of options to transfer data between tables in different modes and formats.
Alternately you can also make use of Data Transformation Services (DTS)/ SQL Server Integration Services (SSIS) package in order to consolidate data from heterogeneous data sources.
Useful Resources:
bcp Utility - http://msdn.microsoft.com/en-us/library/ms162802.aspx
bulk insert - http://msdn.microsoft.com/en-us/library/ms188365.aspx
Case I: If both tables reside in the same database server
Consider the case where you want copy the data from Table_1 under Schema_1 into Table_2 under Schema_2. A simple sql command to do this operation would be
INSERT INTO DB_Name.Schema_2.Table_2
SELECT * FROM DB_Name.Schema_1.Table_1 WHERE [condition]
If you want to insert only certain columns you can slightly tweak the command accordingly
INSERT INTO DB_Name.Schema_2.Table_2(COL1, COL2, ....COLn)
SELECT COL1, COL2, ....COLn FROM DB_Name.Schema_1.Table_1 WHERE [condition]
Case II: Load a table from a file
Consider the situation where you want to load a table from a delimited file. This can be done using the BULK INSERT command in Transact-SQL. In order to perform this operation you need access to the file present in the database server. CSV (Comma-seperated values) is a common format used to store the data from a table or spreadsheet. To load a database table from a .csv file use the following command
BULK INSERT Table_Name
FROM 'C:\Program Files\File.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Bulk insert command provides a slew of options to load a table from a simple delimited text file or even from an xml file.
Case III: If both tables reside in different database servers
The bcp Utility is an excellent command line tool to transfer table data between two different database servers swiftly across a network. You can use bcp to extract table data by querying the table and store it in a delimited file. You can then load the other table from this delimited file easily using a simple bcp command. You don't even have to login to the servers to access the files.
bcp DB1.SCHEMA1.TABLE1 out FILEPATH -n -S[server_name\instance_name] -T -e[error_filepath]
bcp DB2.SCHEMA2.TABLE2 in FILEPATH -n -S[server_name\instance_name] -T -e[error_filepath]
-n uses the native (database) data types
-S should be added before server name
-T using a trusted connection
-e should be added before filepath for error file that logs the failed rows
The first command extracts the rows from TABLE1 and stores in the appropriate FILEPATH (eg. C:\Program Files\File.csv) in the database server. The second command loads TABLE2 with the data present in the File.csv. You have to run the first command to create the file first and then run the second command to load the data into the table. Instead of selecting the entire table you can also include your own select query within the bcp command. BCP offers a legion of options to transfer data between tables in different modes and formats.
Alternately you can also make use of Data Transformation Services (DTS)/ SQL Server Integration Services (SSIS) package in order to consolidate data from heterogeneous data sources.
Useful Resources:
bcp Utility - http://msdn.microsoft.com/en-us/library/ms162802.aspx
bulk insert - http://msdn.microsoft.com/en-us/library/ms188365.aspx
