x

Dynamic SQL to run Bulk Insert

Hi, I wonder if anyone can help. I am creating dynamic SQL using variables so that I can use the same script for various imports.

The script is:

DECLARE @sSourceType AS VARCHAR(10)
DECLARE @sPath AS VARCHAR(50)
DECLARE @sFileName AS VARCHAR(50)
DECLARE @sSourceTableName AS VARCHAR(50)
DECLARE @sSourceFMTFile AS VARCHAR(50)
DECLARE @iSourceID AS INT
DECLARE @SQL AS VARCHAR(500)

SET @iSourceID = (SELECT TOP 1 iID FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sPath = (SELECT TOP 1 sPath FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sFileName = (SELECT TOP 1 sFileName FROM SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sSourceTableName = (SELECT TOP 1 sSourceTableName FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sSourceFMTFile = (SELECT TOP 1 sSourceFMTFile FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))

PRINT @sSourceTableName

EXEC ('Truncate table ' + @sSourceTableName )
PRINT  @sSourceTableName + ' has been truncated'

SET @SQL = 
('BULK INSERT ' + @sSourceTableName + '
FROM ''' + @sPath + @sFileName + '''
WITH (FORMATFILE = ''' + @sSourceFMTFile + ''')')

EXEC @SQL

When you copy the printed script which looks like this:

BULK INSERT PriceImportHoldingEW
FROM 'C:\Auto Import\Auto Import\EandWTest.txt'
WITH (FORMATFILE = 'C:\Auto Import\EandWFormatFile.fmt')

It works fine, but when you to try to execute it within the script is has the error message:

Msg 911, Level 16, State 4, Line 27 Database 'BULK INSERT PriceImportHoldingEW FROM 'C:\Auto Import\Auto Import\EandWTest' does not exist. Make sure that the name is entered correctly.

I am now completely stumped. Any ideas greatfully received.

Many thanks
more ▼

asked May 18 '11 at 04:50 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 57 62 66

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Shouldn't that be

EXEC (@SQL)
You need parentheses around the string otherwise it thinks it's executing a stored proc, or some other module of the type database.schema.object, hence the error saying it can't find the database 'BULK INSERT PriceImportHoldingEW FROM 'C:\Auto Import\Auto Import\EandWTest' - i.e. everything up to the first '.'
more ▼

answered May 18 '11 at 05:03 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Now I feel really stupid, I thought I'd tried that early on. Yes of course it's working fine now. Doh!!
May 18 '11 at 05:05 AM Mrs_Fatherjack
Nah don't feel stupid - this catches me out soooooo many times - it's made worse by the fact that when you select out the contents of the string it looks perfect!
May 18 '11 at 05:16 AM Kev Riley ♦♦

The number of times I get caught out by that one too... It doesn't help that the syntax of the EXECUTE statement does allow for use without parentheses under certain circumstances. The gist of it seems to be if it's a real SP call (eg EXEC sp_foo @bar=1) then you don't need parens, but if it's anything remotely dynamic, then parentheses are required.

http://msdn.microsoft.com/en-us/library/ms188332.aspx
May 19 '11 at 06:03 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x673
x31
x16

asked: May 18 '11 at 04:50 AM

Seen: 1796 times

Last Updated: May 19 '11 at 12:47 AM