Move Data between SQL Server Tables Fast — Use Native Format with bcp


bcp -n
Performs the bulk-copy operation using the native (database) data types of the data.



DECLARE @cmd100N NVARCHAR(4000) 
set @cmd100N = 'BCP  [test1].[dbo].[address] OUT  "C:\temp\address_N.txt"  -n -T  -S'+ @@servername
--Batch sizes are not applicable when you bulk export data from an instance of SQL Server to a data file.


exec master..xp_cmdshell @cmd100N
set @cmd100N = 'BCP  [test1].[dbo].[address] format nul   -f  "C:\temp\address_N.fmt" -n   -T  -S'+ @@servername
exec master..xp_cmdshell @cmd100N

set @cmd100N = 'BCP [testdb].[dbo].address  IN  "C:\temp\address_N.txt" -f  "C:\temp\address_N.fmt"  -b 100000  -T -S'+ @@servername
exec master..xp_cmdshell @cmd100N


--select * from [testdb].[dbo].address
-- truncate table [testdb].[dbo].address

--If the tables are the same structure, you can skip the fmt file to make it simple.
DECLARE @cmd10N NVARCHAR(4000) 
set @cmd100N = 'BCP  [test1].[dbo].[address] OUT  "C:\temp\address_N.txt"  -n -T  -S'+ @@servername
--Batch sizes are not applicable when you bulk export data from an instance of SQL Server to a data file.

set @cmd10N = 'BCP [testdb].[dbo].address  IN  "C:\temp\address_N.txt" -n -b 100000  -T -S'+ @@servername
exec master..xp_cmdshell @cmd10N



 

Use Native Format to Import or Export Data
http://msdn.microsoft.com/en-us/library/ms191232.aspx
http://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s