How Do I Insert Only Recently Updated Row From Table1 To 2

Question

1.00/5 (1 vote)

See more:

i have two tables with same schema. i have to check table1 for any updates and insert the updates or the entire row into table2, only the most recently updated row.
so basically, table2 has the unique values from table1; for the duplicate entries in table1, there is only the most recent entry in table2.
i have tried to get the most recently updated id from table1:

SQL

SELECT distinct id
FROM table1 order by time_date desc limit 1;

now i need to use this id to get the other columns from table1 and insert the row with that id, into table2.
and i have to do this for all the entries in table1.

any help would be great.!
thank you.

Posted 2-Apr-15 1:38am

RevathySanthanam

Add a Solution

Comments

Richard Deeming 2-Apr-15 9:08am

Which DBMS? The limit clause suggests MySQL.

RevathySanthanam 2-Apr-15 9:32am

yes, it is MySQL.!

Praveen Kumar Upadhyay 2-Apr-15 9:46am

2 things I need to know, your are going to insert only new entry(which are not there in table2) of table1 to table2 or you want to insert the updated rows of table1 to table2, these updated rows may exists in table2.

recent updated row means, what is the time that

RevathySanthanam 2-Apr-15 9:48am

i have to update the row in table2 if it already exists, else insert new row. i check for recent record based on the date column, time_date.
both the tables have same schema.

4 solutions

Solution 1

It would be a better approach to have a flag in `table1` to mark whether the row has been updated to the `table2` or not. That's pretty easy to do and much more convenient than your current approach of finding the recent row (`recent` sounds imaginary to me).

However, once you got the id of the recent row from `table1`, you can insert the row to the `table2` like-

SQL

INSERT INTO table2
SELECT * FROM table1 WHERE id=@id

Hope, that helps :)

References:
SQL INSERT INTO SELECT Statement[^]

Posted 2-Apr-15 3:17am

Suvendu Shekhar Giri

v2

Comments

RevathySanthanam 2-Apr-15 9:42am

thank you Suvendu but I am supposed to insert only the recent records for each id into table2. so how can i make use of a flag? and i check for recent record based on the date column.
and yes, i was using the way i mentioned before about finding the recent record id and inserting it into table2. but i do not know how can i do it for each and every id in table1. i have to insert all the recent records for all ids in table1 into table2...!! any ideas???

Solution 2

The best way is to use the MERGE Statment.

SQL

DECLARE @int int 

--*  The line below will allow you to insert identy or primary id key 
SET IDENTITY_INSERT table1 ON

--* now the merge statement
--* t is the target table
--* s is the source table

MERGE table1 t
      USING table2 s 
      ON s.primkey = t.primekey
      WHEN MATCHED 
           THEN UPDATE
                SET t.primkey = s.primkey
                   ,t.col2name = s.col2name
                   ,t.col3name = s.col3name
                   ,t.col4name = s.col4name
       WHEN NOT MATCHED
           THEN INSERT
                ( t.primekey
                 ,t.col2name
                 ,t.col3name
                 ,t.col4name)
            VALUES
                ( s.primkey
                 ,s.col2name
                 ,s.col3name
                 ,s.col4name); 

--* turn Identiy insert off 
SET IDENTITY_INSERT table1 OFF

--*Now update the sequence of the primarykey id field.
SET @int = (SELECT MAX(t.primkey) as intseed from dbo.table1)

--* using the @int update table1 identity sequence.
dbcc checkident('table1', Reseed, @int)

Posted 8-Apr-15 3:01am

Ben Dailey

Comments

RevathySanthanam 8-Apr-15 9:15am

Thank you for the interesting solution, Ben.
but I don't want to merge based on just the Primary key because when there are many duplicates in table1, I need only the Most Recently Updated Row (based on the date column corresponding to the Primary Key) inserted or updated into Table2. So how can I achieve that here?

Solution 3

You don't have to merge on just the Primary key. You can merge on multiple statments. I just used the simplest in my example.

See the following site for more information on merge. It is very powerful.

https://msdn.microsoft.com/en-us/library/bb510625.aspx[^]

Posted 8-Apr-15 11:12am

Ben Dailey

Updated 8-Apr-15 11:14am

v2

Comments

RevathySanthanam 9-Apr-15 3:43am

great, it is a nice piece of information. and it works. Thank you. But i was wondering, is it possible to do the Insert dynamically, as in not plugging in all the column names manually in Insert Statement because I have a huge number of columns in my table. it really would be helpful..! googling for a solution wasn't of much help.! Also, any idea on how I can build the above code in C#.? I'm supposed to provide a C# solution to my problem.!!

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RevathySanthanam · Accepted Answer · 2015-04-10T03:49:00

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient; 

namespace DBsync2
{
      //table2 is the Master(with duplicates) Table and table1 is the one without duplicates 
 
    public partial class Form1 : Form
    {
        private MySqlConnection conn1 = null;
        private MySqlConnection conn2 = null;
        string cs1,cs2;

        class IdTsEntry
        {
            public Int64 fid { get; set; }
            public String fdate { get; set; }
        }

        void Init()
        {
            //initialize cs1, cs2 to suit the database connection details.

            try
            {
                string servername1 = server1.ToString();
                string uname1 = uid1.ToString();
                string pass1 = pwd1.ToString();
                string dbName1 = dbname1.ToString();

                string tbl1 = table1.ToString();
                string tbl2 = table2.ToString();

                

                conn1 = new MySqlConnection(cs1);
                conn1.Open();
                conn2 = new MySqlConnection(cs2);
                conn2.Open();

                tbl1 = "abc";
                tbl2 = "def";

                // Load target list's ids and timestamps
                string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
                MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
                List<IdTsEntry> A = new List<IdTsEntry>();
                using (MySqlDataReader reader = cmd1.ExecuteReader())
                {
                    //while (reader.Read())
                    //{ // I assume the fields are set to NOT NULL
                    //    A.Add(new IdTsEntry()
                    //    {
                    //        fid = reader.GetInt64(0),
                    //        fdate = reader.GetString(1)
                    //    });
                    //}

                    while (reader.Read())
                    { // I assume the fields are set to NOT NULL
                        A.Add(new IdTsEntry()
                        {
                            fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
                            fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
                        });
                    }
                }
                // Load source list's ids and timestamps
                string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2;
                MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

                List<IdTsEntry> B = new List<IdTsEntry>();
                using (MySqlDataReader reader = cmd2.ExecuteReader())
                {
                    while (reader.Read())
                    { // I assume the fields are set to NOT NULL
                        B.Add(new IdTsEntry()
                        {
                            fid = reader.GetInt64(0),
                            fdate = reader.GetString(1)
                        });
                    }

                }


              
                // Filter lists
                List<Int64> List1 = new List<Int64>();
                List<Int64> List2 = new List<Int64>();
                foreach (IdTsEntry b in B)
                {
                    var a = A.FirstOrDefault(e => e.fid.Equals(b.fid));
                    if (a == null) 
                        List2.Add(b.fid); // b.id not in A -&gt; new row
                    else if (!a.fdate.Equals(b.fdate))
                        List1.Add(b.fid); // b.id in A but other timestamp -&gt; altered row
                }


            
                            // Update altered rows
                //string ct1 = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
                //string ct2 = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = {0}";
                foreach (Int64 id in List1)
                {
                    // Read all entry values into parameters
                   // cmd1.CommandText = String.Format(ct1, id);
                    cmd1.CommandText = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();

                   // MessageBox.Show(cmd1.CommandText);
                    cmd2.Parameters.Clear();
                    int i = 0;
                    using (MySqlDataReader reader = cmd1.ExecuteReader())
                    {
                        if (!reader.Read()) continue; 
                        for (int n = 0; n < reader.FieldCount; n++)
                        {
                            cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                                reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                        }

                    }
                    // Update row
                    //cmd2.CommandText = String.Format(ct2, id);
                    cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = " + id.ToString();
                    cmd2.ExecuteNonQuery();
                }
            
            
                                // Insert new rows
                //ct1 = "SELECT fid, variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
                //ct2 = "INSERT INTO " + tbl1 + " (seriennummer, variante,charge,fdate) " +
                //    "VALUES (@val1, @val2, @val3, @val4)";
                //    cmd2.CommandText = ct2;
                    cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
                        "VALUES (@val1, @val2, @val3, @val4)";
                    foreach (Int64 id in List2)
                    {
                        // Read all values into parameters
                        //cmd1.CommandText = String.Format(ct1, id);

                        cmd1.CommandText = "SELECT fid,variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();
                        cmd2.Parameters.Clear();
                        int i = 0;
                        using (MySqlDataReader reader = cmd1.ExecuteReader())
                        {
                            if (!reader.Read()) continue;
                            for (int n = 0; n < reader.FieldCount; n++)
                            {
                                cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                                    reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                            }
                        }
                        // Insert row
                        cmd2.ExecuteNonQuery();
                    }




            
            }




            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }

        }
        public Form1()
        {
            InitializeComponent();

            Init();


        }

    }

}

How Do I Insert Only Recently Updated Row From Table1 To 2

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

How Do I Insert Only Recently Updated Row From Table1 To 2

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us