Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Unique filename

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
earngreen
Asked:
earngreen
  • 5
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
replace @date with

SELECT CONVERT(varchar,@date,112)
0
 
earngreenAuthor Commented:
The date is fine. The part that I am having trouble with is incrementing the number following the date.
0
 
ptjcbCommented:
Or you could add an CASE statement


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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Aneesh RetnakaranDatabase AdministratorCommented:
another option is use  this , which wont need any count part

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

Can you give an example?
0
 
earngreenAuthor Commented:
ptjcb

 Also, the file is not incrementing when i run it a second time. it just overwrites the current file.
0
 
ptjcbCommented:
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
 
earngreenAuthor Commented:
That worked the first time around but did not increment with the second file.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
ptjcbCommented:
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
 
earngreenAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now