Solved

loop through simple insert statement

Posted on 2009-07-15
1
224 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now