SET NOCOUNT ON;
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tempTestTable')
BEGIN
DROP TABLE tempTestTable
END
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tempColumnNames')
BEGIN
DROP TABLE tempColumnNames
END
CREATE TABLE tempColumnNames (
CoumnName VARCHAR(2000)
)
DECLARE @ItemId INT
DECLARE @sql NVARCHAR(MAX) ,
@sqlSelect NVARCHAR(MAX) ,
@ColumnName AS VARCHAR(2000)
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT TOP 1 ItemId FROM eTouchs.tempReportDump
ORDER BY 1
OPEN c1
FETCH NEXT FROM c1 INTO @ItemId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'Create Table tempTestTable (ItemId BIGINT, '
SET @sqlSelect = 'SELECT ItemId, '
--print @ItemId
DECLARE c2 CURSOR Forward_only Read_only FOR
SELECT KeyName FROM eTouchs.tempReportDump
WHERE ItemId = @ItemId
ORDER BY KeyName
OPEN c2
FETCH NEXT FROM c2 INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + @columnName + N' VARCHAR(MAX) ,'
INSERT INTO tempColumnNames VALUES ( @ColumnName )
SET @sqlSelect = @sqlSelect + @ColumnName + ', '
FETCH NEXT FROM c2 INTO @ColumnName
END
CLOSE c2
DEALLOCATE c2
SET @sql = SUBSTRING(@sql,1,LEN(@sql) -1) + N')'
SET @sqlSelect = SUBSTRING (@sqlSelect ,1,LEN(@sqlSelect) -1 ) + N' FROM eTouchs.tempReportDump Order By ItemId'
FETCH NEXT FROM c1 INTO @ItemId
END
CLOSE c1
DEALLOCATE c1
--Creates the temp table to hold results
EXECUTE sp_executesql @sql
--Now insert a Qnique set of itemIds
INSERT INTO tempTestTable (ItemId) SELECT DISTINCT ItemId FROM eTouchs.tempReportDump
--Now we update
DECLARE @updateSQL NVARCHAR(MAX) ,
@keyVal NVARCHAR(4000)
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT ItemId FROM eTouchs.tempReportDump
OPEN c1
FETCH NEXT FROM c1 INTO @ItemId
WHILE @@FETCH_STATUS = 0
BEGIN
--Now we need to build up an update statement
DECLARE c2 CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT CoumnName FROM tempColumnNames
OPEN c2
FETCH NEXT FROM c2 INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @keyVal = KeyValue FROM eTouchs.tempReportDump WHERE ItemId = @ItemId AND KeyName = @ColumnName
SET @updateSQL = 'UPDATE tempTestTable Set '+ @columnName + ' = ''' + @keyVal + ''' WHERE ItemId = ' + CONVERT(NVARCHAR(100),@itemId)
BEGIN TRY
BEGIN
EXECUTE sp_executesql @updateSQL
END
END TRY
BEGIN CATCH
BEGIN
PRINT @updateSQL
PRINT @@ERROR
END
END CATCH
FETCH NEXT FROM c2 INTO @columnName
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @ItemId
END
CLOSE c1
DEALLOCATE c1
