IF EXISTS ( SELECT NAME FROM SYSOBJECTS WHERE NAME='JDTSP_LOAD_ID_TABLES')
DROP PROCEDURE JDTSP_LOAD_ID_TABLES
GO

CREATE PROCEDURE JDTSP_LOAD_ID_TABLES
AS
----------------------------------------------------------
-- USES A LINKED QUERY TO LOAD THE ID TABLES FOR UPGRADE
-- To Match Production ID TABLES to populate remote values
-- and update local tables.  this example looks up tables 
-- that end with _id 
-- USER BEWARE- YOU ARE ON YOUR OWN- NO Warranty
----------------------------------------------------------
SET Quoted_IDENTIFIER OFF --QI will bite you, but a good thing
DECLARE DELETEIDS  Cursor
FOR
Select name from 
OpenQuery(RemoteDb2,'Select Distinct(name) from sysobjects where type=''u'' and name like ''%_id''  ')
order by name
OPEN DELETEIDS
DECLARE @TNAME Varchar(255)

FETCH NEXT FROM DELETEIDS INTO @TNAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
	BEGIN

		SELECT @TNAME = 'DELETE FROM MY_DB..' + @TNAME
		SELECT @TNAME
		exec(@TNAME)
	END

FETCH NEXT FROM DELETEIDS INTO @TNAME
End
CLOSE DELETEIDS
DEALLOCATE DELETEIDS

DECLARE UPDATEIDS  Cursor
FOR
Select name from 
OpenQuery(RemoteDb2,'Select Distinct(name) from sysobjects where type=''u'' and name like ''%_id''  ')
order by name

OPEN UPDATEIDS
DECLARE @TNAME_REMOTE Varchar(255)

FETCH NEXT FROM UPDATEIDS INTO @TNAME_REMOTE
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
	BEGIN
	SELECT @TNAME_REMOTE = "INSERT INTO MY_DB.." +@TNAME_REMOTE + "(SWID) Select * from OpenQuery(RemoteDb2,'Select DISTINCT(swid)+ 250 from "+ @TNAME_REMOTE +"')"
	SELECT @TNAME_REMOTE
	exec(@TNAME_REMOTE)
END

FETCH NEXT FROM UPDATEIDS INTO @TNAME_REMOTE
End
CLOSE UPDATEIDS
DEALLOCATE UPDATEIDS

go