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 |
|
Rach2009
Starting Member
37 Posts |
 Posted - 2009-11-19 : 03:56:50
|
| Hi All,I have successfully done one BULK INSERT in SQL (using a fmt file) to import a csv file of datam (call this csv1) into a particular SQL database table called ORDERS. However, I now want to import more data into this ORDERS table from a different csv file (csv2). All the fields/columns in csv2 file are different from csv1 imported, except that they link on ORDERID. Can I do another BULK INSERT in this situation, or do I need to write a UPDATE query?ThanksRachel |
|
|
MartinH
Starting Member
5 Posts |
Posted - 2009-11-20 : 05:38:55
|
| Hi rachelDo you mean that the fields in csv2 are different or that the data is different? The number of cols and the data types on csv2 need to be the same, and in the same order as the table (and csv1)MartinHhttp://uk.linkedin.com/in/martinhandsley |
 |
|
|
Rach2009
Starting Member
37 Posts |
Posted - 2009-11-20 : 05:50:09
|
| Hi Martin,I am trying to do a further Bulk Insert into the same SQL database table but the 2 csv files contain different columns (except for one column, ORDER_ID) So, if the SQL table has 20 columns, csv1 consists of columns 1-10, and csv2 consists of columns 11-20 -with the aim that once I import both all 20 columns in the SQL table would be filled. Is this possible to do? As I said, csv1 went in fine, but I can't work out how to insert csv2 into this table using BULK INSERT (could I use some kind of WHERE clause to link the 2 ORDER_ID's?) I have created 2 distinct fmt files for csv1 and csv2, to accomodate the different colummns and data types. Is this method at all possible? Or do I just need to run an update query (with a where clause to link the two ORDER_ID's) to insert csv2, and complete the SQL database table. Many Thanks for your help!Rachel |
|
|
|
MartinH
Starting Member
5 Posts |
Posted - 2009-11-20 : 06:16:48
|
| Hi Rachel,I'm pretty sure this isn't possible. I would go with your suggestion of loading into two seperate tables and merging the data using your query, joined on orderid.MartinMartinHhttp://uk.linkedin.com/in/martinhandsley |
|
|
|
MartinH
Starting Member
5 Posts |
Posted - 2009-11-20 : 06:48:09
|
| Rachel, i forgot to explain...You could load the data into the same table, by added whatever your col delimiter your using to the start of each row of the second datafile eg if you use comma as a delimiter,,,,,,,,data1,data2,data2 etc,,,,,,,,data1,data2,data2 etc,,,,,,,,data1,data2,data2 etc,,,,,,,,data1,data2,data2 etcand by allowing nulls on each col.however, after loading the data your table would look like this....data1 data2 data3 data4 data5 data6 data7 data8 null null null null null null null nulldata1 data2 data3 data4 data5 data6 data7 data8 null null null null null null null nulldata1 data2 data3 data4 data5 data6 data7 data8 null null null null null null null nulldata1 data2 data3 data4 data5 data6 data7 data8 null null null null null null null nullnull null null null null null null null data1 data2 data3 data4 data5 data6 data7 data8null null null null null null null null data1 data2 data3 data4 data5 data6 data7 data8null null null null null null null null data1 data2 data3 data4 data5 data6 data7 data8null null null null null null null null data1 data2 data3 data4 data5 data6 data7 data8null null null null null null null null data1 data2 data3 data4 data5 data6 data7 data8etcHope this helps to explain!MartinHhttp://uk.linkedin.com/in/martinhandsley |
|
|
|
Rach2009
Starting Member
37 Posts |
Posted - 2009-11-20 : 08:28:32
|
| Thanks Martin, I'll try inserting into 2 different tables then and then combining. |
|
|
|
|
|
|
|
|
Bulk Insert Question