Insert from a table to another where

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

General SQL Server Forums

New to SQL Server Programming

Insert from a table to another where

Author

Topic

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 07:24:54

Need som help.

I have a table TEMP111 with columns CustNo,A,B,CustPhone

For each row in that table I want to insert A+B into a specific column in the table CUST IF CustNo OR CustPhone is in that table.

Madly happy about any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 07:48:26

insert newtable (s)
select a+b
from temp111
inner join newtable on newtable.custno = temp111.custno or newtable.custphone = temp111.custphone

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 07:52:22

I want it into an existing table named CUST, can I still use it this way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 07:57:12

Of course! Substitute the newtable name with CUST and you get

insert cust (s)
select a+b 
from temp111
inner join cust on cust.custno = temp111.custno or cust.custphone = temp111.custphone

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 07:58:38

what do you mean with (s)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 08:04:10

(s) is the column name in table CUST where you want to insert the sum of A + b.

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 08:31:44

Seemed to work fine but now I got:

Cannot insert duplicate key row in object 'Cust' with unique index 'XPKCust'.
The statement has been terminated.

This is my code now:

insert CUSTOMER (Zkont)
select A+B
from temp222
inner join CUSTOMER on CUSTOMER.CompanyNo = temp222.OrgNr or CUSTOMER.Telephone = temp222.Tele

What am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 08:35:37

Use distinct keyword

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 08:38:48

where?

/sorry Im stupid...

quote:
Originally posted by Peso

Use distinct keyword

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 09:00:23

insert CUSTOMER (Zkont)
select distinct A+B
from temp222
inner join CUSTOMER on CUSTOMER.CompanyNo = temp222.OrgNr or CUSTOMER.Telephone = temp222.Tele

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 09:01:27

But the logic seems very wrong. You INSERT a record to a Customer table with no ID for customer?
Are you sure you don't want to UPDATE instead?

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 09:11:16

You are right. It is probably an update I want to do.

I want to update CUST.Zkont with A+B from TEMP222 where either telephonenr or companyno is the same.

Inno
Starting Member

33 Posts

Posted - 2006-11-02 : 09:46:35

I solved it, thank you for pointing me right when I tilted!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources