Bulk Insert witha variable

You can’t use a variable as a table reference in a BULK insert (or any
SELECT for that matter).  To do so, you need to use dynamic SQL, but I would be very careful that you know what you are doing if you decide to step into dynamic SQL!

To illustrate dynamic SQL is a bit too much for this simple reply, but one tip is to never use:

“EXEC @SQL”

But to use

Execute sp_execute_sql @SQL

If you do a quick search on dynamic SQL you should come up with a few decent hits among the clutter.  SQLServerCentral.com is a good place to start.

J

-Ryan

Hey folks,

This SQL thing is kind of new to me, so please forgive me if this is a really basic question.

Why can't I use a variable for the Bulk Insert command? Is there a way to get around this? Here's what I'm working with:

DECLARE
@emarket_ind int,
@csvfile varchar(MAX)

CREATE TABLE #table01 (
id int identity,
email varchar(MAX),
customer_no int)

BULK
INSERT #table01
FROM @csvfile
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Thanks in advance for any help you can provide!

Rey

The Public Theater

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

You can’t use a variable as a table reference in a BULK insert (or any
SELECT for that matter).  To do so, you need to use dynamic SQL, but I would be very careful that you know what you are doing if you decide to step into dynamic SQL!

To illustrate dynamic SQL is a bit too much for this simple reply, but one tip is to never use:

“EXEC @SQL”

But to use

Execute sp_execute_sql @SQL

If you do a quick search on dynamic SQL you should come up with a few decent hits among the clutter.  SQLServerCentral.com is a good place to start.

J

-Ryan

Hey folks,

This SQL thing is kind of new to me, so please forgive me if this is a really basic question.

Why can't I use a variable for the Bulk Insert command? Is there a way to get around this? Here's what I'm working with:

DECLARE
@emarket_ind int,
@csvfile varchar(MAX)

CREATE TABLE #table01 (
id int identity,
email varchar(MAX),
customer_no int)

BULK
INSERT #table01
FROM @csvfile
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Thanks in advance for any help you can provide!

Rey

The Public Theater

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!