Darryl Collins
darryl at whittleconsulting.com.au
Fri Jan 10 19:23:19 CST 2014
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