Solved

Unique filename

Posted on 2006-07-06
12
477 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
 
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
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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 Select Distinct 8 35
SQL Query - Issue with Top Statement 5 31
MS SQLK Server multi-part identifier cannot be bound 5 25
Error running stored procedure 11 8
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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now