[dba-SQLServer] Bulk Insert?

Hi Darryl:

This is probably your best method. It is best never to import raw data straight into an active table.

Jim

----- Original Message -----
From: "Darryl Collins" <darryl at whittleconsulting.com.au>
To: "Discussion concerning MS SQL Server" <dba-sqlserver at databaseadvisors.com>
Sent: Thursday, January 9, 2014 8:15:11 PM
Subject: Re: [dba-SQLServer] Bulk Insert?

Ok... Making progress, but happy for someone to come up with a better solution if they can think of one.

Using a #Temp Table first, I can load the data into the real table successfully.  This looks a lot more painful to set up as I will need to build each temp table manually (at least I think I do), but I guess I only need to do it once.

'===============================================================================
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1;
GO

CREATE TABLE #test1
    (
    	RowNumber int,
	HeadingLevel int,
	Periods	int,
	ColumnA	nvarchar(255),
	ColumnB	nvarchar(255),
	ColumnC	nvarchar(255),
	ColumnD	nvarchar(255),
	ColumnE	nvarchar(255),
	ColumnF	nvarchar(255)
    )


BULK
INSERT #test1 
FROM 'C:\Users\Darryl\Documents\ProberC_Transit\PrcMWA093_SettingsBase.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

INSERT INTO dbo.ProbC_tblSettingsBase
SELECT * FROM #test1

DROP TABLE #test1

'===============================================================================

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 3:05 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Bulk Insert?

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


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


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