This is my first time posting here, but I've been a fan of the site for some time now. I apologize if I put this posting in the wrong place. So here's the deal.... I'm working on building a home media server with a SQL backend and an ASP frontend that contains all my music, movies and photos. For the music portion, I'm using Windows Indexing Service to gather up all the information for my music directory. Then, using SQL Server 2005, I created a linked server and Inserted all that information into a local database (better performance). This all works fine and dandy, but if I update any of my music (move/change/add) I have to re-build the local database by dropping all the rows and rebuild the table. I would like to avoid that by updating the local table with changes made from windows indexing service. Currently, this is the code I'm using to run the insert from the OPENQUERY localtable.INSERT INTO dbo.DTPmusicSELECT * FROM OPENQUERY(MUSIC, 'Select MusicGenre, MusicTrack, MusicAlbum, MusicYear, MusicArtist, AudioAvgDataRate, AudioTimeLength, fileindex, size, & _ directory, filename, doctitle, attrib, write, access from scope()')
There are plenty of other fields, but these are the relevant ones. And as far as I can tell the FILEINDEX field is the unique Identifier. Any ideas on how I can best accomplish this or any better solutions?Thanks, Dark Matter
Update local table from OPENQUERY
Posted - 2008-12-06 : 13:28:49
