Hi all,

this is a really good idea.

I struggled sometimes about the need to get a few thousend rows from one database into a local test database, which could be done vrey easy by this code, Thanks for this.

But it would be fine, if such a functionality could be located in a "tools" database, so I improved the code a bit.

Now it is possible to point to the the target database with a additional input parameter of the procedure. Hope, that is an improvement someone needs.

Joe

Here is the code:

--======================================================================

-- 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'Test',

--@table = N'Pricing'

--======================================================================

CREATE PROC spScriptInsert(@database sysname,

@table varchar(80),

@condition varchar(80) = '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

EXEC sp_executeSQL @sql

GO