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

Transact-SQL (2005)

Multiple Inserts

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2011-04-06 : 19:19:18

I am trying to insert multiple rows into a table and now I've confused myself so bad ;(

Table: Entity
ObjID, LName
22 Brown
23 Stanley
24 Houston
25 Barr
33 Bee

Table: EntityAfltn
ObjID, VerNbr,AftCd
22 1 staff
22 1 Cont
33 1 Aftl

Table: tmpUsers
ObjID IsStaff IsCont IsAflt
22 Y Y N
23 Y Y Y
33 Y N Y

INSERT into EntityAfltn (ObjID,'1', AftCd)
Select e.ObjID, e.IsStaff, e.IsCont, e.IsAflt
FROM TMPUSERS e
JOIN Entity i
ON i.ObjID = e.ObjID
WHERE NOT EXISTS (Select * EntityAfltn ?????????????????? IM Lost ?????????????

I want there to be a row added to the EntityAfltn table for each user and their AftCd. (will be multiple rows for some users who are marked in the tmpUsers as IsStaff = Y and IsCont = Y etc...

Please Help!
R/P

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 20:32:10

Are you looking for something like this?


select
	PersonId,
	case when [Staff] is not null then 'Y' else 'N' end as [Staff],
	case when [Cont] is not null then 'Y' else 'N' end as [Cont],
	case when [Aftl] is not null then 'Y' else 'N' end as [Aftl]
from
	EntityAfltn 
PIVOT
	(max(AftCd) for AftCd in ([Staff],[Cont],[Aftl]))p

poser
Posting Yak Master

124 Posts

Posted - 2011-04-07 : 11:03:27

I don't think that is what Im looking for..

I want to add everyone from the tmpuser table that has a matching objid in the entity table into the EntityAfltn table.

And if the tmpuser(23) is marked IsStaff = Y than insert a row in EntityAfltn table. If tmpuser(23) is also marked as IsCont=Y then enter another row into the EntityAfltn table.
If tmpuser is marked N for any of them then do not enter into the EntityAfltn table

Only enter into the EntityAfltn table if it does not exist there.

Im really new and horrible explaining what I'm trying to do...
Thank you for trying to help me...
R/P

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 11:18:43

Not your fault, poser. Your initial post had "INSERT into EntityAfltn (ObjID,'1', AftCd)" clearly indicating what you are trying to do. Would this be it? If not, I promise, third time would be the charm

Two questions though:

1. In this query, I am not even using the Entity table - which is fine for populating the EntityAfltn table. But, if there was a reason you included that table in the description of the problem, I missed it.

2. What should be the value of VerbNbr in the EntityAfltn table? If there is a rule, we can use that, but based on your sample data, I just set it as 1.

INSERT into EntityAfltn ([ObjID],VerbNbr, AftCd)
select
   t.[ObjId],
   1 as VerbNbr,
   'Staff' as AftCd
from
   tmpUsers
where
   IsStaff = 'Y'
union all
select
   t.[ObjId],
   1 as VerbNbr,
   'IsCont' as AftCd
from
   tmpUsers
where
   IsCont = 'Y'
union all
   select
   t.[ObjId],
   1 as VerbNbr,
   'IsAflt' as AftCd
from
   tmpUsers
where
   IsAflt = 'Y'

poser
Posting Yak Master

124 Posts

Posted - 2011-04-07 : 19:25:48

I'm really sorry for still being confusing.
Maybe this will simplify what I'm trying to do..
:)

Table: EntityAfltn
ObjID, VerNbr,AftCd
22 1 staff
22 1 Cont
33 1 Aftl

Table: tmpUsers
ObjID IsStaff IsCont IsAflt
22 Y Y N
23 Y Y Y
33 Y N Y

I want to insert into EntityAfltn everything from tmpusers
It can not insert duplicate rows.

This is the result I am looking for adding those last 4 rows into the EntityAfltn table.

EntityAfltn
22 1 staff
22 1 Cont
33 1 Aftl

23 1 staff
23 1 Cont
23 1 Aftl
33 1 Staff

Thank you for your patience
R/P

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 20:36:47

I think I got it this time. I had missed that you wanted only entries that did not exist to be inserted. If this also doesn't work, we will make it work next time - third time is ALWAYS a charm!

--- CREATE TEST DATA
create table tmpUsers([ObjID] int, IsStaff varchar(1), IsCont varchar(1), IsAflt varchar(1));
create table EntityAfltn([ObjId] int, VerNbr int, AftCd	varchar(32));

insert into tmpUsers values (22, 'Y','Y','N');
insert into tmpUsers values (23, 'Y','Y','Y');
insert into tmpUsers values (33, 'Y','N','Y');

insert into EntityAfltn values(22,1, 'staff');
insert into EntityAfltn values(22, 1, 'Cont');
insert into EntityAfltn values(33, 1, 'Aftl');

select * from tmpusers;
select * from EntityAfltn;


--- DO THE INSERTS
INSERT into EntityAfltn ([ObjID],VerbNbr, AftCd)
select
   t.[ObjId],
   1 as VerbNbr,
   'Staff' as AftCd
from
   tmpUsers t
where
   IsStaff = 'Y'
   and not exists (select * from EntityAfltn e where t.[ObjId] = e.[ObjId] and e.AftCd = 'Staff')
union all
select
   t.[ObjId],
   1 as VerbNbr,
   'IsCont' as AftCd
from
   tmpUsers t
where
   IsCont = 'Y'
   and not exists (select * from EntityAfltn e where t.[ObjId] = e.[ObjId] and e.AftCd = 'Cont')
union all
   select
   t.[ObjId],
   1 as VerbNbr,
   'IsAflt' as AftCd
from
   tmpUsers t
where
   IsAflt = 'Y'
   and not exists (select * from EntityAfltn e where t.[ObjId] = e.[ObjId] and e.AftCd = 'Aftl')

poser
Posting Yak Master

124 Posts

Posted - 2011-04-08 : 12:58:52

That's exactly what I needed!
Thank you for your patience and diligence

R/P

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 13:05:35

You are quite welcome, glad it worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources