Almost as an aside, this script was posted at:
http://scottonwriting.net/sowBlog/posts/2801.aspx
It allows you to recreate a table's data. This would be a good way to script out lookup tables and add them to a create database script. Very cool.
*************************************************************************
--Stored Procedure to Create Insert commands from table of data
--At the moment dates need to be edited.
go
CREATE PROCEDURE usp_CreateInsert ( sysname, bit)
AS
-- Currently the proc ignores Identity columns (by checking syscolumns.status & 128 (0x80)
SET NOCOUNT ON
DECLARE bit,
int,
int,
varchar(4),
sysname,
@txt varchar(250),
varchar(8000),
varchar(8000)
SET = ''''
SELECT = id
FROM sysobjects
WHERE name =
CREATE TABLE #sphelptab
(
col_name char (30) NULL,
col_type char (30) NULL,
col_type_val int NULL,
col_len int NULL,
col_prec char (5) NULL,
col_scale char (5) NULL,
col_status int NULL,
colid int NULL
)
INSERT
INTO #sphelptab
SELECT c.name,
t.name,
c.type,
c.length,
CONVERT(CHAR(5),c.prec),
CONVERT(CHAR(5),c.scale),
c.status,
c.colid
FROM syscolumns c,
systypes t
WHERE c.id =
AND c.usertype *= t.usertype
--
-- Don't display precision and scale for datatypes
-- which they not applicable.
--
UPDATE #sphelptab
SET col_prec = '',
col_scale = ''
WHERE col_type IN
(SELECT name FROM systypes WHERE type NOT IN
(38,48,52,55,56,59,60,62,63,106,108,109,110,122))
--
-- Insert script
--
SELECT = 'INSERT [' + + '] ('
if ( = 0) begin
SELECT = MIN(colid),
= 1
FROM #sphelptab
WHERE col_status & 128 <> 128
end
else begin
SELECT = MIN(colid),
= 1
FROM #sphelptab
end
SET = 'SELECT '
WHILE ( IS NOT NULL) BEGIN
SELECT = RTRIM(col_name),
@txt = CASE
WHEN col_type_val IN (37,39,47) THEN
' ISNULL(' + +++ +'+'+ RTRIM(col_name) +'+'+
+++ + ',''NULL'')'
ELSE ' ISNULL(CAST('+RTRIM(col_name)+ ' AS VARCHAR),''NULL'')'
END
FROM #sphelptab
WHERE colid =
IF = 0
SELECT = + ', ' + ,
= + '+' + + ',' + + '+' + @txt
ELSE
SELECT = + ' ' + ,
= @txt
SELECT = MIN(colid),
= 0
FROM #sphelptab
WHERE col_status & 128 <> 128
AND colid >
END
SELECT = + ') VALUES (' + + '+',
= + '+' + + ')' + + ' FROM [' + + ']'
if ( = 1) begin
select 'SET IDENTITY_INSERT [' + + '] ON'
select 'GO'
end
EXEC( 'SELECT ' + + + )
if ( = 1) begin
select 'SET IDENTITY_INSERT [' + + '] OFF'
select 'GO'
end
GO