call bcp using tsql

exec master..xp_cmdshell ‘bcp “SELECT * FROM OPENQUERY(SERVER,”select * from DBASE.SCHEMA.TABLE’) ” queryout c:\test.txt -U LOGIN -P PASSOWRD -c -t “|*|”‘ –,NO_OUTPUT

This works with a sql login, had login issues using windows authentication, i use |*| as a column delimiter in this example

Import it using

BULK INSERT destination_table FROM ‘c:\test.txt’ WITH(FIELDTERMINATOR=’|*|’,ROWTERMINATOR=’0x0a’,FIRSTROW=1,TABLOCK,batchsize=50000,MAXERRORS=0)

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


%d bloggers like this: