June 9, 2003 at 8:57 pm
Hello. I was wondering if anyone out there can help me out. I'm pretty new to all this so I'm not sure it can be done.
Let's say I have 6 Word documents (Worddoc1.doc, Worddoc2.doc, Worddoc3.doc........) that have been uploaded into a SQL Server 2000 table. The name of the table is "Peter". I was wondering if there is a way I can rename all of the Word files with some code or script by appending the table name to the file name of the Word document. This would result in: "Peter Worddoc1.doc, Peter Worddoc2.doc, Peter Worddoc3.doc.......). And if so, how can I do this against 1 table and against 100 tables at a time (if possible). Thanks in advance.
June 10, 2003 at 7:56 am
Hello wongj5,
quote:
Let's say I have 6 Word documents (Worddoc1.doc, Worddoc2.doc, Worddoc3.doc........) that have been uploaded into a SQL Server 2000 table. The name of the table is "Peter". I was wondering if there is a way I can rename all of the Word files with some code or script by appending the table name to the file name of the Word document. This would result in: "Peter Worddoc1.doc, Peter Worddoc2.doc, Peter Worddoc3.doc.......). And if so, how can I do this against 1 table and against 100 tables at a time (if possible). Thanks in advance.
UPDATE table SET filename = 'Peter ' + filename
should work on one table. If you place this in a stored proc, you can use the dynamic sql approach to run this against 100 tables.
Your proc should take the table name as an input parameter and look something like this:
DECLARE @stmt varchar(1000)
SET @stmt = 'UPDATE ' + @tablename + ' SET .....'
exec sp_executeSQL @stmt
go
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 10, 2003 at 8:05 am
If your 100 tables have similar names, e.g. all start with peter (peter1,peter2 etc). Then you could use this
sp_MSforeachtable @command1="update ? set doc = replace(replace('?','[dbo].[',''),']','')+' '+doc",@whereand = "and name like 'peter%'"
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply