Migrate Data with BCP

  

 
 declare @qrytext varchar(1000), @filepath varchar(1000)
 set @filepath = '  "C:\DATA\mytable_Data.txt" ' 
set @qrytext = '"SELECT   t1.col1,t2.col2,t3.col3 ,t3.col4 from BD1.dbo.t1 t1 JOIN myDB.dbo.t2 t2 on t1.id = t2.oldid JOIN myDB.dbo.t3 p on t1.id2 = t3.oldid "'
--@qrytext should be in one line

------ generate format file and need to remove the first row in the format file to skip a table column
----DECLARE @cmd1 VARCHAR(4000) 
----set @cmd1 ='bcp   [myDB].[dbo].[mytable]  format nul  -f C:\DATA\mytableFormatFile_Raw.fmt  -c -t\t -r\n -T -S '+ @@servername;;
----exec master..xp_cmdshell @cmd1--, NO_OUTPUT

--export data
 DECLARE @cmdOut VARCHAR(4000) 
set @cmdOut ='BCP ' + @qrytext + ' QUERYOUT ' + @filepath + ' -t\t -r\n -c -T -S '+ @@servername;;
exec master..xp_cmdshell @cmdOut, NO_OUTPUT


 --import data
DECLARE @cmdIn VARCHAR(4000) 
set @cmdIn = 'bcp  myDB.dbo.[mytable] IN ' + @filepath + ' -f C:\DATA\mytableFormatFile.fmt -T -S '+ @@servername
exec master..xp_cmdshell @cmdIn, NO_OUTPUT

 ----delay 2 seconds
 WAITFOR DELAY '00:00:02' 

--clean up
DECLARE @cmdDelete VARCHAR(4000) 
SET @cmdDelete ='del '+ @filepath
exec  master..xp_cmdshell @cmdDelete, NO_OUTPUT




 
Advertisements

Bulk Insert Fixed Length Text File

  

 use myDb;

--create destination table structure 
create table bcpTest
(
mob  char(3),
acct  char(5),
mmid  char(4) 
)


 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t ""  -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd


--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt'
WITH
(
FORMATFILE ='E:\DATA\myFormatFiletest.fmt',
ERRORFILE = 'E:\DATA\ERROR_FILE_UD3.log'
)
select * from bcpTest
 

 Drop table bcpTest




 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d93ac67-7499-4614-b4ea-3c81daf64d0f/bcp-width-range?forum=transactsql