need help in looping records

Question

need help in looping records

Feebie

Right there with Babe

Points: 785
More actions
May 13, 2009 at 3:05 pm

#95639

Hi,
I have a query like 'Select *
from tskill_codes a
Where a.skillcodeidno not in
(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b)
'
so when i run this query i get multiple records...
I would like to insert all these other records into other tables at same time or if possible can loop through and insert these records in?
Could you please let me know how to do that.
i tried this,,,,
Insert Into tPM_Cmptc_Cat_Skill_Codes (compcatidno, skillcodeidno, fromeffectdate, toeffectdate)
select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate
From tskill_codes a
Where a.skillcodeidno in (Select *
from tskill_codes a
Where a.skillcodeidno not in
(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b))
but it didnt work as the logic is wrong..
please help me..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Anjana (5/13/2009)
i tried this,,,,
Insert Into tPM_Cmptc_Cat_Skill_Codes (compcatidno, skillcodeidno, fromeffectdate, toeffectdate)
select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate
From tskill_codes a
Where a.skillcodeidno in (Select *
from tskill_codes a
Where a.skillcodeidno not in
(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b))
but it didnt work as the logic is wrong..

What's wrong with it's logic?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

WayneS SSC Guru Points: 95460 More actions · Answer 2

Anjana (5/13/2009)
I would like to insert all these other records into other tables at same time or if possible can loop through and insert these records in?
Insert Into tPM_Cmptc_Cat_Skill_Codes (compcatidno, skillcodeidno, fromeffectdate, toeffectdate)
select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate
From tskill_codes a
Where a.skillcodeidno in (Select *
from tskill_codes a
Where a.skillcodeidno not in
(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b))
but it didnt work as the logic is wrong..
please help me..

1. As long as the fields in the table being inserted to are of the same datatype as what your query runs, then this should work.

2. Is your query running correctly by itself?

3. You can also do it in this format:

select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate

Into tPM_Cmptc_Cat_Skill_Codes ----<<<< NOTE THE NEW LINE

From tskill_codes a

Where a.skillcodeidno in (Select *

from tskill_codes a

Where a.skillcodeidno not in

(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b))

This would create a new table with all of the data returned by your query. If that table already exists, it will be overwritten.

By any chance, is the tPM_Cmptc_Cat_Skill_Codes.compcatidno field part of any kind of a unique constraint, or is it the sole field in a PK?

If so, then your query, which is always sending in '-10001' into that field, would be trying to insert a duplicate value, and the whole thing would fail.

HTH,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Feebie Right there with Babe Points: 785 More actions · Answer 3

hi,

compcatidno is not a unique constraint...

and datatypes for both the filelds are same.

and when i execute that statemet i get an error message like....

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

When using IN clause the sub-query must only return a single column but in this case "*" is used.

Modified Query

Insert Into tPM_Cmptc_Cat_Skill_Codes (compcatidno, skillcodeidno, fromeffectdate, toeffectdate)

select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate

From tskill_codes a

Where a.skillcodeidno in (Select a.skillcodeidno

from tskill_codes a

Where a.skillcodeidno not in

(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b))

But a little looking, this query can be further reduced to

Insert Into tPM_Cmptc_Cat_Skill_Codes (compcatidno, skillcodeidno, fromeffectdate, toeffectdate)

select '-10001', a.skillcodedomainidno, a.skillcodefromeffectdate, a.skillcodetoeffectdate

From tskill_codes a

Where a.skillcodeidno not in

(Select b.skillcodeidno from tPM_Cmptc_Cat_Skill_Codes b)

Let me know if the above query works for you.

--Ramesh