Import Excel .xslx file into sql server : difficulty in updating table

Question

I am importing excel file into sql server datatbase. The code works fine but the way I am doing currently is deleting (clear the table) the table data.

         string ssqltable = "tStudent";
          string myexceldataquery = "select id,student,rollno,course from [sheet1$]";
        try
        {
            string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
            string ssqlconnectionstring = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=Test;CONNECTION RESET=FALSE";

            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(@"MERGE tStudent AS target
                              USING (select ID, STUDENT , ROLLNO from @source)  as source
                                ON (source.ID = target.ID)
                              WHEN MATCHED THEN
                              UPDATE SET Student = source.Student,
                                         ROLLNO = source.ROLLNO
                              WHEN NOT MATCHED THEN
                              INSERT (ID, STUDENT , ROLLNO)
                              VALUES (source.id, source.Student, source.RollNo);", sqlconn);
          ******************************************
            SqlParameter param = new SqlParameter();
            sqlcmd.Parameters.AddWithValue("@source", dr);
            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.tStudent";
          ******************************************
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();

            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);

            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqltable;

            bulkcopy.WriteToServer(dr);
            while (dr.Read())
            {
                //bulkcopy.WriteToServer(dr);
            }
            oledbconn.Close();
            Console.WriteLine(".xlsx file imported succssessfully into database.", bulkcopy.NotifyAfter);
        }

See * section. I have assigned my OleDb DataRreader dr in Sqlparameters, but I am declaring it later in code. Please guide me with how to structure my code.

Example would be appreciated.

you want [INSERT](http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx) not UPDATE. in SQL, UPDATE changes existing values. — RadioSpace, Apr 01 '14 at 23:16
@RadioSpace I want to update in Sql. For a moment I was deleteing data and inserting it. But now I want to update those data. I feel that update query and parameters I passed in it are not a correct way to do. Thanks. — user2525244, Apr 01 '14 at 23:27
@ConradFrix I can use anything , just I am a beginner and I want some code that is easier. I am not familiar with merge and Table-Valued parameter. Thanks. — user2525244, Apr 01 '14 at 23:28
@user2525244 - so you have fixed data, okay. are you getting an error or is something not working correctly? — RadioSpace, Apr 01 '14 at 23:31
@RadioSpace Import works fine if I delete all data from sql table and then perform import. But I am not able to do it if I want to update table rather deleting. So I guess I don't know how to write update parametized query for Update. So please help with parametized query of update. Thanks. — user2525244, Apr 01 '14 at 23:42
[update](http://technet.microsoft.com/en-us/library/ms177523.aspx) or maybe [check this question](http://stackoverflow.com/questions/13452998/parameterized-update-query-for-sql-server-in-asp-net-with-vb) your SQL seems to be fine. but I always use typed datasets so I could be wrong. but try getting rid of `.test` — RadioSpace, Apr 01 '14 at 23:46

Conrad Frix · Accepted Answer · 2015-09-14T14:31:07.177

1

Given that your excel file is the same structure as your table and you want to update rather than just insert the easiest way is to use Merge and a Table-Valued Paramter

SqlCommand cmd = new SqlCommand(@"MERGE tStudent AS target
                                  USING (select ID, STUDENT , ROLLNO from @source)  as source
                                    ON (source.ID = target.ID)
                                  WHEN MATCHED THEN
                                  UPDATE SET Student = source.Student,
                                             ROLLNO = source.ROLLNO
                                  WHEN NOT MATCHED THEN
                                  INSERT (ID, STUDENT , ROLLNO)
                                  VALUES (source.id, source.Student, source.RollNo);"
                                   , sqlconn);

SqlParameter param cmd.Parameters.AddWithValue("@source", dr);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.tStudent";

Your other options involve looping, using staging tables, passing the data as xml data or string data, or using an ETL tool like SSIS.

edited Sep 14 '15 at 14:31

answered Apr 02 '14 at 20:18

Conrad Frix

51,984
12
96
155

Thanks .. you are a hope.. I get this error saying "A MERGE statement must be terminated by a semi-colon (;)." I tried to find solution but cannot solve it. – user2525244 Apr 02 '14 at 20:44
1

I forgot the ";" at the end of the sql string. I updated the answer – Conrad Frix Apr 02 '14 at 21:02
Yes my excel and sql data have same structure. And how to declare table variable @source ? As I am taking excel file data. – user2525244 Apr 02 '14 at 21:26
1

@user2525244. You don't declare @source. You reference it by name in the sql statement and then set it's value with `Parameters.AddWithValue("@source", dr);`. The variable `dr` is the datareader you set up with `OleDbDataReader dr = oledbcmd.ExecuteReader();` – Conrad Frix Apr 02 '14 at 21:31
the issue i'm on is , I initialize my dr after sqlCon Close(); And I have to declare sql parameters before sqlConn Open. So I am not able to declare dr. Can you guide me with where to place code as my condition? – user2525244 Apr 02 '14 at 22:09
Plz see updated question. I have mentioned my problem in detail. Thanks. – user2525244 Apr 02 '14 at 22:29
1

Sorry I wasn't clear about this. You need to get rid of the SQL bulk copy part. You also need to execute your reader before the merge. – Conrad Frix Apr 02 '14 at 23:31
Totaly fine Conrad. I did that. Get this error "invalid attempt to call getschematable when reader is closed" – user2525244 Apr 02 '14 at 23:37
1

Seems like your reader is closed. You might have more success if you load the excel file into a datatable and pass that instead of a datareader – Conrad Frix Apr 02 '14 at 23:44
Hi.. I fixed that.. ANother error: "column parameter or variable @source cannot find data type dbo.tStudent" – user2525244 Apr 02 '14 at 23:49
plz see this.. http://stackoverflow.com/questions/22824854/coulumn-parameter-or-variable-source-cannot-find-datatype-dbo-tstudent – user2525244 Apr 03 '14 at 00:42
1

`dbo.tStudent` was a sample name you need to use the actual name – Conrad Frix Apr 03 '14 at 01:15
DOne.. Thanks alot Conrad. Appreciate. – user2525244 Apr 03 '14 at 18:59

Import Excel .xslx file into sql server : difficulty in updating table

1 Answers1

Linked