While loop order messed up in csv file

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

Transact-SQL (2012)

While loop order messed up in csv file

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 16:38:14

I'm trying to create a procedure with a while loop [code below] that's eventually creating a new csv file for each loop. From the code, [MAP] is the first row. However when I run the query, for certain i's everything looks fine, but for others I end up getting data up top and then [MAP] ends up being in later rows...basically the order is all messed up. I've had this issue with a cursor before as well...what's going on internally and what's a good way of fixing this issue?


IF OBJECT_ID('dbo.Loss_csv', 'U') IS NOT NULL
  DROP TABLE dbo.Loss_csv
GO

create table dbo.Loss_csv 
(
	col1 varchar(100),
	col2 varchar(100)
        --etc up to 25 columns
)

declare @DFP nvarchar(max)
declare @i as int = 1

set @StartDate='03/1/2014'
set @EndDate='06/30/2014'

while @i<7
begin

   if @i=1 begin
	
      delete from dbo.Loss_csv
		
      insert into dbo.Loss_csv values('[MAP]','','','','','','','','','','','','','','','','','','','','','','','','')
      insert into dbo.Loss_csv values('Segment','Subsegment','Ent','LOB','Cov','Cur','ExchRate','ExpType','TBD1','TBD2','TBD3','TBD4','TBD5','Claim#','AYDate','EvalDate','GPaidLoss','GPaidALAE','GSSRecd','GCaseLoss','GCaseALAE','GSSResv','GRepCt','GClsCt','GClsCtPay')

   IF OBJECT_ID('dbo.DFP_Loss', 'U') IS NOT NULL
	DROP TABLE dbo.DFP_Loss	 
        
        select ...
        into dbo.DFP_Loss
        from ....
        where ....

        union all
 
        select ...
        from ....
        where ....

        set @DFP = 'select * from dbo.DFP_Loss order by Cov, [Claim#]], AYDate, EvalDate'
        insert into dbo.Loss_csv
        execute sp_executesql @DFP

        --Create csv file      
        execute xp_cmdshell .........

   end
   else if @i=2
   begin

     PRETTY MUCH THE SAME THING AS ABOVE BUT FOR A DIFFERENT LINE
   
   END
   
   set @i=@i+1

end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-14 : 16:56:54

You will need an ORDER BY some column.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 17:01:27

There's an order by (which i just updated in the dynamic sql when input into the csv file. But it's not the issue of the data not being in order, it's the issue of the text being placed after the data...which is weird.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-15 : 00:16:00

No, no, no...
This line execute xp_cmdshell ......... will need the same ORDER BY to have the correct order in the file.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-15 : 09:08:07

The basic problem is that there is no ordering column on dbo.Loss_csv. SQL can insert rows into that table anyway it likes. The order of your INSERTS does not affect how SQL actually stores the rows or later retrieves them. You should add a column to insert the row number as you go:


create table dbo.Loss_csv 
(
        rn int,
	col1 varchar(100),
	col2 varchar(100)
        --etc up to 25 columns
)
]

and your first two inserts become:


insert into dbo.Loss_csv values(0, '[MAP]' ...
insert into dbo.Loss_csv values(1, 'Segment'. ...

Then, when adding the data rows:


set @DFP = 'select 1+Row_Number() over (order by ...), * from dbo.DFP_Loss order by Cov, [Claim#]], AYDate, EvalDate'
        insert into dbo.Loss_csv

but you DO need to fill in the order by ... with something meaningful.

Now, when you actually create the CSV file, it will have an extra column that you can use for sorting

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-15 : 11:40:34

I added the new row with the numbers and it is very helpful. However, is there a way to order by automatically instead of having to go to the csv file and order by that column? Not sure how to order by in a shell command, if that's even possible.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-15 : 11:44:43

What is the actual shell command?

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-15 : 11:50:24

execute xp_cmdshell 'sqlcmd -S (local) -d ODS_Actuarial -E -s, -W -h-1 -i "E:\Database\MSSQL\ODS_Actuarial\Procedures\ExportLoss.sql" -o "E:\Database\Data\ODS_Actuarial\DFP\Result_Loss.csv"'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-15 : 11:56:04

OK, so what's in E:\Database\MSSQL\ODS_Actuarial\Procedures\ExportLoss.sql

You can put the ORDER BY in that script and remove the column from the output so you're left with just the CSV data

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-15 : 11:57:57

oh i'm dumb...it's just selecting all the columns to input into the csv file...i can order by there..DUH

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-15 : 12:09:38

thank you everyone!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-15 : 14:22:45

Any reason you didn't use the bcp command for this? It's probably easier than SQLCMD in your case:

http://msdn.microsoft.com/en-us/library/ms191516.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources