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?

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.

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?

bulk insert into a linked server fails

Hello,

I am trying to perform bulk insert operation on a linked server.

Here is the T-sql code for the same:

Declare @.dynamic_sql nvarchar(1000)
Declare @.file_name varchar(100)

set @.file_name = 'C:\mvam\calls\11182003.txt'

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE]...cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

execute sp_executesql @.dynamic_sql

On executing the above posted code I get the following error

Invalid object name 'MUMBAI\AMIT_DATABASE...cdr_repositroy'

Any suggestions would be helpful

ThanxAmit,
Unless you specifically configured your linked server to point to the appropriate database it defaults to the master DB.

To remove ambiguity you need to specify the full 4 part name
e.g

insert [DBDEVSERVER\DEVELOP].pubs.dbo.test
select 'a'

For your bulk insert specify the database name

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE].databasename.ownername.cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

let us know if you are still having problem|||I tried with the option you mentioned in your reply i.e. using the complete naming convention but of no use. I still get the same error.

Also, I checked if the Database is correct on linked server and its correct.

Anyways, thanx for the help.

Amit|||Does the link work with a select statement to the linked table

ie
Select * from servername.databasename.ownername.objectname.

BULK INSERT HELP

Hi

I am trying to do the following

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

DECLARE @.FilePath varchar(50)

SELECT @.FilePath = 'C:/Test/Test.log'

SELECT

@.doc = c from OpenRowset (BULK + @.Filepath + , SINGLE_BLOB) as T (c)

I get the following error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '@.Filepath'.

I am sure this is really basic.Can some one let me know what am i doing wrong

Thanks

You will have to use dynamic sql.

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

go

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

Thanks for the reply

Cna i do something like :

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

exec sp_xml_preparedocument @.hdoc OUTPUT,@.doc

Thanks

|||

Use the new XML datatype, If you use XML datatype, you need not to use the sp_xmlpreparedocuement(which uses more expensive resources & legecy com objects).

See XQuery on BOL to understand how to work with new XML data typed values. if you failed to use XML datatype / XML casting you only get xml content as binary..

Code Snippet

declare @.doc XML

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = convert(xml,c) from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql , N'@.doc XML OUTPUT', @.doc OUTPUT

Select @.doc

|||

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

DECLARE @.SQL nvarchar(200)

DECLARE @.Filepath varchar(50)

SET @.Filepath = 'C:\XML_Processing\Test.LOG'

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec

sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc

SELECT @.doc

print @.doc

print @.SQL

I was expecting @.doc to have the whole XML from Test,LOG but it is returning me NULL

Any ideas whats going on?

Thanks

Hemanshu

Build SQL Statement for execution with variables

How do I build a sql statement using variable that will be run within SP.
I have the following:
**Temp folder is designed above
declare @.CurrentDatabase nvarchar(128)
set @.CurrentDatabase = 'abc'
insert #BackupSet_Header
exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
I have written the restore sql, bur want the backup device to be dynamic
depending on variable passed to SP.
Thanks.might want to do something like this ps. I did not compile this so I hope it
compiles
create procedure my_sp @.dbname nvarchar(128) as
begin
declare @.sql nvarchar(1000)
select @.sql = 'restore headeronly from ' + @.dbname + '_backup'
insert into #BackupSet
exec master..sp_executesql @.sql
end
"Ed Gregory" <eg@.hotmail.com> wrote in message
news:O7P150o8EHA.936@.TK2MSFTNGP12.phx.gbl...
> How do I build a sql statement using variable that will be run within SP.
> I have the following:
> **Temp folder is designed above
> declare @.CurrentDatabase nvarchar(128)
> set @.CurrentDatabase = 'abc'
> insert #BackupSet_Header
> exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
> I have written the restore sql, bur want the backup device to be dynamic
> depending on variable passed to SP.
> Thanks.
>