CODEWITHSUNDEEP
c#sql-servercsv
DonboscoJ
DonboscoJ

Reputation: 9

Importing CSV data in two SQL tables

I have a CSV file that contains 10 columns. I want to save 5 columns in one SQL table and next 5 columns in the second SQL table using C#

Upvotes: 0

Views: 68

Answers (1)

Jeroen Bouman
Jeroen Bouman

Reputation: 829

I think the easiest (and probably one of the fastest) ways for this is to do is using a temporary table.

Assuming the following CSV file:

Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10
Row1Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10
Row2Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10
...

Use something like this (in your C#) to import the file into de DB, where you can influence the file-path:

    CREATE TABLE #temp( column1 varchar,
                        column2 varchar,
                        column3 varchar,
                        column4 varchar,
                        column5 varchar,
                        column6 varchar,
                        column7 varchar,
                        column8 varchar,
                        column9 varchar,
                        column10 varchar);

    BULK INSERT #temp1
       FROM 'C:\Test.csv'
       WITH (
             FIELDTERMINATOR =',',
             ROWTERMINATOR = '\ n',
             FIRSTROW = 2
          )

Now you can use INSERT INTO SELECT to select columns into you other tables:

    INSERT INTO table1
    (Id, Name, Title, Desription, Date)
    SELECT column1, column3, column5, column7, column10
    FROM #temp1;

    INSERT INTO table2
    (SupplierName, Country, Contact, Addres, City)
    SELECT column2, column4, column6, column8, column9
    FROM #temp1;

If you like you can DROP or EMPTY the #temp1 table now.

Upvotes: 1

Related Questions