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(),
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
@Counter varchar(3)
set @Counter = '000'
Set @Counter = @Counter + 1
@Date = CONVERT(char(8),GETDATE(),
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
ASKER
The date is fine. The part that I am having trouble with is incrementing the number following the date.
Or you could add an CASE statement
SELECT CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE '0' + @counter END
SELECT CASE len(@counter) WHEN 1 THEN '00' + @counter ELSE '0' + @counter END
another option is use this , which wont need any count part
REPLACE(REPLACE(REPLACE(CO
REPLACE(REPLACE(REPLACE(CO
ASKER
ptjcb
Can you give an example?
Can you give an example?
ASKER
ptjcb
Also, the file is not incrementing when i run it a second time. it just overwrites the current file.
Also, the file is not incrementing when i run it a second time. it just overwrites the current file.
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(),
SELECT @Date
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(),
SELECT @Date
ASKER
That worked the first time around but did not increment with the second file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(),
SELECT @Date
Set @cnt = @cnt + 1
END
This would create 001 through 009. You would change the WHILE statement to make it higher.
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(),
SELECT @Date
Set @cnt = @cnt + 1
END
This would create 001 through 009. You would change the WHILE statement to make it higher.
ASKER
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?
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?
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(),
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
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(),
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
SELECT CONVERT(varchar,@date,112)