T-SQL Utility

Here's a handly little routine that will generate INSERT statements from the data in a supplied table.  I wrote this a long time ago and don't often need to use it these days, but when security restricts me from doing any sort of actual table export/import between servers yet that's what I need to do, this is extremely useful.

DECLARE @ColName varchar (100) 
DECLARE @colType varchar (100) 
DECLARE @Output varchar (8000)
DECLARE @TableName varchar(50)

SELECT @TableName = '{table_name_here}'

SELECT @ColName = '', @ColType = '', @Output = ''

DECLARE rs CURSOR FOR
	SELECT c.Name,	t.Name
	FROM
		sysobjects o 
		JOIN syscolumns c ON o.ID = c.ID 
		JOIN systypes t	ON c.xType = t.xType
	WHERE
		o.Name = @TableName 
	
OPEN rs 

FETCH NEXT FROM	rs INTO @ColName,@ColType 

--build the field listing...
IF  @@Fetch_Status = 0
SET	@outPut = 'SELECT ''INSERT INTO ' + @TableName + ' (' 

WHILE @@Fetch_Status = 0
BEGIN
SELECT @output = @output + char ( 10 ) + '	' + @colname + ', ' 
	
FETCH NEXT FROM	rs INTO @ColName, @ColType
END
CLOSE rs 

--remove the trailing ', '
SELECT @output = Left(@output, Len(@output) - 1) 

--now build the values list
OPEN rs 
FETCH NEXT FROM rs INTO @ColName, @ColType 

--build the field listing...

IF @@Fetch_Status = 0
SET	@outPut = @output + char(10) + ') VALUES (' 

WHILE @@Fetch_Status = 0
BEGIN
SELECT @output = @output + char ( 10 ) + '	' + 
	CASE
		WHEN  @ColType IN ( 'varchar' , 'char' , 'smalldatetime' , 'timestamp' , 'datetime' , 'nvarchar' , 'nchar' ) 
			THEN ''''''' + replace(ISNULL(' + @ColName + ',''''),'''','''''''') + '''''
		ELSE ''' + convert(varchar,ISNULL(' + @ColName + ',0)) + '
	END

SELECT @output = @output + ''', '
FETCH NEXT FROM rs INTO @ColName, @ColType
END

CLOSE rs 
DEALLOCATE rs 

--remove the trailing  ', '
SELECT @output = Left( @output,Len( @output) - 2)

SELECT @output = @output + ''')''' 
--now we've completed the complicated SELECT clause, just close up the FROM

SELECT @output = @output + Char(10) + 'FROM ' + @TableName

--SELECT @output 
EXEC(@output)