How to make a 'Merge' Stored Procedure with this... RSS

• • Reply
  

  alejandro.la...

  Member

  2 Points

  8 Posts

  How to make a 'Merge' Stored Procedure with this...

  Apr 16, 2012 03:57 PM|LINK

  Hello! 

  I'm retrieving data from an Excel file with OleDb, then I passed that to a DataReader in order to populate a SQL Server Express table, my INSERT statement works very well. All data that I want in there, but I need to control duplicate rows when the user upload an Excel file again, it just must UPDATE 2 date columns if necessary, I've been reading about Stored Procedures in the follow link: Merge Statement in SQL Server 2008. They mention this code:

  CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
  CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);
  
  --Synchronize source data with target
  MERGE INTO dbo.tbl_Target AS t
      USING dbo.tbl_Source AS s
          ON t.id = s.id
      WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
          --Row exists and data is different
          UPDATE SET t.name = s.name, t.qty = s.qty
      WHEN NOT MATCHED THEN
          --Row exists in source but not in target
          INSERT VALUES (s.id, s.name, s.qty)
      WHEN SOURCE NOT MATCHED THEN
          --Row exists in target but not in source
          DELETE OUTPUT$action, inserted.id, deleted.id

  But I just don't know if this is possible to implement with my code, I don't know what to use as a target and source table. This is my code:

                      OleDbCommand oleDbCommand = new OleDbCommand
                          ("SELECT [Create],[Lote],[Qtd],[F11],[Término Real],[Número da Ordem] From [R 1120$]", oleDbConnection);
  
                      oleDbConnection.Open();
                      OleDbDataReader dataReader = oleDbCommand.ExecuteReader();
                      string create = "";
                      string lote = "";
                      string qtd = "";
                      string fechaPlan = "";
                      string terminoReal = "";
                      string numeroOrden = "";
  
                      CultureInfo cultureInfo = new CultureInfo("pt-BR");
  
                      while (dataReader.Read())
                      {
                          DateTime dateTime_FechaPlan = new DateTime();
                          DateTime dateTime_TerminoReal = new DateTime();
  
                          create = EsValido(dataReader, 0);
                          lote = EsValido(dataReader, 1);
                          qtd = EsValido(dataReader, 2);
                          fechaPlan = EsValido(dataReader, 3);
                          terminoReal = EsValido(dataReader, 4);
                          numeroOrden = EsValido(dataReader, 5);
  
                          SqlConnection sqlConnection = new SqlConnection(sqlConn);
                          SqlCommand sqlCommand = new SqlCommand();
  
                          sqlCommand.Connection = sqlConnection;
                          sqlCommand.CommandText = "INSERT Into temp([Create], Lote, Qtd, FechaPlaneada, TerminoReal, NumeroOrden)" +
                                                   "Values(@create, @lote, @qtd, @dtFechaPlan, @dtTerminoReal, @numeroOrden)";
  
                          bool fechaPlaneada_Es2011 = true;
                          bool terminoReal_Es2011 = true;
  
                          if (create != "0")
                          {
                              sqlCommand.Parameters.Add("@create", SqlDbType.VarChar).Value = create;
  
                              if (fechaPlan == "")
                              {
                                  sqlCommand.Parameters.AddWithValue("@dtFechaPlan", DBNull.Value);
                              }
                              else
                              {
                                  dateTime_FechaPlan = DateTime.Parse(fechaPlan, cultureInfo);
  
                                  if (dateTime_FechaPlan.Year != 2011)
                                  {
                                      sqlCommand.Parameters.Add("@dtFechaPlan", SqlDbType.DateTime).Value = dateTime_FechaPlan;
                                      fechaPlaneada_Es2011 = false;
                                  }       
                              }
  
                              if (terminoReal == "")
                              {
                                  sqlCommand.Parameters.AddWithValue("@dtTerminoReal", DBNull.Value);
                              }
                              else
                              {
                                  dateTime_TerminoReal = DateTime.Parse(terminoReal, cultureInfo);
  
                                  if (dateTime_TerminoReal.Year != 2011)
                                  {
                                      sqlCommand.Parameters.Add("@dtTerminoReal", SqlDbType.DateTime).Value = dateTime_TerminoReal;
                                      terminoReal_Es2011 = false;
                                  }
                              }
  
                              if (terminoReal_Es2011 == false || fechaPlaneada_Es2011 == false)
                              {
                                  sqlCommand.Parameters.Add("@lote", SqlDbType.VarChar).Value = lote;
                                  sqlCommand.Parameters.Add("@qtd", SqlDbType.VarChar).Value = qtd;
                                  sqlCommand.Parameters.Add("@numeroOrden", SqlDbType.VarChar).Value = numeroOrden;
  
                                  sqlCommand.CommandType = CommandType.Text;
  
                                  sqlConnection.Open();
                                  sqlCommand.ExecuteNonQuery();
                                  sqlConnection.Close();
                              }
                          }
                      }
                      oleDbConnection.Close();

  Thanks in advance, and sorry about my English :P
  
  

  StoredProcedure datareader MERGE

  • Edited by alejandro.lagos on Apr 16, 2012 03:57 PM
• • Reply
  

  krishdsouza

  Member

  250 Points

  50 Posts

  Re: How to make a 'Merge' Stored Procedure with this...

  Apr 16, 2012 06:03 PM|LINK

  go through below post

  http://forums.asp.net/t/1452459.aspx/1

  http://asp.net.bigresource.com/Find-Duplicate-records-in-excel-while-uploading--v81ZCNvOV.html

  StoredProcedure datareader MERGE

• • Reply
  

  Chen Yu - MS...

  All-Star

  21569 Points

  2493 Posts

  Microsoft

  Re: How to make a 'Merge' Stored Procedure with this...

  Apr 18, 2012 10:48 AM|LINK

  Hi,

  If you want export data from excel file to SQL Server Express table, your execel table is source table, temp table in your SQL Server Express is target table. If you want to use merge clause in your code, use them in sqlCommand.CommandText instead of Insert clause. If you meet any trouble, please tell us as free.

  Thanks.

  StoredProcedure datareader MERGE

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by alejandro.lagos on Apr 18, 2012 06:21 PM
• • Reply
  

  alejandro.la...

  Member

  2 Points

  8 Posts

  Re: How to make a 'Merge' Stored Procedure with this...

  Apr 18, 2012 06:24 PM|LINK

  Thank you :-)

  It's my first time working with this kind of data, I didn't know that the "MERGE" sentence could be used in a SqlCommand.CommandText, actually my code is working, but anyway I'll try that way, I think it is faster, isn't it?

• • Reply
  

  Chen Yu - MS...

  All-Star

  21569 Points

  2493 Posts

  Microsoft

  Re: How to make a 'Merge' Stored Procedure with this...

  Apr 19, 2012 03:42 AM|LINK

  Hi,

  It's easier to use Merge than to use both an INSERT statement and an UPDATE statement.

  There are some examples in MSDN library,

  please check :http://technet.microsoft.com/en-us/library/bb510625(v=sql.100).aspx 

  You could follow this blog to edit your Merge query.

  Please see: http://klanguedoc.hubpages.com/hub/Transact-SQL-Upsert-Inserting-and-Updated-in-SQL-Server 

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Edited by Chen Yu - MSFT on Apr 19, 2012 04:11 AM