Posts
463
Comments
319
Trackbacks
1
<< Removing Business Logic from Stored Procedures | Home | How to rename a SQL Server >>
Create Insert Stored Procedure

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

posted on Monday, July 11, 2005 9:01 AM
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 7 and 4 and type the answer here: