?
Solved

Unique filename

Posted on 2006-07-06
12
Medium Priority
?
484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

719 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