Tuesday, August 30, 2011

Different methods of moving data between 2 tables

1.    Insert into db.schema.table select * from db.schema.tab where condition
2.    Insert into db.schema.tab (col1,col2,col2,…) select col1,col2,col3,… from db.schema.tab where condition
3.    Bcp db.schema.tab OUT filepath –n –s(servername/instancename) –t –e(error file path).
4.    Bcp db.schema.tab IN filepath –n –s(servername/instancename) –t –e(error file path).
5.    We can use DTS/SSIS/BULK INSERT commands.
6.    BULK INSERT Table_Name
FROM 'C:\Program Files\File.csv'
7.    Create a new table (duplicate structure), located in the new filegroup.
Copy the current table's data to the new table.
Remove any PK-FK relationships.
DROP the old table
RENAME the new table to the old table name
Re-create the PK-FK relationships.
Drop any existing CLUSTERED index.
Re-Create the CLUSTERED index on the new filegroup

-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

No comments:

Post a Comment