[dba-SQLServer] Bulk Insert?

Darryl Collins darryl at whittleconsulting.com.au
Thu Jan 9 22:04:50 CST 2014


Ok... I suspect I can see the issue with this example.  I have " SeqID_ProcClassManual" as a field that is not included in the original text file (it is populated later).  That might be why this is failing.  That said, not having much luck with *any* of them at this stage (I have about 40 text files to load).  Clearly I will use a SPOC with variable when it comes to the real thing, but right now, I just want ONE of them to work manually.  Meh!

Cheers
Darryl.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Friday, 10 January 2014 2:47 PM
To: Discussion concerning MS SQL Server
Subject: [dba-SQLServer] Bulk Insert?

Hi Folks,

I am trying to get BULK INSERT to work in SQL Server 2008 R2 (Express version).  Ideally Integrated Services would be a more elegant solution, but that is not available in the Express Version from what I understand.

That said, BULK INSERT should do the job.  I am running  test to check it out and I cannot get it to work at all.

T-SQL is:
'=====================================================================================
BULK INSERT dbo.ProbC_tblProcedureClasses FROM 'C:\Users\Darryl\Documents\ProberC_Transit\PrcMWA093_ProcedureClasses.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2, ROWTERMINATOR = '\n') '=====================================================================================


The text file is in the target location and contains:
'=====================================================================================
ProcedureClassName,ProcedureClassType
MaterialSource,Mining
Processing,Downstream
'=====================================================================================


Table in SQL Server looks like (PK is SeqID_ProcClass):
'=====================================================================================
Column Name			Data Type	Allow Nulls
ProcedureClassType		nvarchar(32)	Checked
ProcedureClassName		nvarchar(32)	Checked
SeqID_ProcClass		int		Unchecked
SeqID_ProcClassManual	int		Checked
'=====================================================================================


If I use the .TransferText method to a linked table in MS Access than the data loads fine into SQL Server.  However the BULK INSERT Code in SQL Server the query runs 'successfully', but returns
"(0 row(s) affected)" and (obviously based on that message) no data is loaded into the table.

Most curious.  Any idea what I am stuffing up here?

Cheers
Darryl.


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list