SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 dynamically script data (helpful sproc)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nic
Posting Yak Master

209 Posts

Posted - 12/03/2008 :  13:23:22  Show Profile  Reply with Quote
Hi,
The purpose of this stored procedure is to dynamically create insert statements for every record in a table. This is useful if you need to transfer data from one database to another and there is no direct communication between the two servers. The sproc takes 2 parameters: tableName and ExcludeIdentity. The output is a series of insert statements. One for each record in the table.

Here is some history: along time ago I recieved some help from "Nr" to dynamically create an insert statement based on data in a table. Since the origin of the script came from sqlteam, I'd like to provide an enhancement.

Here is the original post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26891

Here is the updated sproc (handles additional data types, identities etc)



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




Thanks,

Nic

Edited by - nic on 12/04/2008 11:50:46
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000