CREATE PROCEDURE [dbo].[sp_generate_insert_script]
@tablename_mask VARCHAR(30) = NULL
AS
BEGIN
DECLARE @tablename VARCHAR(128)
DECLARE @tablename_max VARCHAR(128)
DECLARE @tableid INT
DECLARE @columncount NUMERIC(7, 0)
DECLARE @columncount_max NUMERIC(7, 0)
DECLARE @columnname VARCHAR(30)
DECLARE @columntype INT
DECLARE @string VARCHAR(30)
DECLARE @leftpart VARCHAR(8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */
DECLARE @rightpart VARCHAR(8000) /* without having to resort to concatenation */
DECLARE @hasident INT
SET nocount ON
-- take ALL tables when no mask is given (!)
IF ( @tablename_mask IS NULL )
BEGIN
SELECT @tablename_mask = '%'
END
-- create table columninfo now, because it will be used several times
CREATE TABLE #columninfo
(
num NUMERIC(7, 0) IDENTITY,
name VARCHAR(30),
usertype SMALLINT
)
SELECT name,
id
INTO #tablenames
FROM sysobjects
WHERE type IN ( 'U', 'S' )
AND name LIKE @tablename_mask
-- loop through the table #tablenames
SELECT @tablename_max = MAX(name),
@tablename = MIN(name)
FROM #tablenames
WHILE @tablename <= @tablename_max
BEGIN
SELECT @tableid = id
FROM #tablenames
WHERE name = @tablename
IF ( @@rowcount <> 0 )
BEGIN
-- Find out whether the table contains an identity column
SELECT @hasident = MAX(status & 0x80)
FROM syscolumns
WHERE id = @tableid
TRUNCATE TABLE #columninfo
INSERT INTO #columninfo ( name, usertype )
SELECT name,
type
FROM syscolumns C
WHERE id = @tableid
AND type <> 37 -- do not include timestamps
-- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames
SELECT @leftpart = 'select ''insert into '
+ @tablename
SELECT @leftpart = @leftpart + '('
SELECT @columncount = MIN(num),
@columncount_max = MAX(num)
FROM #columninfo
WHILE @columncount <= @columncount_max
BEGIN
SELECT @columnname = name,
@columntype = usertype
FROM #columninfo
WHERE num = @columncount
IF ( @@rowcount <> 0 )
BEGIN
IF ( @columncount < @columncount_max )
BEGIN
SELECT @leftpart = @leftpart
+ @columnname + ','
END
ELSE
BEGIN
SELECT @leftpart = @leftpart
+ @columnname + ')'
END
END
SELECT @columncount = @columncount + 1
END
SELECT @leftpart = @leftpart + ' values('''
-- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted
SELECT @columncount = MIN(num),
@columncount_max = MAX(num)
FROM #columninfo
SELECT @rightpart = ''
WHILE @columncount <= @columncount_max
BEGIN
SELECT @columnname = name,
@columntype = usertype
FROM #columninfo
WHERE num = @columncount
IF ( @@rowcount <> 0 )
BEGIN
IF @columntype IN ( 39, 47 ) /* char fields need quotes (except when entering NULL);
* use char(39) == ', easier readable than escaping
*/
BEGIN
SELECT @rightpart = @rightpart
+ '+'
SELECT @rightpart = @rightpart
+ 'ISNULL('
+ REPLICATE(CHAR(39), 4)
+ '+replace('
+ @columnname + ','
+ REPLICATE(CHAR(39), 4)
+ ','
+ REPLICATE(CHAR(39), 6)
+ ')+'
+ REPLICATE(CHAR(39), 4)
+ ',''NULL'')'
END
ELSE
IF @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
/* convert to VC 1000 to leave space for other fields */
BEGIN
SELECT @rightpart = @rightpart
+ '+'
SELECT @rightpart = @rightpart
+ 'ISNULL('
+ REPLICATE(CHAR(39), 4)
+ '+replace(convert(varchar(1000),'
+ @columnname
+ ')' + ','
+ REPLICATE(CHAR(39), 4)
+ ','
+ REPLICATE(CHAR(39), 6)
+ ')+'
+ REPLICATE(CHAR(39), 4)
+ ',''NULL'')'
END
ELSE
IF @columntype IN ( 58, 61, 111 ) /* datetime fields */
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(' + REPLICATE(CHAR(39), 4) + '+convert(varchar(20),' + @columnname + ')+' + REPLICATE(CHAR(39), 4) + ',''NULL'')'
END
ELSE /* numeric types */
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
END
IF ( @columncount < @columncount_max )
BEGIN
SELECT @rightpart = @rightpart
+ '+'','''
END
END
SELECT @columncount = @columncount + 1
END
END
SELECT @rightpart = @rightpart + '+'')''' + ' from '
+ @tablename
-- Order the select-statements by the first column so you have the same order for
-- different database (easy for comparisons between databases with different creation orders)
SELECT @rightpart = @rightpart + ' order by 1'
-- For tables which contain an identity column we turn identity_insert on
-- so we get exactly the same content
IF @hasident > 0
SELECT 'SET IDENTITY_INSERT ' + @tablename + ' ON'
EXEC ( @leftpart + @rightpart
)
IF @hasident > 0
SELECT 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
SELECT @tablename = MIN(name)
FROM #tablenames
WHERE name > @tablename
END
END
