Solved

Unique filename

Posted on 2006-07-06
12
479 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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