Solved

loop through simple insert statement

Posted on 2009-07-15
1
230 Views
Last Modified: 2012-05-07
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

Open in new window

0
Comment
Question by:tbaseflug
1 Comment
 
LVL 13

Accepted Solution

by:
TommyTupa earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

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

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

Join & Ask a Question