Duplicate row will not inserted

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 2005 Forums

Transact-SQL (2005)

Duplicate row will not inserted

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-03-24 : 23:52:21

My table and rows as follow,

declare @t1 table
(idx smallint identity(1,1),cd smallint,seq tinyint,remk varchar(100));
insert into @t1 values(2,1,'p1');
insert into @t1 values(2,2,'p11');
insert into @t1 values(2,1,'p14');
insert into @t1 values(2,3,'p12');
insert into @t1 values(4,1,'p15');
insert into @t1 values(2,1,'p17');
insert into @t1 values(5,3,'p13');
insert into @t1 values(2,3,'p13');
insert into @t1 values(1,3,'p13');
insert into @t1 values(2,3,'p13');

So my result as follow,


idx  | cd | seq |  remk
--------------------------------------
1	2	1	p1
2	2	2	p11
3	2	1	p14
4	2	3	p12
5	4	1	p15
6	2	1	p17
7	5	3	p13
8	2	3	p13
9	1	3	p13
10	2	3	p13

Now, i've new table as follow. then i want to insert value in @t1 into #t1,

create table #t1
(idx smallint identity(1,1) primary key clustered,cd smallint,seq tinyint,remk varchar(100));
alter table #t1 add constraint t1p01 unique(cd,seq);

insert into #t1
--select cd,seq,remk from @t1

drop table #t1

How to customize my

select cd,seq,remk from @t1

then, all duplicate row is not insertered.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-25 : 00:35:04

You are creating a unique constraint on CD & SEQ.
--The second insert will raise an error
insert into @t1 values(2,1,'p1');
insert into @t1 values(2,1,'p14');

Out of the above two which value you need for Remk ?

Regards,
Bohra

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-03-25 : 00:50:28

How to customize my
select cd,seq,remk from @t1

then the resultset will return clean result and able to insert into #t1

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-25 : 02:06:39

Hi,

I am still not clear.

If you are expecting the below data in #T1 then you need to add rmk column in the constraint t1p01.

alter table #t1 add constraint t1p01 unique(cd,seq,remk);

insert into #t1
select distinct cd,seq,remk from @t1

idx cd seq rmk
1 1 3 p13
2 2 1 p1
3 2 1 p14
4 2 1 p17
5 2 2 p11
6 2 3 p12
7 2 3 p13
8 4 1 p15
9 5 3 p13

If you are expecting some other data in #t1 then post the expected result.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources