[dba-SQLServer] Bulk Insert?

Darryl Collins darryl at whittleconsulting.com.au
Wed Jan 15 16:37:54 CST 2014 

Well, it has been fun.  My "Job for Monday" turned out to be about 3 days' work but I have learnt a few new tricks on the way when converting the existing import and update process from using MS Access to SQL Server instead.

But happy to say it is now working well and is about twice as fast as MS Access - all good there.  I also appreciate you folks listening to me working through these ideas.  You might think you didn't do much, but I found your responses helpful - even if it is just confirmation that I am on the right track (I have been on the wrong track often enough to be Leary of these things!).

Using CAST was interesting and caused me some (self-inflicted) grief, as was some strange rules about JOINs and unbound field etc.  Still not sure why I need to use the "AS x" syntax but, but it works and is simple enough to set up and I guess it makes things neater.  Finding out about MERGE was interesting too.

MERGE dbo.vPopulateActivityProcedure_Sub1 AS t
USING dbo.vPopulateActivityProcedure_Sub2 AS s
ON t.PredActivityID = s.ActivityID
WHEN Matched
THEN UPDATE
SET
t.ProcedureClassType = s.[ProcedureClassType],
t.ProcedureName = s.[ProcedureName],
t.ProcedureClassName = s.[ProcedureClassName],
t.MaterialClassName = s.[MaterialClassName];

and IIF doesn't work - needed to change them all to CASE.  Again, this is ok once you know what you are doing.

Fun stuff.

Anyway, first part is done, now onto the more complicated second part - The output of the reporting into Excel.  :)  Thanks for your assistance.

Cheers
Darryl.



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

Thanks Jim.  A Job for Monday, but at least I know what to do and how to do it.

Cheers
Darryl


Sent from my Galaxy Note II. Please excuse any gibberish and/or brevity.



-------- Original message --------
From: Jim Lawrence <accessd at shaw.ca>
Date: 11/01/2014 07:04 (GMT+10:00)
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>
Subject: Re: [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

_______________________________________________
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

More information about the dba-SQLServer mailing list