Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL INSERT

Posted on 2011-10-31
9
Medium Priority
?
284 Views
Last Modified: 2012-05-12
Hello,
I’m trying to Insert into an SQL table (AddressContacts) From another Table (Abook),
attached sample

Insert Into Addresscontacts ("Name","Phone","Fax","Email","AddressID","IsDefault","NotificationType","NotifyOnNewOrder",
"NotifyOnDispatch","NotifyOnPickedUp","NotifyOnPOD","NotifyOnCancel")
Select “Name”,”Phone”,”Fax”,"Email","AddressID",1,4,0,0,0,0,0
From ABook
where Addresscontacts.Addressid = ABook.Addressid

Where am I going wrong,
Any help is appreciated.

Thanks,
Wass

ABook.xls
0
Comment
Question by:W.E.B
9 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 37057475
If you have the AddressContacts table referenced in the select , you need to have a join there connecting it to the abook table.
0
 

Author Comment

by:W.E.B
ID: 37057497
I did add a join,
Inner Join ABook on Addresscontacts.Addressid = ABook.Addressid

I get error.
Msg 1013, Level 16, State 1, Line 1
The objects "ABook" and "ABook" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Thanks
0
 
LVL 3

Accepted Solution

by:
lisa_mc earned 252 total points
ID: 37057749
you need to do your join this way
Insert Into Addresscontacts ("Name","Phone","Fax","Email","AddressID","IsDefault","NotificationType","NotifyOnNewOrder",
"NotifyOnDispatch","NotifyOnPickedUp","NotifyOnPOD","NotifyOnCancel")
Select “Name”,”Phone”,”Fax”,"Email","AddressID",1,4,0,0,0,0,0
From ABook 
inner join Addresscontacts on
ABook.Addressid = Addresscontacts.Addressid

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 248 total points
ID: 37057775
Insert Into Addresscontacts ("Name","Phone","Fax","Email","AddressID","IsDefault","NotificationType","NotifyOnNewOrder",
"NotifyOnDispatch","NotifyOnPickedUp","NotifyOnPOD","NotifyOnCancel")
Select “Name”,”Phone”,”Fax”,"Email","AddressID",1,4,0,0,0,0,0
From ABook


will insert(add) all rows from abook into addresscontacts....

did you want to add the rows or update rows if they already existed?

do you only need to add rows which don't already exist on addresscontacts?
try this

Insert Into Addresscontacts ("Name","Phone","Fax","Email","AddressID","IsDefault","NotificationType","NotifyOnNewOrder",
"NotifyOnDispatch","NotifyOnPickedUp","NotifyOnPOD","NotifyOnCancel")
Select “Name”,”Phone”,”Fax”,"Email","AddressID",1,4,0,0,0,0,0
From ABook
where not exists (select Addressid from addresscontacts where addresscontactsid= ABook.Addressid)

?

please clarify your requirement
some example data may assist us



0
 

Author Comment

by:W.E.B
ID: 37058394
Hello,
I'm trying to insert new contacts into my AddressContacts table,
Please see attached, the exact columns that I have in my database for both columns, addresses and addresscontacts

 I get an error mesage,

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AddressContacts_Addresses". The conflict occurred in database "Database", table "dbo.Addresses", column 'AddressID'.

Insert Into Addresscontacts ("Name","Phone","Fax","Email","AddressID","IsDefault","NotificationType","NotifyOnNewOrder",
"NotifyOnDispatch","NotifyOnPickedUp","NotifyOnPOD","NotifyOnCancel")
Select "Name","Phone","Fax","Email","AddressID",1,4,0,0,0,0,0
From ABook
where not exists (select Addressid from addresscontacts where addresscontacts.AddressID = ABook.Addressid)

Thanks,

ABook.xls
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37058588
you need to insert the addresses into the address table before you can use them in the address contact table...

0
 

Author Comment

by:W.E.B
ID: 37058667
I did, I inserted all addresses (few thousands).
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37059489
please show me the ddl for the 3 tables... address , addresscontacts, abook

are you sure that you committed the addresses before processing the address contacts?

are you certain that the address contacts are using the correct values for the addressid column?

can you explain what you system is doing...

e.g. i'd find it easier to understand if i knew why an address needed a contact.... rather than an organisation/person
having "locations" with contact details....
0
 

Author Closing Comment

by:W.E.B
ID: 37125857
Hello,
sorry for not responding to this earlier, I was away,
FYI,
Issue was resolved.

I had duplicate values on the same sheet.

Thanks for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question