generate batch file from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

generate batch file from table

Author

Topic

keneo
Starting Member

3 Posts

Posted - 2010-10-28 : 12:05:23

hi,
Here is my table:

Filename Desc1 Desc2
-------------------------
Greeting.txt Top Red
Goodbye.txt Bot Blue
Welcome.txt Sid Pink

My ultmate goal is to generate a DOS batch file that will automatically rename files that are located in the table. The new file name is composed from a combination of static text and fields in the table:
'C:\' + Desc1 + Desc2 + '.txt'

The bat file would look something like this
move "C:\Greeting.txt" "C:\TopRed.txt"
move "C:\Goodbye.txt" "C:\BotBlue.txt"
move "C:\Welcome.txt" "C:\SidPink.txt"

Is there a way to use SQL statements to generate these lines and then either place them into a new table or directly generate a text file?

I am using MS SQL Server Management Studio Express
I was hoping that I could use SQLCMD to do this (but not necessarily).

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 12:50:20

ya mean like


CREATE TABLE myTable99([Filename] varchar(255), Desc1 varchar(50), Desc2 varchar(50))
GO

INSERT INTO myTable99([Filename], Desc1, Desc2)
SELECT 'Greeting.txt', 'Top', 'Red'		UNION ALL
SELECT 'Goodbye.txt',  'Bot', 'Blue'	UNION ALL
SELECT 'Welcome.txt',  'Sid', 'Pink'
GO

SELECT * FROM myTable99
GO

DECLARE @mySQL99 varchar(8000), @cmd varchar(8000)
SET @cmd = 'echo ''REM *** START '' > C:\Test.bat'
PRINT @cmd
--EXEC master..xp_cmdshell @cmd

DECLARE myCursor99 CURSOR
FOR

SELECT 'move c:\' + [Filename] + ' C:\' + Desc1 + Desc2 + '.txt' AS mySQL99
  FROM myTable99

OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @mySQL99

WHILE @@FETCH_STATUS = 0
  BEGIN	
	PRINT @mySQL99
	--EXEC master..xp_cmdshell @mySQL99
	SET @cmd = 'echo '''+@mySQL99+''+''' >>  C:\Test.bat'
	PRINT @cmd
	--EXEC master..xp_cmdshell @cmd
	FETCH NEXT FROM myCursor99 INTO @mySQL99
  END
CLOSE myCursor99
DEALLOCATE myCursor99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources