tsql query to create new table from a combination of two tables

Question

I have a table as follows: table 1

temp_id    node_name  variable_1 variable_2 variable_3
1          ab         a          b           y
2          sdd        a          a           a
3          u          a          s           s

and another table as follows: table 2

temp_id    node_name  variable_1 variable_2 variable_3
1          ab         as        sb           y
2          sdd        a          a           a
3          u          a          s           s

I want to fetch all the records from table 1 where variable_1, variable_2 and variable_3 of table 1 doesnot match with table 2.

How can I do that in TSQL?

I'm assuming temp_id and node_name have to match?

Daryl Teo
– Daryl Teo

2011-09-22 23:53:28 +00:00
Commented Sep 22, 2011 at 23:53 — Daryl Teo
– Daryl Teo, Commented Sep 22, 2011 at 23:53
Create a new table, or fetch records?

p.campbell
– p.campbell

2011-09-22 23:55:31 +00:00
Commented Sep 22, 2011 at 23:55 — p.campbell
– p.campbell, Commented Sep 22, 2011 at 23:55
yes ... the join will be on temp_id and node_id.

CPDS
– CPDS

2011-09-22 23:56:09 +00:00
Commented Sep 22, 2011 at 23:56 — CPDS
– CPDS, Commented Sep 22, 2011 at 23:56
i want to create a new table.

CPDS
– CPDS

2011-09-22 23:56:29 +00:00
Commented Sep 22, 2011 at 23:56 — CPDS
– CPDS, Commented Sep 22, 2011 at 23:56

Marco · Accepted Answer · 2011-09-22 23:57:13Z

3

Try this:

INSERT INTO new_table
SELECT t1.* FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.temp_id = t2.temp_id AND 
   t1.node_name = t2.node_name
WHERE t1.variable_1 <> t2.variable_1 AND
      t1.variable_2 <> t2.variable_2 AND
      t1.variable_3 <> t2.variable_3

answered Sep 22, 2011 at 23:57

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Branko Dimitrijevic · Accepted Answer · 2011-09-23 00:05:32Z

1

SELECT * FROM [table 1] t1
WHERE NOT EXISTS (
    SELECT * FROM [table 2] t2
    WHERE
        t1.variable_1 = t2.variable_1
        AND t1.variable_2 = t2.variable_2
        AND t1.variable_3 = t2.variable_3
)

---EDIT---

The above will "fetch all the records from table 1 where variable_1, variable_2 and variable_3 of table 1 doesnot match with table 2", as you asked.

However, it seems that you want to match the specific rows from table 2 not just any rows (BTW, you should have specified that in your question). If that is the case, Marco's answer looks good.

edited Sep 23, 2011 at 0:05

answered Sep 22, 2011 at 23:58

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

Collectives™ on Stack Overflow

tsql query to create new table from a combination of two tables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related