n4h96pHR

1BEGIN TRAN
2/**********************************************************************
3*** INSTRUCTIONS: 
4*** 1) Create a Link Server on the server being updated to the server reading from. (If both databases are on the same SQL instance you can omit this step.)
5*** 2) Create the UDL that you will be copying to through the DocuPhase GUI.
6*** 3) Determine the UDL Format(GroupID) from querying the IndexTypes Table. Confirm that you selected the correct UDL by querying the UserDefinedList table with the GroupID.
7*** 4) Update the variables with the correct linkedserver.database name and UDL groupids.
8**********************************************************************/
9
10    DECLARE @maxListID int
11			,@maxSortOrder int
12			,@NewUDL int
13			,@OrigUDL int
14			,@RemoteSource nvarchar(500)
15			,@SQL nvarchar(max)
16
17-----Name of Server.database Reading From    	
18	SET @remotesource = '[isynergylive].[espeed]' 
19-----UDL of server that is being updated
20	SET @NewUDL = 27
21-----UDL of server reading from
22	SET @OrigUDL = 9 
23
24	DECLARE @udltemp TABLE ([ListID] [int] NULL,
25	[GroupID] [int] NULL,
26	[ListItem] [nvarchar](50) NULL,
27	[SortOrder] [int] NULL,
28	[ListItemDisplay] [nvarchar](50) NULL)
29
30
31/**************************     BEFORE CHANGES    ***************************/
32    SELECT * FROM UserDefinedLists WHERE GroupID = @NewUDL
33
34    /***  GETS HIGHEST ListID   ***/
35    SELECT @maxListID = ISNULL(MAX(ListID),0) FROM UserDefinedLists
36/**************************  GETS HIGHEST SORT VALUE FOR GROUP   *****************************/
37    SELECT @maxSortOrder = ISNULL(Max(SortOrder),0) FROM UserDefinedLists WHERE GroupID = @NewUDL
38/**************************  REMOTE DATA COMPARED TO UDL   ***********************************/
39    SET @SQL = '
40        SELECT ROW_NUMBER() OVER(ORDER BY ListItem) + ' + convert(nvarchar(5),@maxListID) + ' ListID,
41            '+ convert(nvarchar(5),@NewUDL) + ' as GroupID,
42            ListItem,
43            ROW_NUMBER() OVER(ORDER BY ListItem) + ' + convert(nvarchar(5),@maxSortOrder) + ' SortOrder,
44            '''' AS ListItemDisplay
45        FROM '+ @remotesource +'.dbo.userdefinedlists Re
46        WHERE NOT EXISTS (SELECT ListItem FROM UserDefinedLists og WHERE og.GroupID = ' + convert(nvarchar(5),@NewUDL) + ' AND re.Listitem = og.ListItem)
47		AND re.groupid = ' + convert(nvarchar(5),@OrigUDL) + '
48        '
49INSERT INTO @udltemp
50EXEC sp_executesql @sql
51/****************  TEMP TABLE DATA  *********************/
52   --SELECT * FROM @udltemp
53/****************  INSERT INTO UDL  *********************/
54    INSERT INTO UserDefinedLists (ListID, GroupID, ListItem, SortOrder, ListItemDisplay)
55    SELECT * FROM @udltemp
56/****************  UDL WITH NEW VALUES  *********************/
57    --SELECT * FROM UserDefinedLists WHERE GroupID = @NewUDL
58/****************  UDPATE ORDER OF UDL *********************/
59    UPDATE udl SET udl.sortorder = t.SortOrder
60    FROM UserDefinedLists udl
61    INNER JOIN (SELECT ROW_NUMBER () OVER(ORDER BY ListItem) SortOrder, ListItem, ListID FROM UserDefinedLists WHERE GroupID = @NewUDL) t ON t.ListID = udl.ListID
62
63
64/****************  COMPLETED INSERT  *********************/
65    SELECT * FROM UserDefinedLists WHERE GroupID = @NewUDL
66	
67ROLLBACK TRAN