Posted: April 30, 2018 | Author: Jingyang Li | Filed under: Uncategorized |
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
Posted: April 13, 2018 | Author: Jingyang Li | Filed under: Uncategorized |
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