Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday 3 February 2020

To change file name of text files from SQL QUERY

----------------To change file name of text files from SQL QUERY-------------------------------------------------------------

create table #test(col   varchar(1000))
create table #files(name varchar(1000),rename varchar(1000))

Declare @path as varchar(500)='\\sharepath\filesfolder\test\'
Declare @ssql as varchar(500)='dir ' + @path
Declare @col as varchar(500)=''
Declare @col1 as varchar(500)=''

insert into #test
Exec xp_cmdshell @ssql


--------Tables

insert into #files
select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.Table','')

-------SPs

--insert into #files
--select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
--rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.StoredProcedure','')


from #test where PATINDEX('%.%',col) > 0

delete
from #files where  name in (' .',' ..')

select @col =name , @col= rename
from #files

while (1=1)
Begin
select  top 1 @col =name , @col1= rename from #files
if @col = ''
begin
break
return
End

set @ssql = 'rename  ' + @path+'\'+ ltrim(rtrim(@col))+' '+ltrim(rtrim(@col1))+''
print @ssql

Exec xp_cmdshell @ssql
delete  from #files  where @col =name
set @col = ''
End


------------file name be like dbo.Test2.Table
-------output of result dbo.Test2

1 comment:

  1. If you're trying to lose pounds then you absolutely need to get on this totally brand new tailor-made keto meal plan diet.

    To create this keto diet, certified nutritionists, personal trainers, and professional cooks have united to develop keto meal plans that are efficient, painless, cost-efficient, and enjoyable.

    From their grand opening in January 2019, 100's of people have already remodeled their figure and health with the benefits a smart keto meal plan diet can give.

    Speaking of benefits: clicking this link, you'll discover 8 scientifically-certified ones given by the keto meal plan diet.

    ReplyDelete