compare two tables and update the third one

Question

Suppose I have a table in SQL "abc" and with in it there is a column "number" and this column contains (1,2,3,4) etc.

In my second table "xyz" I have a column "number" and this column contains (1,2,3,4,5,6,7,8,9).

Now I want to compare these two and insert equal data within third table. So how can I do this?

code :

string str = "SELECT     Invoice_Details.PGI_ID, PARTY_BOOKING_DETAILS.PGI_ID AS abc ";
str += "FROM         PARTY_BOOKING_MAIN INNER JOIN ";
str += "    PARTY_BOOKING_DETAILS ON PARTY_BOOKING_MAIN.PBM_ID = PARTY_BOOKING_DETAILS.PBM_ID CROSS JOIN ";
str += "          Invoice_Details where PARTY_BOOKING_MAIN.PM_ID = 1 ";

SqlConnection con = new SqlConnection("data source = .; database = ePartyDatabase01; integrated security = true");
con.Open();
SqlCommand cmd1 = new SqlCommand("update Invoice_Details set [status] = @a", con);
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    if (dr["PGI_ID"].ToString() == dr["abc"].ToString())
    {

        cmd1.Parameters.AddWithValue("@a", 1);

    }
}

dr.Close();
cmd1.ExecuteNonQuery();

con.Close();

check the thread http://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables — Madhabendra, Feb 27 '13 at 09:53
Also check: http://stackoverflow.com/questions/5990586/mulitply-2-values-from-2-different-tables — kyndigs, Feb 27 '13 at 09:54

score 0 · Answer 1 · answered Feb 27 '13 at 09:55

0

You probably want to use some kind of this query:

insert into table3 (number)
select 
    t1.number
from abc t1
inner join xyz t2
    on t1.number = t2.number

answered Feb 27 '13 at 09:55

Sergio

6,900
5
31
55

score 0 · Answer 2 · answered Feb 27 '13 at 09:56

0

I haven't tested this yet, but you might not even need C#, try something like this:

SELECT xyz.number
INTO third_table
FROM (
   SELECT t1.number
   FROM first_table t1 JOIN second_table t2 ON t1.number = t2.number
) AS xyz

answered Feb 27 '13 at 09:56

gerrytan

40,313
9
84
99

score 0 · Answer 3 · answered Feb 27 '13 at 09:56

0

Maybe something like this:

INSERT INTO table3(number)
SELECT
    number
FROM
    xyz
WHERE NOT EXISTS
    (
        SELECT
            NULL
        FROM
            abc
        WHERE
            abc.number=xyz.number
    )

answered Feb 27 '13 at 09:56

Arion

31,011
10
70
88

compare two tables and update the third one

3 Answers3