U05y725R

1BEGIN LOOP
2#B = #A Join (Select value From dataTypeTable...)
3Drop Table #A
4#A = #B
5Drop Table #B
6END 
7     
8--*** DECLARATION ***
9USE [DB];
10GO
11
12-- ***************
13-- Target Table Id
14DECLARE @TagSourceID INT = 130;
15-- ***************
16
17-- Result Set - DROP if EXIST
18IF OBJECT_ID(N'#ResultSet') IS NOT NULL DROP TABLE #ResultSet
19CREATE TABLE dbo.#ResultSet (
20    DateTime DATETIME,
21    GroupID VARCHAR(50)
22);
23IF OBJECT_ID(N'#JoinSet') IS NOT NULL DROP TABLE #JoinSet
24CREATE TABLE dbo.#JoinSet (
25    DateTime DATETIME,
26    GroupID VARCHAR(50)
27);
28
29-- Target Fields - Iterator
30DECLARE field_cursor CURSOR FOR
31SELECT Id, Name, RawTableName
32FROM dbo.[Tag]
33WHERE TagSourceId = @TagSourceID
34AND Name NOT IN ('DateTime','GroupId')
35AND TagTypeId NOT IN (6,7)
36ORDER BY Id;
37
38-- Local Variables
39DECLARE @Tag varchar(10), @TagName varchar(10), @RawTableName varchar(50)
40
41OPEN field_cursor;
42
43-- Initial first fetch and store
44FETCH NEXT FROM field_cursor
45INTO @Tag, @TagName, @RawTableName;
46
47IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @RawTableName)
48BEGIN
49    -- Check @@FETCH_STATUS for any other rows to fetch
50    WHILE @@FETCH_STATUS = 0
51    -- Loop through each field/column...
52    BEGIN
53        -- full outer join values from rawtables to ResultSet
54        IF OBJECT_ID(N'#JoinSet') IS NOT NULL DROP TABLE #JoinSet;
55        DECLARE @field nvarchar(500);
56        SET @field = 
57        N'SELECT a.*, b.value AS ' + QuoteName(@TagName) +
58        N'INTO #JoinSet
59          FROM #ResultSet a
60          FULL OUTER JOIN ( 
61            SELECT
62                CONVERT(datetime2, [DateTime], 1) as DateTime,
63                [GroupId],
64                [Value]
65            FROM dbo.' + QuoteName(@RawTableName) +
66            N'WHERE TagId = ' + QuoteName(@Tag) + N'AND
67            IsDeleted=0 ) b
68          ON a.DateTime = b.DateTime AND a.GroupId = b.GroupId
69        ';
70        EXEC sp_executesql @field;
71
72        DROP TABLE #ResultSet;
73        SELECT *
74        INTO #ResultSet
75        FROM #JoinSet --***** THROWS ERROR - #RESULTSET ALREADY EXISTS *****
76
77
78
79        -- Iterate values to next field
80        FETCH NEXT FROM field_cursor
81        INTO @Tag, @TagName, @RawTableName;
82    END
83
84END
85
86CLOSE field_cursor;
87DEALLOCATE field_cursor;
88
89SELECT * FROM #ResultSet;
90DROP TABLE #ResultSet;
91
92GO