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.e.

JREV, 1, lhr, 2, 5
JSTA, 2, gla, 3, 6
JSTA, 5, lhr, 5, 7
JREV, 2, sla, 8, 8

Once i have created the 2 tables, (one called JREV and another called JSTA), I need to be able to import/query the data directly from the CSV file into these two tables.

Is there a way to do this please?

Thanks

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.

pootle flump ur codings are working excelent.

Hi, thank you for your quick reply.

Yes because the data on the CSV file is mixed with different data types, i.e. column 2 may have a datetime field and also a numeric/data field.

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.

pootle flump ur codings are working excelent.