Insert help - 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 2012 Forums

Transact-SQL (2012)

Insert help

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 14:57:24

How do insert a new record with cus_code = 'A' for each customer? Duplicates are not allowed.

list_id cus_id cus_code code_state

1 20 x Y
2 20 y y
3 20 z y
4 20 p N
5 21 x N
2 21 y y
3 21 z y
4 21 p N

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 15:09:36

what are the needed values for list_id and code_state then?

Too old to Rock'n'Roll too young to die.

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-13 : 15:13:14

you can just use list_id=list_id and code_state='Y'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 15:32:06

[code]declare @sample table (list_id int, cus_id int, cus_code varchar(30), code_state varchar(30))
insert @sample
select 1,20,'x','y' union all
select 2,20,'y','y' union all
select 3,20,'e','y' union all
select 1,21,'z','y' union all
select 5,21,'A','y'

select * from @sample

insert @sample(list_id, cus_id, cus_code, code_state)
select
max(list_id),
cus_id,
'A',
'Y'
from @sample t1
where not exists(select * from @sample t2 where t2.cus_code='A' and t2.cus_id = t1.cus_id)
group by cus_id

select * from @sample
order by cus_id[/code]

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources