Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cwm
Starting Member
39 Posts |
 Posted - 2008-12-01 : 20:21:22
|
| Hi everyone,This is actually a 2 part question. This is what I need help with. I have a csv file that gets appended to on a daily basis, I will be populating that into sql with the bulk copy statement. Then I have a text file that is a schedule, with the same order numbers that are in the csv file, what I need to do is when i go to insert the text file, i need to have the right data go to the corresponding order number. here is a brief description...Csv File order numbers ( with other data as well )123451234612347123481234912340etc....Text File schedule123451234612347123481234912340etc....Now the i need to insert the other data from the text file into a table that is foreign keyed to other tables, based on the order number.I am not sure if i explained this properly, if not, please let m know so i can try re explaining. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-01 : 23:33:56
|
| do you mean you need to update csv with text data? |
 |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2008-12-02 : 09:15:08
|
| I figured i had explained it incorrectly..No what I have is 2 files, one is a csv file that i am going to import into sql using the bulk copy statement.Next I have a text file tha contains the same order numbers that are in the csv file.So I want to copy the order numbers and customer from the text file but have the ordernumbers from the csv reference the order numbers and customer from the text file in a sql table.ThanksChris |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-02 : 09:20:31
|
quote:
Originally posted by Cwm
I figured i had explained it incorrectly..No what I have is 2 files, one is a csv file that i am going to import into sql using the bulk copy statement.Next I have a text file tha contains the same order numbers that are in the csv file.So I want to copy the order numbers and customer from the text file but have the ordernumbers from the csv reference the order numbers and customer from the text file in a sql table.ThanksChris
Since you bulk copy csv to table first, isnt it enough to compare text file with table and do insert/update? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-02 : 09:40:29
|
| You can use openrowset to read from csv and text files. The select can be used to insert a new row or SELECT INTO a new table. The syntax is same for csv as for txt files.--To import each files into a new column of existing table and add date:INSERT INTO TableName (DateColumn, textColumn) SELECT GetDate(), * FROM OPENROWSET ( BULK 'C:\temp\t.TXT',SINGLE_CLOB) AS MYTABLEINSERT INTO TableName (DateColumn, textColumn) SELECT GetDate(), * FROM OPENROWSET ( BULK 'C:\temp\c.csv,SINGLE_CLOB) AS MYTABLE |
|
|
|
Cwm
Starting Member
39 Posts |
Posted - 2008-12-02 : 20:18:03
|
| Thanks I will try that tonight while i am at work.Chris |
|
|
|
|
|
|
|
|
i need help with inserting from csv and text files