Unanswered: Importing a csv file directly into MS SQL
I have a csv file which has been exported from one of our systems. I need to be able to write a script that can import this csv directly into two SQL tables. The first column of the csv file determines which table the data should go in.
I need to be able to import/query the data directly from the CSV file into these two tables.
Are you sure that is exactly what you need? Because that would be complex. Easier would be to have a staging table. BULK INSERT in to that, do any cleaning\ checks\ transformations you require, then just run two insert queries for your two destination tables.
With a staging table you would not enforce datatypes - just use NVARCHAR columns. The idea of this is that you don't want your entire export to fail if there is one rogue value. Make the import as simple as possible (a dump into an unconstrained table). You can then do any cleaning or checks required and then move all rows that are clean in the final destination tables.