loop through ID's to insert data, better method? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

loop through ID's to insert data, better method?

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2013-08-29 : 08:13:39

Hi I have created a cursor based loop to insert all possible medical conditions for each ID. As below. The ID are located in another SQL table and the conditions are listed in the insert statements.



DECLARE @Parameter int

DECLARE IDs CURSOR LOCAL FOR select ID from MyIDtable

OPEN IDs
FETCH NEXT FROM IDs into @Parameter
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC('
INSERT INTO MYConditionTable (ID, Condition)
VALUES (' + @Parameter + ', ''Allergy'');
INSERT INTO MYConditionTable (ID, Condition)
VALUES (' + @Parameter + ', ''Asthma'');
INSERT INTO MYConditionTable (ID, Condition)
VALUES (' + @Parameter + ', ''Colitis'');

   ')

    FETCH NEXT FROM IDs into @Parameter
END

CLOSE IDs
DEALLOCATE IDs

I wondered if anyone knew a better way to do this perhaps with a for each loop.

thanks

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-29 : 08:19:00

If you put the conditions in a table you could do something like

INSERT INTO MYConditionTable(ID, Condition)
SELECT A.ID, B.Condition 
FROM MyIDtable
CROSS JOIN ConditionList;

This is not tested.

djj

MrBloom
Starting Member

36 Posts

Posted - 2013-08-29 : 09:31:07

Thanks but I think this wouldn't work for what I want as my ID column is unique so I would get.

1 Allergy
2 Allergy
3 Allergy
1 Asthma
2 Asthma
3 Asthma
etc...

and I want

1 Allergy
1 Asthma
2 Allergy
2 Asthma

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-29 : 09:38:55

quote:
Originally posted by MrBloom

Thanks but I think this wouldn't work for what I want as my ID column is unique so I would get.

1 Allergy
2 Allergy
3 Allergy
1 Asthma
2 Asthma
3 Asthma
etc...

and I want

1 Allergy
1 Asthma
2 Allergy
2 Asthma

After staring at the cursor code you posted and the solution djj55 posted, I am not able to discern how they would be different. If you have 3 id's and and three conditions, you would get 9 rows. So would djj55's solution. So I am not clear on how djj55's solution ends up with six rows and yours ends up with only four rows.

I am sure I am missing something, but I don't know what.

MrBloom
Starting Member

36 Posts

Posted - 2013-08-29 : 09:45:31

Sorry it should have read this

quote:

1 Allergy
2 Allergy
3 Allergy
1 Asthma
2 Asthma
3 Asthma
1 Colitis
2 Colitis
2 Colitis
etc...

and I want

1 Allergy
1 Asthma
1 Colitis
2 Allergy
2 Asthma
2 Colitis
3 Allergy
3 Asthma
3 Colitis

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-29 : 10:07:27

quote:
Originally posted by MrBloom

Sorry it should have read this

quote:

1 Allergy
2 Allergy
3 Allergy
1 Asthma
2 Asthma
3 Asthma
1 Colitis
2 Colitis
2 Colitis
etc...

and I want

1 Allergy
1 Asthma
1 Colitis
2 Allergy
2 Asthma
2 Colitis
3 Allergy
3 Asthma
3 Colitis

I am still not getting it. It's my slow day

The two results ets you have are the same - just that the ordering is different. If you order djj55's result set by ID, then you will get the same set as yours.

MrBloom
Starting Member

36 Posts

Posted - 2013-08-29 : 10:12:21

No sorry, really my slow day. sorry for the trouble.
and thanks

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-29 : 17:01:04

You can create the table "in-line" if you want to:



INSERT INTO MyConditionTable( ID, Condition )
SELECT A.ID, B.Condition 
FROM MyIDtable
CROSS JOIN (
    SELECT 'Allergy' AS condition UNION ALL
    SELECT 'Asthma' UNION ALL
    SELECT 'Colitis'
) AS B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources