Solved

Unique filename

Posted on 2006-07-06
12
478 Views
Last Modified: 2008-02-01
I am triying to increment a filename wiith 000,001,002. I am exporting a file from SQl server that may or may not have more than one file in a day. I want to A concatenate todays date plus increment a number to the end of the file. concatenating the date works fine but incrementing the number does not. any ideas?

@Counter varchar(3)
set @Counter = '000'
Set @Counter = @Counter + 1
@Date = CONVERT(char(8),GETDATE(),112)


   SET @bcpCommand = 'bcp "Select * from yourtable" queryout  C:\Test'+ @Date + '+@Counter+'.txt -c -t"," -Syourservername -Uusername -Ppassword'
   EXEC master..xp_cmdshell @bcpCommand


desired result

test20060707001.txt
test20060707002.txt
etc




0
Comment
Question by:earngreen
  • 5
  • 4
  • 3
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17052285
replace @date with

SELECT CONVERT(varchar,@date,112)
0
 

Author Comment

by:earngreen
ID: 17052298
The date is fine. The part that I am having trouble with is incrementing the number following the date.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17052305
Or you could add an CASE statement


SELECT CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17052325
another option is use  this , which wont need any count part

REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),120),'-',''),':',''),' ','')
0
 

Author Comment

by:earngreen
ID: 17052370
ptjcb

Can you give an example?
0
 

Author Comment

by:earngreen
ID: 17052493
ptjcb

 Also, the file is not incrementing when i run it a second time. it just overwrites the current file.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17052521
DECLARE @Counter varchar(3), @date varchar(12)
Set @counter = 10
Set @Counter = @Counter + 1
SELECT @COUNTER = CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END
SELECT @Date = CONVERT(char(8),GETDATE(),112) + CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END
SELECT @Date
 
0
 

Author Comment

by:earngreen
ID: 17052643
That worked the first time around but did not increment with the second file.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17052885
Hi,
I think you can either go with my last solution. or you can keep the count values in another table ...

CREATE TABLE CountStore ( i int )
GO
INSERT INTO CountStore select 1
DECLARE @Counter varchar(3), @date varchar(12)
Select @counter = i+1
FROM CountStore

SELECT @COUNTER = CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END

SELECT @Date =  CONVERT(char(8),GETDATE(),112)
   SET @bcpCommand = 'bcp "Select * from yourtable" queryout  C:\Test'+ @Date + '+@Counter+'.txt -c -t"," -Syourservername -Uusername -Ppassword'
   EXEC master..xp_cmdshell @bcpCommand
IF @@ERROR <> 0
  Update CountStore SET i = i +1  


0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17053012
You could use a WHILE to control how many times it increments - I have no idea how many file names you would need.

DECLARE @Counter varchar(3), @date varchar(20), @cnt tinyint

SELECT @Cnt = 1

WHILE ( @cnt ) < 10 BEGIN

SELECT @COUNTER = @cnt
SELECT @COUNTER = CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END
SELECT @Date = CONVERT(char(8),GETDATE(),112) + CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END

SELECT @Date

Set @cnt = @cnt + 1

END

This would create 001 through  009. You would change the WHILE statement to make it higher.
0
 

Author Comment

by:earngreen
ID: 17053382
aneeshattingal

It looks as if your solution will work. If you could help me with one more thing, the only problem that I have left is how to reset the counter for tommorrow. I was thinking that If I had a last run date column and wrote a If statement that says

If greater than last run date Update set i to 0.

Do you think this will work or do you have some thoughts on this?

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17092615
You can add a new column in the table to store the Getdate() value

CREATE TABLE CountStore ( i int , CurDate smalldatetime)
GO
INSERT INTO CountStore select 1 , getdate()
GO

DECLARE @Counter varchar(3), @date varchar(12)
SELECT @Date =  CONVERT(char(8),GETDATE(),112)

If @Date < CONVERT(char(8), (SELECT curDate FROM CountStore),112)
  UPDATE CountStore
    SET  i = 0 ,
           CurDate = getdate()

Select @counter = i+1
FROM CountStore

SELECT @COUNTER = CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE  '0' + @counter END


   SET @bcpCommand = 'bcp "Select * from yourtable" queryout  C:\Test'+ @Date + '+@Counter+'.txt -c -t"," -Syourservername -Uusername -Ppassword'
   EXEC master..xp_cmdshell @bcpCommand
IF @@ERROR <> 0
  Update CountStore SET i = i +1  
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 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