Insert into table if record doesn’t already exist? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert into table if record doesn’t already exist?

Author

Topic

Round
Starting Member

6 Posts

Posted - 2007-01-24 : 08:06:17

Hello all,
I have two customer tables. I want to insert all the customers and their details from table 1 into table 2, but only if they are not already in table 2. Is this possible?

Regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-24 : 08:08:37

Yes it is possible.

Insert into Tbl2(col1, col2, ....)
Select col1, col2, ....
From Tbl1 t1
Left Join Tbl2 t2
on t1.pk = t2.pk
where t2.pk is NULL

Note: pk = primary key column

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Round
Starting Member

6 Posts

Posted - 2007-01-24 : 08:57:05

Table 1 does not have the same primary key as Table 2.
Table 1's columns:- customer_id(pk), forename, surname.
Table 2's columns:- ID(pk), customer_id, forename, surname.
Many Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-01-24 : 10:25:21

What do u want in each Column of Table 2 to be having once the query is run
Mainly refered to : ID(pk) & customer_id

V can see that Table1.forename --> Table2.forename & Table1.surname --> Table2.surname

Srinika

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-24 : 10:30:15

[code]Insert into Tbl2(customer_id, forename, surname)
Select customer_id, forename, surname
From Tbl1 t1
Left Join Tbl2 t2
on t1.customer_id = t2.customer_id
where t2.customer_id is NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources