CODEWITHSUNDEEP
c#sql-server-2008t-sql
Ryan Leach
Ryan Leach

Reputation: 4470

TSQL merge Incorrect syntax near ','

Getting SQLException with this "super useful" exception message (Incorrect syntax near ','.) , was wondering if anyone could see anything wrong with the syntax at a glace?

It is code based off of the example that can be found at http://www.jarloo.com/c-bulk-upsert-to-sql-server-tutorial/

  private void importToolStripMenuItem_Click(object sender, EventArgs e)
  {
        DataTable import = new DataTable();
        DialogResult result = this.importFileDialog.ShowDialog();
        if (DialogResult.OK == result)
        {
            Stream importStream = this.importFileDialog.OpenFile();
            import.ReadXml(importStream);
            importStream.Close();

            string tmpTable = "select top 0 * into #import from tblJob;";

            using (SqlConnection con = new SqlConnection(CONSTRING))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand(tmpTable, con);
                cmd.ExecuteNonQuery();

                SqlBulkCopyOptions options = SqlBulkCopyOptions.KeepIdentity;

                using (SqlBulkCopy bulk = new SqlBulkCopy(con))
                {
                    bulk.DestinationTableName = "#import";
                    bulk.WriteToServer(import);
                }

                //http://www.jarloo.com/c-bulk-upsert-to-sql-server-tutorial/
                //Now use the merge command to upsert from the temp table to the production table
                string mergeSql = "merge into tblJob as Target " +
                                  "using #import as Source " +
                                  "on " +
                                  "Target.[Location]=Source.[Location]," +
                                  "Target.[Schedule]=Source.[Schedule] " +
                                  "when matched then " +
                                  "update set Target.[Complete]=Source.[Complete]," +
                                  "Target.[Cost]=Source.[Cost]," +
                                  "Target.[Description]=Source.[Description]";
                                  //"when not matched then " +
                                  //"insert (Symbol,Price,Timestamp) values (Source.Symbol,Source.Price,Source.Timestamp)" +
                                  //";";

                cmd.CommandText = mergeSql;
                cmd.ExecuteNonQuery();

                //Clean up the temp table
                cmd.CommandText = "drop table #import";
                cmd.ExecuteNonQuery();
            }
            dgvJobs.DataSource = getData("select * from tblJob");

Upvotes: 0

Views: 749

Answers (1)

marc_s
marc_s

Reputation: 754953

If you want to have multiple Join criteria in your MERGE, you need to use the AND keyword (not a comma ,, as you're using now).

Instead of 

merge into tblJob as Target 
using #import as Source on Target.[Location]=Source.[Location], Target.[Schedule]=Source.[Schedule]

that you're using, use this instead:

MERGE INTO dbo.tblJob AS Target 
USING #import AS Source ON Target.[Location] = Source.[Location]
                           AND Target.[Schedule] = Source.[Schedule]

This is true for any join condition, also for INNER JOIN and LEFT OUTER JOIN or any other join type, too.

Upvotes: 5

Related Questions