Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# loop through simple insert statement

Posted on 2009-07-15
Medium Priority
239 Views
I have the following code - the below has to be executed 18 times - the column CPT has 18 occurances in it so rename it each execution and execute - is there a way - starting with CPT1 to execute the below through CPT18
``````INSERT INTO dbWorking2.dbo.[tblNCD_LAB]
SELECT     replace(ltrim(rtrim(CPT18)),',',''), ltrim(rtrim(ICD9)), effDate, termDate, resCode
FROM         dbo.NCD_Lab_090630
WHERE     (CPT18 IS NOT NULL) and isnumeric(ICD9) = 1
group by CPT18, ICD9, effDate, termDate, resCode order by CPT18, ICD9, effDate, termDate, resCode
``````
0
Question by:tbaseflug
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
1 Comment

LVL 13

Accepted Solution

TommyTupa earned 2000 total points
ID: 24865908
This is untested but you get the gist.

Create a while loop and execute as dynamic SQL:

Declare @iCounter int
Declare @sCounter varchar(2)
Declare @SQL varchar(2000)

Set @iCounter = 1

While @iCounter <=18
BEGIN

Set @sCounter = cast(@iCounter as varchar(2))

Set @SQL = '
INSERT INTO dbWorking2.dbo.[tblNCD_LAB]
SELECT     replace(ltrim(rtrim(CPT18)),'','',''''), ltrim(rtrim(ICD9)), effDate, termDate, resCode
FROM         dbo.NCD_Lab_090630
WHERE     (CPT' + @sCounter + ' IS NOT NULL) and isnumeric(ICD9) = 1
group by CPT' + @sCounter + ', ICD9, effDate, termDate, resCode order by CPT' + @sCounter + ', ICD9, effDate, termDate, resCode
'
EXECUTE sp_executesql @SQL

--print @SQL

Set @iCounter = @iCounter +1

END
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month9 days, 20 hours left to enroll

#### 609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.