question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

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
sqlbulk-insertexecute
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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 '.'
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
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!
1 Like 1 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Now I feel really stupid, I thought I'd tried that early on. Yes of course it's working fine now. Doh!!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.