Using INSERT where there is a multiple value

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

Using INSERT where there is a multiple value

Author

Topic

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 06:15:27

Hi

I am trying to do a similar thing to what I did before but now using the following command.

Basically I want to INSERT the clauses shown in the INSERT statement into the VALUES as shown but where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values. Do I need to somehow use UNION or UNION ALL for this?

I am getting the following error when I run the statement as shown below:-

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

declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)

insert into [communications]
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select address_number, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from communications where address_number in (126, 127, 128)

Thanks for all your help.

Jon

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 06:22:48

No, just do as you were doing before, but your contact_number can not be NULL as this is your primary key.

bmfjon
Starting Member

14 Posts

Posted - 2007-11-29 : 06:25:33

Hi Rick

Thanks for your answer.

This is a similar database but with a slightly different table structure.

On this database the primary key is address_number.

So I am trying to update all records in the COMMUNICATIONS table with the website address where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values (ie. 126, 127, 128).

I know it's confusing and I'm probably not explaining myself too well!

Thanks,

Jon

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-29 : 08:46:06

quote:
Originally posted by bmfjon

Hi Rick

Thanks for your answer.

This is a similar database but with a slightly different table structure.

On this database the primary key is address_number.

So I am trying to update all records in the COMMUNICATIONS table with the website address where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values (ie. 126, 127, 128).

I know it's confusing and I'm probably not explaining myself too well!

Thanks,

Jon

they way you are saying that addressnumber is primary key and you are reutilising it againwhile updating then it will certainly raise the error
as
create table testing(id int,[name] char(12),address int primary key)
insert into testing values(1,'aaa',1)
insert into testing values(1,'aaa',2)
insert into testing values(1,'aaa',3)

insert into testing id,name,address select id,name,address from testing where address=3
now as there is already existing a value 3 as address and again you are trying to insert so it will create problem

and one more thing can u make the problem little more clear i am still 80-85 % known to the problem

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-29 : 21:01:45

quote:
Cannot insert duplicate key row in object 'communications' with unique index 'c2962cn7081

the index in on which columns ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources