-1

i have created an sp to export scripts i stored in a table to text files. What this sp do is if I give 100 as count then each file will have 100 scripts from the table. i am saving the file name as objects_1 ,objects_2 and so on. But what I need is . If I have 100 files. Then the 1st file should be names as objects_001 ,objects_002 , and the last file should be objects_100 . Can someone suggest an idea to do that . ,And please confirm this sp logic is correct . Thanks.


alter PROCEDURE USP_GenerateFile_Packets_new(@count int)
AS 
BEGIN 


DECLARE @ExportQry NVARCHAR(500)
DECLARE @sql NVARCHAR(500)
DECLARE @ExportPath NVARCHAR(100)
SET @ExportPath='C:\Upgrade\Scripts\'
DECLARE @BCPFileName NVARCHAR(50)
DECLARE @ReturnValue NVARCHAR(50)
DECLARE @K INT=1

declare @minval int=(select min(id) from vsl.ScriptTable)
--DECLARE @I INT=(SELECT COUNT(*) from [UpgradeDB].vsl.ScriptTable)
declare @count1 int=(select @count-1)
 WHILE @minval <=  (SELECT max(id) from [UpgradeDB].vsl.ScriptTable)
 
    BEGIN
        SET @ExportQry = 'SELECT TOP '+convert(nvarchar(100),@count)+' script FROM [UpgradeDB].vsl.ScriptTable where id>='+convert(nvarchar(100),@minval)+' and id<='+convert(nvarchar(100),@minval)+'+'+convert(nvarchar(100),@count1)+' order by id' 

        SET @sql = 'bcp "' + @ExportQry + ' " queryout ' + @ExportPath +  'OBJECTS_'+convert(nvarchar(100),@k)+'.txt -c -t^| -T -S ' + @@SERVERNAME;

    --select @sql
        EXEC @ReturnValue =  master..xp_cmdshell @sql


   SET @minval=@minval+@COUNT
   set @k=@k+1   
   END

END

--USP_GenerateFile_Packets_new 1500



1

2 Answers 2

1

Replace your SET @sql with this. This format the @k to the requested format.

SET @sql = 'bcp "' + @ExportQry + ' " queryout ' + @ExportPath +  'OBJECTS_'+RIGHT(CONCAT('000',@k), 3)+'.txt -c -t^| -T -S ' + @@SERVERNAME;
1
1

Adding some changes to Peter's answer

SET @sql = 'bcp "' + @ExportQry + ' " queryout ' + @ExportPath +  'OBJECTS_'+RIGHT(CONCAT(REPLICATE('0', @len),@k), @len)+'.txt -c -t^| -T -S ' + @@SERVERNAME

@len should be the length of the number of files .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.