SQL Server : Bulk insert from one table to another

Updated on October 2, 2015     Kisan Patel

Problem:

How to copying a huge table data into another table in sql server?
Copying Data Between Servers
How to Bulk insert from one table to another in SQL Server?

Solution:

To Bulk insert from one table to another, you need to use the bcp command-line utility.

The following example bulk exports data in native format from the myTestNativeData table into a new data file named myTestNativeData-n.Dat data file.

Exec xp_cmdShell 'bcp "[AdventureWorks].[dbo].[Users]" out "C:\myTestNativeData-n.Dat" -n -T'

Or you can run below command in command prompt.

bcp "[AdventureWorks].[dbo].[Users]" out "C:\myTestNativeData-n.Dat" -n -T

The following example uses BULK INSERT to import the data in the myTestNativeData-n.Dat data file into the myTestNativeData table. In SQL Server Management Studio Query Editor, execute:

BULK INSERT [AdventureWorks].[dbo].[Another_Users] 
    FROM 'C:\myTestNativeData-n.Dat' 
   WITH (DATAFILETYPE='native');