The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

Or, in another form, How to achieve the same type of thing that Load Into Table ... (ColA,ColB,filler(),ColC) would do and still fire triggers.

Say you have a table:

CREATE TABLE AAmytable (
TID INTEGER NULL,
TName VARCHAR(40) NULL,
TAmount DECIMAL(10,4) NULL);

You have a CSV file import.csv:

TID,TTname,Tdesc,Tamount,TRep,
2,Morning,Second Trans,20,Matt,
3,Noon,Third Trans,30,Adam,
4,MidAft,Fourth Trans,40,Adam, 
5,Closing,Fifth Trans,50,Matt\

I have tried using the input, but cannot figure a way for it to skip a column.

I have tried using the load table, with filler(), and this correctly skips the column, but does not fire the triggers.

Moving the data into a temporary table and adjusting it from there is probably the way we will go, but I wanted to make sure I was not missing anything.

Any help would be appreciated.

asked 19 Apr '11, 14:12

Siger%20Matt's gravatar image

Siger Matt
3.2k496697
accept rate: 13%


Try using OPENSTRING.

For your example, use something like:

insert into AAmytable( TID, TName, TAmount )
select Tid, Tname, Tamount
  from openstring( FILE 'import.csv' )
  with ( Tid integer,
         Tname varchar(40),
         Tdesc varchar(100),
         Tamount decimal(10,4),
         TRep varchar(100) )
  option ( delimited by ',' )
  as imported

See the FROM clause in the docs for more info.

permanent link

answered 19 Apr '11, 15:24

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

edited 20 Apr '11, 15:09

Worked great. Thanks Mark. I did have to add an "As Imported" alias at the end, otherwise it gave me an error at end of line, but that does exactly what I need.

(20 Apr '11, 14:58) Siger Matt
Replies hidden

Glad to hear it worked for you, and reporting the error - I've corrected the statement.

(20 Apr '11, 15:11) Mark Culp

Mark's suggestion for using OPENSTRING is probably the way to go, but if you want an alternate suggestion, you could also consider using a proxy table. Assuming you are on Windows, the Microsoft Access Text Driver is a decent ODBC driver for accessing CSV files. You could then map a proxy table to your CSV file and use it like any other base table.

For some additional information/discussion have a look here

permanent link

answered 20 Apr '11, 07:40

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 20 Apr '11, 07:52

Thanks Karim, I will give that a try next time.

(20 Apr '11, 14:58) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14
×6
×5

question asked: 19 Apr '11, 14:12

question was seen: 1,121 times

last updated: 20 Apr '11, 15:11