Happy Ending with BULK INSERT


After playing with bcp for some time, it is just very simple to use BULK INSERT to load data into a table very quickly.



-------fmt format
DECLARE @cmd0 VARCHAR(4000) 
set @cmd0 = 'BCP  [test1].[dbo].[ncoabycounty] format nul   -f  "C:\temp\ncoabycounty.fmt" -c -t\t -T -S'+ @@servername
exec master..xp_cmdshell @cmd0


DECLARE @cmd NVARCHAR(4000) 
set @cmd = 'BCP  [test1].[dbo].[ncoabycounty] OUT  "C:\temp\ncoabycounty.txt" -f  "C:\temp\ncoabycounty.fmt" -c -T  -S'+ @@servername
exec master..xp_cmdshell @cmd


DECLARE @cmd3 NVARCHAR(4000) 
set @cmd3 = 'BCP  [test1].[dbo].[ncoabycounty] IN  "C:\temp\ncoabycounty.txt" -f  "C:\temp\ncoabycounty.fmt" -c -T  -S'+ @@servername
exec master..xp_cmdshell @cmd3



-----************************************
--Quicker
BULK INSERT [test1].[dbo].ncoabycounty FROM 'C:\temp\ncoabycounty.txt' WITH (FORMATFILE='C:\temp\ncoabycounty.fmt');

-----************************************
 

 --Or

 BULK INSERT  [test1].[dbo].ncoabycounty
   FROM 'C:\temp\ncoabycounty.txt'
   WITH 
      (
         FIELDTERMINATOR ='\t',
         ROWTERMINATOR ='\n'
      );




 
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