SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot table question


Pivot table question

Author
Message
john-902052
john-902052
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 145
All,
I am trying to create a pivot table like the following:
Offense ID IncidentID OffenseTypeID etc...
[all offense Id rows] [all IncidentID rows] [all Offense TypeID rows]

using the following table.

USE [master]
GO
/****** Object: Database [Lenod] Script Date: 5/9/2013 4:41:43 PM ******/
CREATE DATABASE [Lenod] ON PRIMARY
( NAME = N'Lenod', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Lenod.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Lenod_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Lenod_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Lenod] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Lenod].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Lenod] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Lenod] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Lenod] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Lenod] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Lenod] SET ARITHABORT OFF
GO
ALTER DATABASE [Lenod] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Lenod] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Lenod] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Lenod] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Lenod] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Lenod] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Lenod] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Lenod] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Lenod] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Lenod] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Lenod] SET DISABLE_BROKER
GO
ALTER DATABASE [Lenod] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Lenod] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Lenod] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Lenod] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Lenod] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Lenod] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Lenod] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Lenod] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Lenod] SET MULTI_USER
GO
ALTER DATABASE [Lenod] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Lenod] SET DB_CHAINING OFF
GO
USE [Lenod]
GO
/****** Object: StoredProcedure [dbo].[generateSP] Script Date: 5/9/2013 4:41:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[generateSP] @DatabaseName varchar(100) = 'Sentinel_Data' , @tableName varchar(100)
, @PrintOrExecute varchar(50) = 'Execute'
As

-- Do we want to generate the SP definitions for every user defined
-- table in the database or just a single specified table?
-- Assign a blank string - '' for all tables or the table name for
-- a single table.
DECLARE @GenerateProcsFor varchar(100)
SET @GenerateProcsFor = @tableName
--SET @GenerateProcsFor = ''



-- do we want the script to print out the CREATE PROC statements
-- or do we want to execute them to actually create the procs?
-- Assign a value of either 'Print' or 'Execute'
SET @PrintOrExecute = @PrintOrExecute


-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix varchar(10)
SET @TablePrefix = 'tbl_'

-- For our '_lst' and '_sel' procedures do we want to
-- do SELECT * or SELECT [ColumnName,]...
-- Assign a value of either 1 or 0
DECLARE @UseSelectWildCard bit
SET @UseSelectWildCard = 0

-- ##########################################################
/* END SETTING OF CONFIG VARIABLE
-- do not edit below this line */
-- ##########################################################


-- DECLARE CURSOR containing all columns from user defined tables
-- in the database
DECLARE TableCol Cursor FOR
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

-- Declare variables which will hold values from cursor rows
DECLARE @TableSchema varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int

DECLARE @ColumnNameCleaned varchar(100)

-- Declare variables which will track what table we are
-- creating Stored Procs for
DECLARE @CurrentTable varchar(100)
DECLARE @FirstTable bit
DECLARE @FirstColumnName varchar(100)
DECLARE @FirstColumnDataType varchar(30)
DECLARE @ObjectName varchar(100) -- this is the tablename with the
-- specified tableprefix lopped off.
DECLARE @TablePrefixLength int

-- init vars
SET @CurrentTable = ''
SET @FirstTable = 1
SET @TablePrefixLength = Len(@TablePrefix)

-- Declare variables which will hold the queries we are building use unicode
-- data types so that can execute using sp_ExecuteSQL
DECLARE @LIST nvarchar(4000), @UPSERT nvarchar(4000)
DECLARE @SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES varchar(4000)
DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000)


-- open the cursor
OPEN TableCol

-- get the first row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength

-- loop through the rows of the cursor
WHILE @@FETCH_STATUS = 0 BEGIN

SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')

-- is this a new table?
IF @TableName <> @CurrentTable BEGIN

-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

-- first add any syntax to end the statement

-- _lst
SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @LIST = @LIST + Char(13)

-- _sel
SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @SELECT = @SELECT + Char(13)


-- UPDATE (remove trailing comma and append the WHERE clause)
SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

-- INSERT
SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
SET @INSERT = @INSERT + @INSERTVALUES

-- _ups
SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
END ELSE BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13)
END
SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
SET @UPSERT = @UPSERT + Char(9) + 'SELECT * from ' + @CurrentTable + ' WHERE [' + @FirstColumnName + '] = scope_identity() ' + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13)
SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + Char(13)

-- _del
-- delete proc completed already

-- --------------------------------------------------
-- now either print the SP definitions or
-- execute the statements to create the procs
-- --------------------------------------------------
IF @PrintOrExecute <> 'Execute' BEGIN
-- PRINT @LIST
-- PRINT @SELECT
PRINT @UPSERT
PRINT @DELETE
END ELSE BEGIN
-- EXEC sp_Executesql @LIST
-- EXEC sp_Executesql @SELECT
EXEC sp_Executesql @UPSERT
EXEC sp_Executesql @DELETE
END
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END

-- update the value held in @CurrentTable
SET @CurrentTable = @TableName
SET @FirstColumnName = @ColumnName
SET @FirstColumnDataType = @DataType

IF @TablePrefixLength > 0 BEGIN
IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN
--PRINT Char(13) + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
END ELSE BEGIN
SET @ObjectName = @CurrentTable
END
END ELSE BEGIN
SET @ObjectName = @CurrentTable
END

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

-- ----------------------------------------------------
-- now start building the procedures for the next table
-- ----------------------------------------------------

-- _lst
SET @LIST = 'CREATE PROC [dbo].[usp_' + @ObjectName + '_lst]' + Char(13)
SET @LIST = @LIST + 'AS' + Char(13)
SET @LIST = @LIST + 'SET NOCOUNT ON' + Char(13)
IF @UseSelectWildcard = 1 BEGIN
SET @LIST = @LIST + Char(13) + 'SELECT * '
END
ELSE BEGIN
SET @LIST = @LIST + Char(13) + 'SELECT [' + @ColumnName + ']'
END

-- _sel
SET @SELECT = 'CREATE PROC [dbo].[usp_' + @ObjectName + '_sel]' + Char(13)
SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'
END
SET @SELECT = @SELECT + Char(13) + 'AS' + Char(13)
SET @SELECT = @SELECT + 'SET NOCOUNT ON' + Char(13)
IF @UseSelectWildcard = 1 BEGIN
SET @SELECT = @SELECT + Char(13) + 'SELECT * '
END
ELSE BEGIN
SET @SELECT = @SELECT + Char(13) + 'SELECT [' + @ColumnName + ']'
END

-- _ups
SET @UPSERT = 'CREATE PROC [dbo].[usp_' + @ObjectName + '_ups]' + Char(13)
SET @UPSERT = @UPSERT + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'
END

-- UPDATE
SET @UPDATE = Char(9) + 'UPDATE [' + @TableName + '] SET ' + Char(13)

-- INSERT -- don't add first column to insert if it is an
-- integer (assume autonumber)
SET @INSERT = Char(9) + 'INSERT INTO [' + @TableName + '] (' + Char(13)
SET @INSERTVALUES = Char(9) + 'VALUES (' + Char(13)

IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')
BEGIN
SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
END

-- _del
SET @DELETE = 'CREATE PROC [dbo].[usp_' + @ObjectName + '_del]' + Char(13)
SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'
END
SET @DELETE = @DELETE + Char(13) + 'AS' + Char(13)
SET @DELETE = @DELETE + 'SET NOCOUNT ON' + Char(13) + Char(13)
SET @DELETE = @DELETE + 'DELETE FROM [' + @TableName + ']' + Char(13)
SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + Char(13)
SET @DELETE = @DELETE + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @DELETE = @DELETE + Char(13)

END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END
ELSE BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

-- is the same table as the last row of the cursor
-- just append the column

-- _lst
IF @UseSelectWildCard = 0 BEGIN
SET @LIST = @LIST + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
END

-- _sel
IF @UseSelectWildCard = 0 BEGIN
SET @SELECT = @SELECT + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
END

-- _ups
SET @UPSERT = @UPSERT + ',' + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'
END

-- UPDATE
SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + Char(13)

-- INSERT
SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)

-- _del
-- delete proc completed already
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'
END

-- fetch next row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
END

-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

-- ------------------------------------------------
-- repeat the block of code from within the cursor
-- So that the last table has its procs completed
-- and printed / executed
-- ------------------------------------------------

-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

-- first add any syntax to end the statement

-- _lst
SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @LIST = @LIST + Char(13)

-- _sel
SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
SET @SELECT = @SELECT + Char(13)


-- UPDATE (remove trailing comma and append the WHERE clause)
SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

-- INSERT
SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
SET @INSERT = @INSERT + @INSERTVALUES

-- _ups
SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
END ELSE BEGIN
SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13)
END
SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13)
SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13)
SET @UPSERT = @UPSERT + Char(13)

-- _del
-- delete proc completed already

-- --------------------------------------------------
-- now either print the SP definitions or
-- execute the statements to create the procs
-- --------------------------------------------------
IF @PrintOrExecute <> 'Execute' BEGIN
PRINT @LIST
PRINT @SELECT
PRINT @UPSERT
PRINT @DELETE
END ELSE BEGIN
EXEC sp_Executesql @LIST
EXEC sp_Executesql @SELECT
EXEC sp_Executesql @UPSERT
EXEC sp_Executesql @DELETE
END
END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END

GO
/****** Object: StoredProcedure [dbo].[usp_TableToClass] Script Date: 5/9/2013 4:41:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TableToClass]


@table_name SYSNAME
--,@nameSpace nvarchar(30)

AS

SET NOCOUNT ON

declare @nameSpace nvarchar(100)
set @nameSpace = '';

if @nameSpace = ''
BEGIN
RAISERROR (N'You have to hard code the name space in the stored procedure.', 10, 1, NULL, NULL)

END
ELSE
BEGIN

DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp
SELECT -10, 'using System;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -9, 'using System.Collections.Generic;' + CHAR(13) + CHAR(10)


INSERT INTO @temp
SELECT -8, 'using System.Text;' ++ CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -7, 'using System.Data.SqlClient;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -6, 'using System.Data.Common;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -5, CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -4, 'namespace ' + @nameSpace + ' ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -3, '{' + CHAR(13) + CHAR(10)


INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'


INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string ' + '_' + COLUMN_NAME + ' = string.Empty ;'
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '+ '_' + COLUMN_NAME + ' = 0 ;'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '+ '_' + COLUMN_NAME + ' = DateTime.Now ;'
WHEN DATA_TYPE LIKE '%MONEY%' THEN 'double '+ '_' + COLUMN_NAME + ' = 0.0;'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '+ '_' + COLUMN_NAME + ' ;'
WHEN DATA_TYPE = 'BIT' THEN 'bool '+ '_' + COLUMN_NAME + ' = false ;'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '+ '_' + COLUMN_NAME + ' = string.Empty ;'
WHEN DATA_TYPE LIKE '%DECIMAL%' THEN 'double '+ '_' + COLUMN_NAME + ' = 0.0;'
ELSE 'object '
END + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%MONEY%' THEN 'double '
WHEN DATA_TYPE LIKE '%DECIMAL%' THEN 'double '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion'
INSERT INTO @temp
select 11 , '}'

INSERT INTO @temp
select 12 , '}'

SELECT code FROM @temp
ORDER BY sort
END
GO
/****** Object: StoredProcedure [dbo].[usp_TableToDAL] Script Date: 5/9/2013 4:41:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TableToDAL]


@table_name SYSNAME
--,@nameSpace nvarchar(30)

AS

SET NOCOUNT ON

declare @nameSpace nvarchar(100)
set @nameSpace = '';

if @nameSpace = 'com.john.test'
BEGIN
RAISERROR (N'You have to hard code the name space in the stored procedure.', 10, 1, NULL, NULL)

END
ELSE
BEGIN

DECLARE @temp TABLE
(
sort INT,
code TEXT
)
Declare @totFields int = (Select count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name)
INSERT INTO @temp
SELECT -10, 'using System;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -9, 'using System.Collections.Generic;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -8, 'using System.Text;' ++ CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -7, 'using System.Data.SqlClient;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -6, 'using System.Data.Common;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -5, CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -4, 'namespace ' + @nameSpace + ' ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT -3, '{' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + 'DA' + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(9) + 'public static Boolean Save(' + convert(varchar,@table_name) + ', objItem)'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'

INSERT INTO @temp
SELECT 3, CHAR(9) + CHAR(9) + 'int result = 0' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 4, CHAR(9) + CHAR(9) + 'try {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 5, CHAR(9) + CHAR(9) + CHAR(9) + 'SqlParameter[] sqlParams = new SqlParameter[' + convert(nvarchar,@totFields) + '];' + CHAR(13) + CHAR(10)
INSERT INTO @temp

--Get the fields from the DB & Generate SAVE
SELECT 6, CHAR(9) + CHAR(9) + CHAR(9) + 'sqlParams[] = new SqlParameter("' + COLUMN_NAME + '" , objItem.' + COLUMN_NAME + ');'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, CHAR(9) + CHAR(9) + CHAR(9)
+ 'result Convert.ToInt32(DAL.DALBase.ExecuteScalar(DAL.DALBase.CMCompanyConnection, CommandType.StoredProcedure, "usp_' + @table_name + '_upd", sqlParams));'
+ CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, CHAR(9) + CHAR(9) + CHAR(9) + 'return result' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 9, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 10, CHAR(9) + CHAR(9) + 'catch (Exception Ex) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 11, CHAR(9) + CHAR(9) + CHAR(9) + 'return result' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 12, CHAR(9) + CHAR(9) + CHAR(9) + 'throw Ex' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 13, CHAR(9) + CHAR(9) + + '}' + CHAR(13) + CHAR(10)

-- Generate DELETE
INSERT INTO @temp
SELECT 200, CHAR(9) + 'public static HARDDELETE(int objItemID) {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 210, CHAR(9) + CHAR(9) + 'string sqlString = "Delete from ' + @table_name + ' where ' + @table_name + 'ID = objItemID";' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 220, CHAR(9) + CHAR(9) + 'int result = 0;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 230, CHAR(9) + CHAR(9) + 'try {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 240, CHAR(9) + CHAR(9) + CHAR(9) + 'result = DAL.DALBase.ExecuteNonQuery(DAL.DALBase.CMCompanyConnection, CommandType.Text, sqlString' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 250, CHAR(9) + CHAR(9) + CHAR(9) + 'return true;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 260, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 270, CHAR(9) + CHAR(9) + 'catch (Execption Ex) {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 280, CHAR(9) + CHAR(9) + CHAR(9) + 'return false;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 290, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 295, CHAR(9) + '}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 300, CHAR(9) + 'public static SOFTDELETE(int objItemID) {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 310, CHAR(9) + CHAR(9) + 'string sqlString = "UPDATE ' + @table_name + ' SET IsDeleted = "true" WHERE ' + @table_name + 'ID = objItemID";' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 320, CHAR(9) + CHAR(9) + 'int result = 0;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 330, CHAR(9) + CHAR(9) + 'try {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 340, CHAR(9) + CHAR(9) + CHAR(9) + 'result = DAL.DALBase.ExecuteNonQuery(DAL.DALBase.CMCompanyConnection, CommandType.Text, sqlString);' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 350, CHAR(9) + CHAR(9) + CHAR(9) + 'return true;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 360, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 370, CHAR(9) + CHAR(9) + 'catch (Execption Ex) {' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 380, CHAR(9) + CHAR(9) + CHAR(9) + 'return false;' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 390, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 395, CHAR(9) + '}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

-- Get query results as Object
INSERT INTO @temp
SELECT 400, CHAR(9) + 'public static GetAsObject(SQLDataReader rdr) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 410, CHAR(9) + CHAR(9) + @table_name + ' objItem = new ' + @table_name + ';' +CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 420, CHAR(9) + CHAR(9) + 'DateTime nullDate = Convert.ToDateTime("01/01/01");' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 430, CHAR(9) + CHAR(9) +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%MONEY%' THEN 'double '
WHEN DATA_TYPE LIKE '%DECIMAL%' THEN 'double '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END
+ COLUMN_NAME + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) + '= Convert.IsDBNull(rdr["' + COLUMN_NAME + '"]) ? ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string.Empty : string.Empty;'
WHEN DATA_TYPE LIKE '%INT%' THEN '0 : Convert.ToInt32(rdr["' + COLUMN_NAME + '"]);'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'Convert.ToDateTime("01/01/01") : Convert.ToDateTime(rdr["' + COLUMN_NAME + '"]);'
WHEN DATA_TYPE LIKE '%MONEY%' THEN '0.00 : Convert.ToDouble(rdr["' + COLUMN_NAME + '"]);'
WHEN DATA_TYPE LIKE '%DECIMAL%' THEN '0.00 : Convert.ToDouble(rdr["' + COLUMN_NAME + '"]);'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'null : null;'
WHEN DATA_TYPE = 'BIT' THEN 'false : Convert.ToBoolean(rdr["' + COLUMN_NAME + '"]);'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string.Empty : string.Empty;'
ELSE 'object '
END
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

-- Get DB Result as Generic List of Objects
INSERT INTO @temp
SELECT 499, CHAR(9) + '}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 500, CHAR(9) + 'public static List<' + @table_name + '> GetAsList(int objID) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 510, CHAR(9) + CHAR(9) + 'string sqlString = ""; ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 520, CHAR(9) + CHAR(9) + 'List<' + @table_name + '> lstItems = new List<' + @table_name + '>(); ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 530, CHAR(9) + CHAR(9) + 'SqlDataReader rdr = DAL.DALBase.ExecuteReader(DAL.DALBase.CMCompanyConnection, CommandType.Text, sqlString);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 540, CHAR(9) + CHAR(9) + 'while(rdr.Read()) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 550, CHAR(9) + CHAR(9) + CHAR(9) + @table_name + ' objItem = GetAsObject(rdr);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 560, CHAR(9) + CHAR(9) + CHAR(9) + 'lstItems.Add(objItem);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 570, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 580, CHAR(9) + CHAR(9) + CHAR(9) + 'rdr.Close();' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 590, CHAR(9) + CHAR(9) + 'return lstItems;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
select 599 , '}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

-- Get DB Result as Sortable Binding List of Objects
INSERT INTO @temp
SELECT 600, CHAR(9) + 'public static SortableBindingList<' + @table_name + '> GetAsSBList(int objID) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 610, CHAR(9) + CHAR(9) + 'string sqlString = ""; ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 620, CHAR(9) + CHAR(9) + 'SortableBindingList<' + @table_name + '> lstItems = new List<' + @table_name + '>(); ' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 630, CHAR(9) + CHAR(9) + 'SqlDataReader rdr = DAL.DALBase.ExecuteReader(DAL.DALBase.CMCompanyConnection, CommandType.Text, sqlString);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 640, CHAR(9) + CHAR(9) + 'while(rdr.Read()) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 650, CHAR(9) + CHAR(9) + CHAR(9) + @table_name + ' objItem = GetAsObject(rdr);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 660, CHAR(9) + CHAR(9) + CHAR(9) + 'lstItems.Add(objItem);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 670, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 680, CHAR(9) + CHAR(9) + 'rdr.Close();' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 690, CHAR(9) + CHAR(9) + 'return lstItems;' + CHAR(13) + CHAR(10)

INSERT INTO @temp
select 699 , CHAR(9) + '}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

-- Get DB Result as DataTable
INSERT INTO @temp
SELECT 700, CHAR(9) + 'public static DataTable GetAsDataTable(int objID) {' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 710, CHAR(9) + CHAR(9) + 'DataTable dt' + @table_name + ' = new DataTable();' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 720, CHAR(9) + CHAR(9) + 'SqlParameter[] sqlParams = new SqlParameter[1];' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 730, CHAR(9) + CHAR(9) + 'sqlParams[0] = new SqlParameter("' + @table_name +'"ID, objID);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 740, CHAR(9) + CHAR(9) + 'SqlDataReader drTemp = DAL.DALBase.ExecuteReader(DAL.DALBase.CMCompanyConnection, CommandType.StoredProcedure, "", sqlParams);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 750, CHAR(9) + CHAR(9) + 'dt' + @table_name + '.Load(drTemp);' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 760, CHAR(9) + CHAR(9) +'drTemp.Close();' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 770, CHAR(9) + CHAR(9) + 'return dt' + @table_name + ';' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 799, CHAR(9) + CHAR(9) + '}' + CHAR(13) + CHAR(10)
--

INSERT INTO @temp
select 900 , '}' + CHAR(13) + CHAR(10)

INSERT INTO @temp
select 910, '}' + CHAR(13) + CHAR(10)

SELECT code FROM @temp
ORDER BY sort
END
GO
/****** Object: Table [dbo].[workingTable] Script Date: 5/9/2013 4:41:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workingTable](
[PrimaryKey] [nvarchar](50) NULL,
[FieldName] [nvarchar](50) NULL,
[oldvalue] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'PrimaryKey', N'FieldName', N'OldValue')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[OffenseID]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[IncidentID]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[OffenseTypeID]', N'21')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[IsCrime]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[WeaponType]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[LACOE_AuditUserID]', N'3')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[LACOE_AuditDate]', N'Sep 28 2009 10:10AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'1', N'[LACOE_AuditIP]', N'127.0.0.1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[OffenseID]', N'100848')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[IncidentID]', N'45988')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[OffenseTypeID]', N'52')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[LACOE_AuditUserID]', N'113')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[LACOE_AuditDate]', N'Oct 24 2012 10:40AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100848', N'[LACOE_AuditIP]', N'205.154.3.139')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[LACOE_AuditIP]', N'205.154.3.139')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[LACOE_AuditDate]', N'Oct 24 2012 10:40AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[LACOE_AuditUserID]', N'113')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[IsMostSevere]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[OffenseTypeID]', N'47')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[IncidentID]', N'45988')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100849', N'[OffenseID]', N'100849')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[OffenseID]', N'100850')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[IncidentID]', N'45988')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[OffenseTypeID]', N'20')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[LACOE_AuditUserID]', N'113')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[LACOE_AuditDate]', N'Oct 24 2012 10:40AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100850', N'[LACOE_AuditIP]', N'205.154.3.139')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[LACOE_AuditDate]', N'Oct 24 2012 10:42AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[LACOE_AuditIP]', N'205.154.3.139')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[LACOE_AuditUserID]', N'113')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[OffenseTypeID]', N'22')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[IncidentID]', N'45988')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100851', N'[OffenseID]', N'100851')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[OffenseID]', N'100852')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[IncidentID]', N'45988')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[OffenseTypeID]', N'21')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[LACOE_AuditUserID]', N'113')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[LACOE_AuditIP]', N'205.154.3.139')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'100852', N'[LACOE_AuditDate]', N'Oct 24 2012 10:42AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[OffenseID]', N'102996')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[IncidentID]', N'46862')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[OffenseTypeID]', N'31')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[LACOE_AuditUserID]', N'304')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[LACOE_AuditDate]', N'Nov 13 2012 1:59PM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'102996', N'[LACOE_AuditIP]', N'205.154.4.16')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[OffenseID]', N'103556')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[IncidentID]', N'47085')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[OffenseTypeID]', N'52')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[IsMostSevere]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[LACOE_AuditUserID]', N'1617')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[LACOE_AuditDate]', N'Nov 20 2012 10:40AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103556', N'[LACOE_AuditIP]', N'205.154.3.72')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[LACOE_AuditDate]', N'Nov 20 2012 10:45AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[LACOE_AuditIP]', N'205.154.3.72')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[LACOE_AuditUserID]', N'1617')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[IncidentID]', N'47085')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[OffenseTypeID]', N'20')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103557', N'[OffenseID]', N'103557')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[OffenseID]', N'103558')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[IncidentID]', N'47085')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[OffenseTypeID]', N'35')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[LACOE_AuditDate]', N'Nov 20 2012 11:00AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[LACOE_AuditUserID]', N'1617')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'103558', N'[LACOE_AuditIP]', N'205.154.3.72')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[OffenseID]', N'109162')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[IncidentID]', N'49279')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[OffenseTypeID]', N'47')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[IsMostSevere]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[LACOE_AuditUserID]', N'1776')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[LACOE_AuditDate]', N'Jan 17 2013 9:17AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109162', N'[LACOE_AuditIP]', N'205.154.3.147')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[LACOE_AuditIP]', N'205.154.3.147')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[LACOE_AuditDate]', N'Jan 17 2013 9:18AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[LACOE_AuditUserID]', N'1776')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[OffenseTypeID]', N'51')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[IncidentID]', N'49279')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109163', N'[OffenseID]', N'109163')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[OffenseID]', N'109164')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[IncidentID]', N'49279')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[OffenseTypeID]', N'27')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[LACOE_AuditUserID]', N'1776')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[LACOE_AuditDate]', N'Jan 17 2013 9:19AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109164', N'[LACOE_AuditIP]', N'205.154.3.147')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[LACOE_AuditDate]', N'Jan 17 2013 9:19AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[LACOE_AuditUserID]', N'1376')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[LACOE_AuditIP]', N'205.154.2.233')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[IsCrime]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[IncidentID]', N'49280')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[OffenseTypeID]', N'20')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109165', N'[OffenseID]', N'109165')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[OffenseID]', N'109166')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[OffenseTypeID]', N'52')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[IncidentID]', N'49280')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[IsMostSevere]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[LACOE_AuditIP]', N'205.154.2.233')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[LACOE_AuditUserID]', N'1376')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109166', N'[LACOE_AuditDate]', N'Jan 17 2013 9:19AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[LACOE_AuditDate]', N'Jan 17 2013 9:25AM')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[LACOE_AuditUserID]', N'952')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[SuspectsKnown]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[LACOE_AuditIP]', N'205.154.4.51')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[WeaponType]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[LossToDistrictCOE]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[LossToPersonal]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[EstDollarLoss]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[HateMotivationID]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[HateMotivated]', NULL)
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[IsMostSevere]', N'0')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[IsCrime]', N'1')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[OffenseTypeID]', N'52')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[IncidentID]', N'49281')
GO
INSERT [dbo].[workingTable] ([PrimaryKey], [FieldName], [oldvalue]) VALUES (N'109167', N'[OffenseID]', N'109167')
GO
USE [master]
GO
ALTER DATABASE [Lenod] SET READ_WRITE
GO


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search