May 9, 2013 at 5:48 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply