------------------------------------------------------------------
--                                                              --
--  File:   Convert_NI_ITEM_Data.SQL                            --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1022      --
--          Copyright Microsoft  2008, 2009, 2010               --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_OFF ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

---
--- Move the data from the NEWS_ITEM_TEMP table to
--- the final NEWS_ITEM table.  We must do this 
--- since the data generated by EGenLoader is 
--- character based and the column is defined
--- as a binary(LOB) in the TPC-E Specification
--- The CONVERT(varbinary(max),NI_ITEM) AS NI_ITEM
--- statement does this.  All other data is unchanged
---
--- If the NEWS_ITEM_TEMP table does not exist then we
--- can assume that the conversion has already been done
---
IF EXISTS ( SELECT name FROM sys.objects WHERE name = 'NEWS_ITEM_TEMP' )
BEGIN
    INSERT INTO NEWS_ITEM
        SELECT  NI_ID,
                NI_HEADLINE,
                NI_SUMMARY,
                CONVERT(varbinary(max),NI_ITEM),
                NI_DTS,
                NI_SOURCE,
                NI_AUTHOR
        FROM    NEWS_ITEM_TEMP

    --- 
    --- Now that we have the data moved, we can
    --- delete the NEWS_ITEM_TEMP table
    ---
    DROP TABLE NEWS_ITEM_TEMP
END
GO
