Bulk Insert using scalar variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bulk Insert using scalar variable

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-07-05 : 11:34:33

Hi,

I receive the error:
Must declare the scalar variable "@Temp1".

Can someone see the error I am doing and is my code okay?

DECLARE @FilePath  varchar(100)
      SET  @FilePath = '\\servername\rawdata\textfile.txt'

DECLARE  @Temp1 TABLE
(
[FileName] nvarchar(50),
heure  int,
success int,
reject int,
Datein char(8)
)
DECLARE @sql NVARCHAR(200) = 'BULK INSERT ''' + @Temp1 + ''' FROM  ''' + @FilePath + ''' WITH ( formatfile =\\servername\rawdata\format\fileformat.FMT)'
EXEC(@sql)

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-05 : 11:56:54

You can't use a table variable for that as the dynamic SQL execute in another session where the table variable doesn't exist. Use a regular table or a global temp table. You may be able to stick the entire script into @sql though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 11:59:57

There are two problems with your code as far as I can see (without actually running it).

First is that you have to use the name of the table,
not the table itself in the dynamic sql statement you are constructing.

So it should be this:

DECLARE @sql NVARCHAR(200) = 'BULK INSERT  @Temp1  FROM  ''' 
   + @FilePath + ''' WITH ( formatfile =\\servername\rawdata\format\fileformat.FMT)'

Second, as far as I know, you cannot do bulk insert into a table variable (which is what you have).
So even if you changed the code as I showed above, it is unlikely to work.
You can use base tables or views, but not table variables as the destination for a bulk insert.

[snipped]

infodemers
Posting Yak Master

183 Posts

Posted - 2012-07-05 : 12:10:54

Thanks to both of you for your explanations.

So, I tried the following and now I get the error message:
Incorrect syntax near '\'.

--drop table #temp1
DECLARE @FilePath  varchar(100)	
SET  @FilePath = '\\servername\rawdata\textfile.txt'

Create TABLE #temp1
(
[FileName] nvarchar(50),
heure  int,
success int,
reject int,
Datein char(8)
)

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT  #temp1 FROM  ''' + @FilePath + ''' WITH ( formatfile =\\BLMCIK\CMAMS\format\fileformat.FMT)'

EXEC(@sql)

Select * from #temp1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 12:16:50

You need a few more single quotes - see in red below:

''' WITH ( formatfile =''\\BLMCIK\CMAMS\format\fileformat.FMT)'''

infodemers
Posting Yak Master

183 Posts

Posted - 2012-07-05 : 13:30:24

Hi sunitabeck,
You were right except for the location of closing single quotes...
''' WITH ( formatfile =''\\BLMCIK\CMAMS\format\fileformat.FMT'')'

Thanks a lot!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 13:39:55

Of course! Sorry about the mishap, but glad you figured it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources