BULK INSERT using variables

[Jeff Jones]

I'd like to "pass" the BULK INSERT statement a variable for the table_name and file_name arguments. I've tried the following:

-----
BULK INSERT @strTableName
FROM @strDataFileName
WITH
(
FIELDTERMINATOR = " ",
ROWTERMINATOR = "
"
)
-----

where the @strTableName and @strDataFileName variables are passed in via stored procedure arguments. I get the error:

Incorrect syntax near '@str_TableName'.

Is there a way to do this?
Thanks!
Jeff

[Paul]

why have you got an underscore in @str_TableName when there isn't one in the insert statement.

What are u trying to achieve ? can u use DTS instead ?


------------
Jeff Jones at 2/28/01 12:49:12 PM


I'd like to "pass" the BULK INSERT statement a variable for the table_name and file_name arguments. I've tried the following:

-----
BULK INSERT @strTableName
FROM @strDataFileName
WITH
(
FIELDTERMINATOR = " ",
ROWTERMINATOR = "
"
)
-----

where the @strTableName and @strDataFileName variables are passed in via stored procedure arguments. I get the error:

Incorrect syntax near '@str_TableName'.

Is there a way to do this?
Thanks!
Jeff

[Jeff Jones]

My bad on the underscore. I'm trying to write 1 sp and use it over and over to import many different files into many different tables. DTS works, but I haven't figured out a way to "store" DTS jobs so that I can reuse them.
Jeff


------------
Paul at 3/1/01 12:48:23 PM

why have you got an underscore in @str_TableName when there isn't one in the insert statement.

What are u trying to achieve ? can u use DTS instead ?


------------
Jeff Jones at 2/28/01 12:49:12 PM


I'd like to "pass" the BULK INSERT statement a variable for the table_name and file_name arguments. I've tried the following:

-----
BULK INSERT @strTableName
FROM @strDataFileName
WITH
(
FIELDTERMINATOR = " ",
ROWTERMINATOR = "
"
)
-----

where the @strTableName and @strDataFileName variables are passed in via stored procedure arguments. I get the error:

Incorrect syntax near '@str_TableName'.

Is there a way to do this?
Thanks!
Jeff

[Sumit]

You can do it this way :-

DECLARE @QueryStr varchar(250)
SET @QueryStr = 'BULK INSERT ' + @strTableName +
' FROM ' + @strDataFileName +
' WITH
(
FIELDTERMINATOR = " ",
ROWTERMINATOR = "
"
)'

Exec (@Querystr)

Sumit

------------
Jeff Jones at 3/1/01 2:35:06 PM

My bad on the underscore. I'm trying to write 1 sp and use it over and over to import many different files into many different tables. DTS works, but I haven't figured out a way to "store" DTS jobs so that I can reuse them.
Jeff


------------
Paul at 3/1/01 12:48:23 PM

why have you got an underscore in @str_TableName when there isn't one in the insert statement.

What are u trying to achieve ? can u use DTS instead ?


------------
Jeff Jones at 2/28/01 12:49:12 PM


I'd like to "pass" the BULK INSERT statement a variable for the table_name and file_name arguments. I've tried the following:

-----
BULK INSERT @strTableName
FROM @strDataFileName
WITH
(
FIELDTERMINATOR = " ",
ROWTERMINATOR = "
"
)
-----

where the @strTableName and @strDataFileName variables are passed in via stored procedure arguments. I get the error:

Incorrect syntax near '@str_TableName'.

Is there a way to do this?
Thanks!
Jeff