Bulk Insert into a Table Variable

I am attempting to use a table variable as a destination for a bulk insert.

DECLARE @.TEXTFILE_1 TABLE

(CHAR_FIELD1 varchar(1) ,

CHAR_FIELD2 varchar(1) ,

CHAR_FIELD3 varchar(1) ,

CHAR_FIELD4 varchar(1) )

BULK INSERT @.TEXTFILE_1 FROM 'C:\TRASH.TXT'

WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n')

The input file looks like:

A | B | C | D
E | F | G | H

But the errors indicate that the input is not the issue (or at least not yet...).

The errors look like:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@.TEXTFILE_1'.

Msg 319, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I couldn't find any documentation that stated a table varable is an invalid destination for a bulk insert, but it is looking like that is the case. Any suggestions would be appreciated.

Thanks

Yeah, bulk insert is designed for actual tables, not table variables. Silly really, as you would think the system shouldn't really differentiate between the two... but unfortunately it does. Try creating a table in your database and seeing if it works with only that changed - if it does, then there's your problem for sure.

Rob|||You can't BULK INSERT into a table variable. If you are on SQL Server 2005 you can use INSERT...SELECT OPENROWSET(BULK) instead. See BOL for more help on how to use OPENROWSET(BULK).|||Unfortunately this client is on an older version of SQL and will not be upgrading until late this year at the earliest.

Is there an alternative to bulk insert for SQL 2000? We were trying to use a temporary table. That worked until the stored procedure was run under a login different than the user who ran the script that created the SP. That produced an error: "The current user is not the database or object owner of table 'name of temp table here'. Cannot perform SET operation." In understand this is a known issue. http://laneys.info/node/487

We didn't want to create a permanent scratch table since we would have to deal with the multi-user aspect and cleanup.

Because of the format of the data stored in the text file we cannot import directly to the destination table. We are trying to contain all of the activity in the base application which means we cannot write a parser in .NET. These are the hazards providing third party support.

Thanks so much for your help,
Richard|||

Does the workaround in the KB article referenced in the link above help? The KB article is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

Is it possible for you to give a detailed description of your setup? Any code will also help.

1. Who owns the SP?

2. How is the temporary table created? Is it inside the SP?

3. What privileges does the user calling the SP have in the user database? Is he part of some roles?

4. Does the user belong to any roles in the tempdb?