Bulk Insert A fFile

[UKmedia]

Morning,

I have some txt files in the format of:

ICR,X3,51,2,10:37:40,14/10/2008
1,2,137.00
3,1,8.00

Here is the problem:

I have my table laid out as

net_sales | gross_sales

I want to ignore the first row which I do with FIRSTROW = 2, and insert the other two rows into the columns but when I run this it inserts it as

net_sales | gross_sales
2 137.00
1 8.00

Where I want it to be:

net_sales | gross_sales
137.00 8.00

How can I tell it to insert 137.00 into net_sales and 8.00 into gross_sales

I hope I made this clear enough.



UKmedia productions

http://www.uk-media.com

 

[bborissov]

What you use to insert this file?
BULK INSERT or OPENROWSET()?
If you use BULK INSERT then first insert into temp table and then insert from that table into real one.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[UKmedia]

sorry I know how to do the insert it is changing the way it inserts the data is what I want to do as you can see the file is laid out like:

ICR,X3,51,2,10:37:40,14/10/2008
1,2,137.00
3,1,8.00

I want to insert the 137.00 into column gross_sales and 8.00 into net_sales

but if I run the insert it inserts it like

gross_sales | net_sales
1 2

UKmedia productions

http://www.uk-media.com

 

[bborissov]

I have no doubts you know how to do it. I ask what command you use.
As I said if you use BULK INSERT the first insert data into temp table and then insert it into real one:

CREATE TABLE #test (Fld1 int, Fld2 int, Fld3 numeric(10,2))
BULK INSERT #test
   FROM 'c:\Test\Test.txt'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR =' |\n',
         FIRSTROW = 2
      )



INSERT INTO RealOne (Gross_Sales, Net_Sales)
SELECT SUM(CASE WHEN Fld1 = 1 THEN Fld3 ELSE 0 END),
       SUM(CASE WHEN Fld1 = 3 THEN Fld3 ELSE 0 END)
FROM #Test

DROP TABLE #Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

But if you use OPENROWSET() command you could do this directly:

INSERT INTO RealOne (Gross_Sales, Net_Sales)
SELECT SUM(CASE WHEN Fld1 = 1 THEN Fld3 ELSE 0 END),
       SUM(CASE WHEN Fld1 = 3 THEN Fld3 ELSE 0 END)
FROM OPENROWSET(BULK N'c:\test,txt',.....) AS Tbl1 (Fld1 int, Fld2 int, Fld3 numeric(10,2))

(not tested)
Check the syntax for OPENROWSET() in BOL.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.