August 30, 2007 at 11:42 am
I am a newbie SQL programmer. I need to know the syntax/example for using nested loops with condition values fetched from a table/group of fixed values etc.
The SQL Query needs creates an XML file for each combination of 3 parameters District, Month, RepType.
District values need be fetched from a table “district” and column “dist_code”. The dist_code is of type “tinyint” and its value is between 1-70.
Month values can be either fetched from a table or a fixed set. Its value is from 1-12. The table is “month” and the column name is “stat_month”. The stat_month is of type char(2).
RepType values are 1-11 need to be fetched from a fixed set.
The SQL query is below (with hard coded parameter values)
*************Begin Code************
USE test
GO
Declare @TblName1 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @RepType varchar(2)
SET @TblName1 = '2006exp'
SET @District = 21
SET @Month = 6
SET @RepType = 2
Declare @fName varchar(50)
SET @fName = 'C:\XMLDump\'+@TblName1+'-'+@District+'-'+@Month+'-'+@Reptype+'.xml'
SET @TblName1 = '['+@TblName1+']'
Declare @SQuery nvarchar(3000)
Set @SQuery = 'bcp "EXEC test..free_CustomsDistrict_HS4_TEST '+@TblName1+', '+@District+', '+@Month+', '+@RepType+'" QueryOut "' + @fName + '" -c -T -r -t'
Exec master..xp_cmdshell @SQuery
************End Code*************
When I execute this query, it creates a file “2006exp-21-6-2.xml”.
The need the query to create 46x12x11 = 6072 files for all the combinations of 3 parameters.
August 30, 2007 at 12:54 pm
Already answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88696
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 1:10 pm
Thank you very much. I just posted a reply on the other form for errors I got when I parsed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply