on 2011 Apr 19 3:12 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.