Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 01/02/2009 at 12:35 AM	SQLServer\Code

This Script was written originally by Tony Webster, and was published from SQL ServerCentral.com 2008/12/29
I've modified the code so far, that it could reside in a tools database and could be executed from there.
For this I've used an addtional parameter which defines the database to act on.


--======================================================================
-- Function to format the output value for inclusion in the VALUES list
--======================================================================
CREATE FUNCTION spVal(@str nvarchar(4000)) RETURNS nvarchar(4000) AS
BEGIN
DECLARE @res nvarchar(4000)
IF @str IS NULL
  SET @res = 'NULL'
ELSE
  SET @res = 'N''' + REPLACE(@str, '''', '''''') + ''''
RETURN(@res)
END

GO

--======================================================================
-- Function for the special case of formatting the output value for
-- inclusion in the VALUES list
--======================================================================
CREATE FUNCTION spMVal(@val money) RETURNS nvarchar(4000) AS
BEGIN
DECLARE @res nvarchar(4000)
IF @val IS NULL
  SET @res = 'NULL'
ELSE
  SET @res = CONVERT(varchar(20), @val, 2)
RETURN(@res)
END

GO
--======================================================================
-- Create a script for inserting data into the specified table, based
-- on the optional condition
--
-- i.e
-- EXEC [dbo].[spScriptInsert]
-- @database = N'HTS',
-- @table = N'PrPricing'
--======================================================================
CREATE PROC spScriptInsert(
@database sysname,
@table varchar(80), 
@condition varchar(200) = '1=1') 
AS
DECLARE @fields nvarchar(4000)
, @values nvarchar(4000)
, @SQL nvarchar(4000)
, @fieldname nvarchar(128)
, @colorder int
, @type varchar(40)

if object_id('tempdb..##fields') is not null 
DROP table ##fields

SET @SQL = 'SELECT top 100 colorder, syscolumns.name, systypes.name as type
INTO ##fields
FROM ' + @database + '.dbo.syscolumns
JOIN ' + @database + '.dbo.sysobjects  ON sysobjects.id      = syscolumns.id
JOIN ' + @database + '.dbo.systypes    ON systypes.xusertype = syscolumns.xusertype
WHERE sysobjects.name = ''' + @table + ''' and systypes.name <> ''text''
ORDER BY colorder'

EXEC sp_executeSQL @sql

DECLARE fieldscursor CURSOR FOR
SELECT [colorder], [name], [type]
FROM ##fields
ORDER BY colorder

OPEN fieldscursor

FETCH NEXT FROM fieldscursor INTO @colorder, @fieldname, @type

SET @fields = ''
SET @values = ''

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fields = @fields + ',[' + @fieldname + ']'
IF @type = 'money'
-- Special case for "money" type
SET @values = @values + '+'',''+dbo.spMVal([' + @fieldname + '])'   
ELSE
SET @values = @values + '+'',''+dbo.spVal(['  + @fieldname + '])'

FETCH NEXT FROM fieldscursor INTO @colorder, @fieldname, @type
END

DEALLOCATE fieldscursor

SET @SQL = 'SELECT ''INSERT INTO ' + @table + '(' + 
SUBSTRING(@fields, 2, 2000) + 
  ') VALUES (''+ ' + 
  SUBSTRING(@values, 6, 1000) + '+'')'' FROM ' + 
  @database + '.dbo.' + @table + ' WHERE ' + @condition
PRINT '--' + SQL;
PRINT '-- **************************************************************'

EXEC sp_executeSQL @sql

GO