insert using where exists - 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

General SQL Server Forums

New to SQL Server Programming

insert using where exists

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-04-04 : 07:28:01

Hello everyone.

I am trying to perform a bulk insert using the where exists clause.

my script i am using is below..

begin tran

insert into attribute ( individual_ref , attribute_source, code_type, attr_code_ref, attribute_code, valid_from, valid_to)
values ('45441','1','320','3629','CAMEM1','2011-04-04 00:00:00:000',NULL)

where exists(

select top 50 percent ind.individual_ref, memberclass .lookup_full_desc into test
from individual ind , lookup memberclass, member, attribute attribute3
where ( UPPER( memberclass.lookup_full_desc) IN ('CERTIFIED','FELLOW','MEMBER'))
and memberclass.lookup_ref = member.member_class
and member.individual_ref = ind.individual_ref
and member.member_status = 33
and attribute3.code_type = 320
and attribute3.attr_code_ref = 3611
and UPPER(attribute3.attribute_code) LIKE 'CAMP1'
and UPPER(attribute3.detail) LIKE '%BEFORE%'
and attribute3.individual_ref = ind.individual_ref
--order by ind.individual_ref asc
)

and the error i am receiving is

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

can someone tell me where i am going wrong please.

Kind Regards

Rob

MCTS / MCITP certified

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-04 : 07:56:07

Hi,
1st, I think you can not use SELECT INTO statement into a subquery.
2nd, your INSERT query is not correct, the correct version should be:

INSERT INTO TableName (comma separated column list)
SELECT your_rows_here
--FROM missed.
WHERE your_condition_here;

______________________

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-04-04 : 07:59:43

what about using a table variable

begin tran

declare @individuals table
(ref int)

insert into @individuals select individual_ref from individual

insert into attribute ( individual_ref , attribute_source, code_type, attr_code_ref, attribute_code, valid_from, valid_to)
values (@individuals.ref,'1','320','3629','CAMEM1','2011-04-04 00:00:00:000',NULL)

where @individuals.ref in(

select top 50 percent ind.individual_ref, memberclass .lookup_full_desc
from individual ind , lookup memberclass, member, attribute attribute3
where ( UPPER( memberclass.lookup_full_desc) IN ('CERTIFIED','FELLOW','MEMBER'))
and memberclass.lookup_ref = member.member_class
and member.individual_ref = ind.individual_ref
and member.member_status = 33
and attribute3.code_type = 320
and attribute3.attr_code_ref = 3611
and UPPER(attribute3.attribute_code) LIKE 'CAMP1'
and UPPER(attribute3.detail) LIKE '%BEFORE%'
and attribute3.individual_ref = ind.individual_ref
--order by ind.individual_ref asc
)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-04 : 08:09:47

It maybe work:

insert into attribute ( individual_ref , attribute_source, code_type, attr_code_ref, attribute_code, valid_from, valid_to)
select individual_ref ,'1','320','3629','CAMEM1','2011-04-04 00:00:00:000',NULL
from individual
where individual_ref in(
select top 50 percent ind.individual_ref
from individual ind , lookup memberclass, member, attribute attribute3
where ( UPPER( memberclass.lookup_full_desc) IN ('CERTIFIED','FELLOW','MEMBER'))
and memberclass.lookup_ref = member.member_class
and member.individual_ref = ind.individual_ref
and member.member_status = 33
and attribute3.code_type = 320
and attribute3.attr_code_ref = 3611
and UPPER(attribute3.attribute_code) LIKE 'CAMP1'
and UPPER(attribute3.detail) LIKE '%BEFORE%'
and attribute3.individual_ref = ind.individual_ref
--order by ind.individual_ref asc
)

______________________

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-04-04 : 08:21:49

I have tried the above but i receive the following error.

Msg 128, Level 15, State 1, Line 11
The name "individuals.ref" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

MCTS / MCITP certified

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-04 : 08:27:57

see the changes in the edited post.

______________________

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-04-04 : 08:35:43

still get the same result.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-04-04 : 08:46:09

can you not insert a value by stating a column name instead of a value?