BULK INSERT into a table variable

Bulk Insert to an existing table works fine, but substituting a table variable causes parsing error:

Msg 137, Must declare the scalar variable "@.tblInput". or

Msg 207, Invalid column name '@.tblInput'.

To repro:

CREATE PROC X AS

BEGIN

DECLARE @.strSQL char(99)

DECLARE @.BulkFile varchar(60)

SET @.BulkFile = 'c:\LookingGlass\BulkCopy1.tmp'

DECLARE @.tblInput TABLE (

[Word] [varchar] (50),

[UseCount] [int] )

--works ok with temp table:

SET @.strSQL = 'BULK INSERT temp_tbl From ''' + @.BulkFile + ''''

--but not with table variable

SET @.strSQL = 'BULK INSERT ''' + @.tblInput + ''' From ''' + @.BulkFile + ''''

EXEC(@.strSQL)

END

These don't work either:

SET @.strSQL = 'BULK INSERT @.tblInput From ''' + @.BulkFile + ''''

SET @.strSQL = 'BULK INSERT ' + @.tblInput + ' From ''' + @.BulkFile + ''''

version: Microsoft SQL Server Management Studio Express 9.00.2047.00

You can't do that what you could do is use OPENROWSET with a txt or csv file

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Ignore!!!

|||

The op has tried that already, look at his last line of code

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks, Denis, for the very quick reply. I had searched extensively, and the documentation neglects to mention that Table Variables can't be used with Bulk Insert.

Ironically, the name of the Table Variable is fixed, but because it's a "variable", it can't be used to import.

Importing with a variable filename is a requirement.

But according to BOL, OPENROWSET, down in the Remarks,

"OPENROWSET does not accept variables for its arguments. "

Doesn't that mean hardcoded input filenames? ( OPENROWSET can use a Linked Server, but that's overkill for a text file.)

I wanted the performance benefits that a Table Variable provides, but it appears I'll have to make do with a temp table.

|||

Denis, stupidity on my part ... Read the post and thought he was trying to pass a table name in the variable. He is trying to pass a table variable... You're post is correct, you can't use table variables as part of a dynamic SQL string (would be nice if you could cuz it would same me from using #temp tables...

|||

you will have to use dynamic SQL

example

declare @.v varchar(500)
declare @.s varchar(500)
select @.v ='select top 6 * from ' + 'TestTextFileImport.txt'

select @.s ='
select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;'', ''' +@.v +' '')'

print @.s
exec (@.s)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Denis, that works great, just open the text file as a rowset! No need for a table at all.

Unfortunately, this raises a security issue. Running the sproc produces this error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Here are excerpts from searching BOL (underlines are mine):

If a nonzero value is set, SQL Server does not allow for ad hoc access

through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider.

When this option is not set, SQL Server also does not allow for ad hoc access.

This option controls the ability of non-administrators to run ad hoc queries. Administrators are not affected by this option.

By default, SQL Server does not allow ad hoc distributed queries

using OPENROWSET and OPENDATASOURCE against providers other than the SQL Native Client OLE DB Provider.

When this option is set to 0, SQL Server allows ad hoc access against other providers.

When this option is not set or is set to 1, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions

to connect to remote data sources that use OLE DB.

OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently.

For any data sources that will be accessed more than several times, define a linked server.

'Ad Hoc Distributed Queries is an Advanced option, which should be changed only by an experienced database administrator or a certified SQL Server technician,

and which require setting show advanced options to 1.

And here is how to do it:

sp_configure 'show advanced options',1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries',1;

GO

RECONFIGURE;

GO

or with version 2.0 of the Microsoft .NET Framework, set the OleDbProviderSettings.DisallowAdHocAccess Property

All that just to read local text files? The server can't differentiate between local and remote files?

Why is a linked server recommended for files that will be read frequently? (thousands per hour, hopefully)

And I thought this was a pretty basic task. Thanks for answering my question.