BULK INSERT FROM <string variable> 1

[GHolden]

I would like to change this

BULK INSERT tblImportTest
FROM '\\CHATS-FS1\ENERGY\FENSA\SQL\TEST.TXT'
WITH
(
FORMATFILE = '\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt'
)

So eventually I can pass the File Name. I have tried this as a test..

DECLARE @FileName VARCHAR(50)

SET @FileName = '\\CHATS-FS1\ENERGY\FENSA\SQL\Test.txt'

BULK INSERT tblImportTest
FROM @FileName
WITH
(
FORMATFILE = '\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt'
)

The FROM @FileName generates an error. Is there a way to do this?

Thanks.

There are two ways to write error-free programs; only the third one works.

 

[tlbroadbent]

You'll have to create and Execute a dynamic SQL statement. Here is one way to do this.

DECLARE @FileName VARCHAR(50), @sql varchar(1000)

SET @FileName = '\\CHATS-FS1\ENERGY\FENSA\SQL\Test.txt'
SET @sql=
'BULK INSERT tblImportTest FROM ' + @FileName +
' WITH (FORMATFILE = ' +
'''\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt'')'

Exec (@sql) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[GHolden]

Thanks for the quick response. I can parse this code, but when I try to execute it I get..

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '\'.

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

It's ok, fixed it...

DECLARE @FileName VARCHAR(50), @sql varchar(1000)

SET @FileName = '\\CHATS-FS1\ENERGY\FENSA\SQL\Test.txt'
SET @sql=
'BULK INSERT tblImportTest FROM ''' + @FileName +
''' WITH (FORMATFILE = ' + '''\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt'')'
print @sql

Exec (@sql)

Thanks.

There are two ways to write error-free programs; only the third one works.