Can I Bulk Insert into a Table Variable?

[njvsummit]

Can I bulk insert into a table variable? When I run the following, I recieve a syntax error; however it does work if I create a temp table instead of the variable.

declare @OPLST table(
BATCH varchar(9),
SUBNO varchar(15),
PCNTLNO varchar(38),
CLAIMNO varchar(16),
CPLAN varchar(10),
SERVDATE datetime,
RECDATE datetime,
IN_FILE varchar(15))

bulk insert @OPLST
from '\\SERVER\oplst.txt'
with (FIELDTERMINATOR = ',', ROWTERMINATOR = '\r')

Thanks for your help,

Nick

 

[SQLSister]

I doubt you can. You can't even put the results of a stored procedure into a table variable. You will need to use a temp table instead. Clearly table variables were an afterthought that was not fully implemted for the things that people would normally expect to do with them.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[JamesLean]

Can't find anything confirming either way, but based on your findings with a temp table I would say you probably can't use a table variable for bulk inserts. There are a couple of other limitations with them so this may just be another one!

--James