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

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 Copy Errors

I am getting a "The process could not bulk copy into table '[dbo].[ ... "
error when attempting set up transactional replication between two SQL Server
2000 servers. The error occurs when copying the snapshot. The funny thing is
that the specific error states there is a primary key violation. However the
target table is empty and the source table does not contain any primary key
violations. Any ideas on what's causing this and how to address it?
Don't have a clue. I have never heard of this error before.
I'd try logging as per this kb article in hopes it can shed more light on
the matter.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:6073D5C2-709B-441C-A62D-EB2182A3ADCF@.microsoft.com...
>I am getting a "The process could not bulk copy into table '[dbo].[ ... "
> error when attempting set up transactional replication between two SQL
> Server
> 2000 servers. The error occurs when copying the snapshot. The funny thing
> is
> that the specific error states there is a primary key violation. However
> the
> target table is empty and the source table does not contain any primary
> key
> violations. Any ideas on what's causing this and how to address it?

Building a Where Clause

I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'

IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'

IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'

IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'

IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'

IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'

IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'

IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'

IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'

IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'

IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where

END

To answer my own post, this is how you build a dynamic query in a stored proc:

ALTERPROCEDURE [dbo].[cg_JobSearch]

-- Add the parameters for the stored procedure here

@.JobNoStart varchar(10)='',

@.JobNoEnd varchar(10)= @.JobNoStart,

@.OrderDateStart varchar(10)='',

@.OrderDateEnd varchar(10)= @.OrderDateStart,

@.DueDateStart varchar(10)='',

@.DueDateEnd varchar(10)= @.DueDateStart,

@.ProofDateStart varchar(10)='',

@.ProofDateEnd varchar(10)= @.ProofDateStart,

@.CloseDateStart varchar(10)='',

@.CloseDateEnd varchar(10)= @.CloseDateStart,

@.CogsDateStart varchar(10)='',

@.CogsDateEnd varchar(10)= @.CogsDateStart,

@.ProductName varchar(200)='',

@.CustomerNumber varchar(12)='',

@.SalesPerson varchar(3)='',

@.CSR varchar(15)='',

@.Closedint= 0,

@.Canceledint= 0,

@.FinalShipint= 0,

--@.Invoiced int = 0,

@.where varchar(8000)='',

@.sql varchar(8000)=''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd

IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd

IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd

IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd

IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd

IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd

IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName

IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber

IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson

IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR

IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'

SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'

SET @.sql= @.sql+ @.where

PRINT @.sql

EXEC(@.sql)

END

|||

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...

Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))

The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.

If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.

|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||

This is how I ended up doing mine.

CREATE PROCEDURE [dbo].[pe_getAppraisals]

-- Add the parameters for the stored procedure here

@.PTypenvarChar(500),

@.ClientnvarChar(500),

@.PageSizeINT

AS

DECLARE

@.l_SelectnvarChar(4000),

@.l_FromnvarChar(4000),

@.l_SetWherebit,

@.l_PTypenvarChar(500),

@.l_ClientnvarChar(500)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

--Initialize SetWhere to test if a parameter has Added the keyword WHERE

--Initialize the Where statement in case all parameters are null

SET @.l_SetWhere= 0

--Create WHERE portion of the SQL SELECT Statement

IF(@.PTypeISNOTNULL)AND(@.PType<>'')

BEGIN

SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType

SET @.l_SetWhere= 1

End

ELSESET @.PType=''

IF(@.ClientISNOTNULL)AND(@.Client<>'')

BEGIN

IF @.l_SetWhere= 0

BEGIN

SET @.l_Client=' WHERE o.ClientID='+ @.Client

SET @.l_SetWhere= 1

END

ELSESET @.l_Client=' AND o.ClientID='+ @.Client

END

ELSESET @.l_Client=''

--Build the SQL SELECT Statement

SET @.l_Select=

'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,

o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,

o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,

o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,

o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,

ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'

SET @.l_From=

'Orders AS o LEFT OUTER JOIN

OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN

OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN

OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN

OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN

PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN

PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN

Logins AS l ON o.AppraiserID = l.LoginID'

Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)