insert question - SQL Server Forums

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

SQL Server Development (2000)

insert question

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-08-25 : 16:33:08

i have a table customers1 which a field called customer_id for each customer name.

I have created another table called customers2 where all the customer names in the first customers are present along with some others.

What i want is to insert the customer_id number from the first table customers1 into the second table customers2 against those customers that
present in the second table as well(the same customer_id)

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 16:41:15

Using EXISTS:
INSERT INTO customer2 (...)
SELECT ...
FROM customer1 c1
WHERE EXISTS (SELECT * FROM customer2 c2 WHERE c1.customer_id = c2.customer_id)

You could also do this with an INNER JOIN:
INSERT INTO customer2 (...)
SELECT c1...
FROM customer1 c1
INNER JOIN customer2 c2 ON c1.customer_id = c2.customer_id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-08-25 : 16:51:01

quote:
Originally posted by tkizer

Using EXISTS:
INSERT INTO customer2 (...)
SELECT ...
FROM customer1 c1
WHERE EXISTS (SELECT * FROM customer2 c2 WHERE c1.customer_id = c2.customer_id)

You could also do this with an INNER JOIN:
INSERT INTO customer2 (...)
SELECT c1...
FROM customer1 c1
INNER JOIN customer2 c2 ON c1.customer_id = c2.customer_id

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

it does not work that way because the customer_id field in customer2 table is null so how can i inner join onthat filed and that is the field i want to populate in customer2 table. So please guide if i missed somethin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 16:52:01

I'm so confused. Do rows exist in customer2 already? If so, you want an update instead of an insert?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-25 : 16:56:11

So you want all customer_id from table1 that doesn't exist in table2?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 16:58:08

This might be what you want:

UPDATE c2
SET customer_id = c1.customer_id
FROM customer2 c2
INNER JOIN customer1 c1 ON c2.customer_name = c1.customer_name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-08-25 : 17:39:38

quote:
Originally posted by tkizer

This might be what you want:

UPDATE c2
SET customer_id = c1.customer_id
FROM customer2 c2
INNER JOIN customer1 c1 ON c2.customer_name = c1.customer_name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks this worked.sorry if i confused you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 17:42:56

For future posts, make sure you specify you want to update data if the rows exist already. An insert creates a new row. An update modifies an existing row. I saw the word "insert" used several times in your post, hence my first reply.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources