CREATE PROCEDURE sp_CreateDataLoadScript
-- this sproc scripts out data from a supplied table into a sql insert script
(
@TblName varchar(128)
,@ExcludeIdentity bit = 0
) 
AS
DECLARE 
@id int 
,@maxid int
,@cmd1 varchar(7000) 
,@cmd2 varchar(7000) 
,@hasIdentity bit
,@exIdentity bit
,@identityOn varchar(200)
,@identityOff varchar(200)

SET NOCOUNT ON

-- if exclude identity is false (default), then make sure all columns are included
-- if exclude identity is true (override), then make sure not to include the identity column
IF @ExcludeIdentity = 0
	SET @exIdentity = null
ELSE
	SET @exIdentity = 0


-- create temp table
CREATE TABLE #a 
(
	id INT identity (1,1)
	,ColType int
	,ColName varchar(128)
) 

-- get schema info and insert into temp table
INSERT #a 
(
	ColType
	,ColName
) 
SELECT
	dt.ColType
	,dt.ColName
FROM
(
	SELECT 
		ORDINAL_POSITION
		,CASE 
			WHEN DATA_TYPE LIKE '%char%' THEN 1 
			WHEN DATA_TYPE LIKE '%date%' THEN 2 
			WHEN DATA_TYPE LIKE '%bit%' THEN 3
			WHEN DATA_TYPE LIKE '%varbinary%' THEN 4
			ELSE 0 
		END AS ColType
		,COLUMN_NAME AS ColName
		,(SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS IsIdentity
	FROM 
		INFORMATION_SCHEMA.COLUMNS
	WHERE 
		TABLE_NAME = @TblName 
)AS dt
WHERE
	dt.IsIdentity = COALESCE(@exIdentity, dt.IsIdentity)  -- if exclude is true return all except for identity column (else return all)
ORDER BY
	dt.ORDINAL_POSITION 

-- check to see if table has identity column
SELECT
	@hasIdentity = CAST(SUM(dt.IsIdentity) AS Bit)
FROM
(
	SELECT 
		(SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS IsIdentity
	FROM 
		INFORMATION_SCHEMA.COLUMNS
	WHERE 
		TABLE_NAME = @TblName
) AS dt
-- if excludeIdentity parameter is true, override identity settings
IF @ExcludeIdentity = 1
	SET @hasIdentity = 0

-- get ordinal positions of table columns
SELECT 
	@id = 0 
	,@maxid = MAX(id) 
FROM
	#a 

SELECT @cmd1 = 'select '' insert ' + @TblName + ' ( ' 
SELECT @cmd2 = ' + '' select '' + ' 
WHILE @id < @maxid 
    BEGIN
	-- get next column id 
	SELECT @id = MIN(id) FROM #a WHERE id > @id 
	-- build column name list
	SELECT 
		@cmd1 = @cmd1 + '[' + ColName + '],' 
	FROM 
		#a 
	WHERE
		id = @id 

	-- build column value list
	SELECT 
		@cmd2 = @cmd2 
		+ ' case when [' + ColName + '] is null ' 
		+ ' then ''null'' ' 
		+ ' else ' 
		+ CASE
			WHEN ColType = 1 THEN ''''''''' + replace([' + ColName + '],'''''''','''''''''''')  + '''''''''  						-- replace single apostrophes with double
			WHEN ColType = 2 THEN ''''''''' + convert(char(9),[' + ColName + '],112) + convert(char(12),[' + ColName+ '],114) + '''''''''  	-- properly format dates
			WHEN ColType = 3 THEN ''''''''' + convert(char(1),[' + ColName + ']) + '''''''''							-- properly format bits/booleans
			WHEN ColType = 4 THEN '''convert(varbinary(100),'' + master.dbo.fn_varbintohexstr([' + ColName + '])  + '')'''  			-- properly handle varbinary data (convert to hexstr and back)
			ELSE ''''''''' + convert(varchar(500),[' + ColName + ']) + '''''''''  									--convert numeric into varchar so can append to @cmd2
		END 
		+ ' end + '','' + ' 
	FROM
		#a  
	WHERE
		id = @id 
    END 

-- clean up
SELECT @cmd1 = LEFT(@cmd1,LEN(@cmd1)-1) + ' ) '' ' 
SELECT @cmd2 = LEFT(@cmd2,LEN(@cmd2)-8) + ' from ' + @tblName 


-- if contains identity column, need to turn identiyInsert on
IF @hasIdentity = 1
    BEGIN
	SET @identityOn = 'select ''SET IDENTITY_INSERT ' + @TblName + ' ON'''
	--PRINT(@identityOn)
	EXEC(@identityOn)
    END

--PRINT(@cmd1 + @cmd2)
EXEC(@cmd1 + @cmd2) 

IF @hasIdentity = 1
    BEGIN
	SET @identityOff = 'select ''SET IDENTITY_INSERT ' + @TblName + ' OFF'''
	--PRINT(@identityOff)
	EXEC(@identityOff)
    END

DROP TABLE #a
SET NOCOUNT OFF
GO