Posts
1150
Comments
891
Trackbacks
1
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 (@objname sysname, @includeID bit)
AS
-- Currently the proc ignores Identity columns (by checking syscolumns.status & 128 (0x80)
SET NOCOUNT ON

DECLARE @first bit,
@objid int,
@colid int,
@quote varchar(4),
@colname sysname,
@txt varchar(250),
@select varchar(8000),
@sqltxt varchar(8000)

SET @quote = ''''

SELECT @objid = id
FROM sysobjects
WHERE name = @objname

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 = @objid
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 @sqltxt = 'INSERT [' + @objname + '] ('

if (@includeID = 0) begin
SELECT @colid = MIN(colid),
@first = 1
FROM #sphelptab
WHERE col_status & 128 <> 128
end
else begin
SELECT @colid = MIN(colid),
@first = 1
FROM #sphelptab
end

SET @select = 'SELECT '
WHILE (@colid IS NOT NULL) BEGIN

SELECT @colname= RTRIM(col_name),
@txt = CASE
WHEN col_type_val IN (37,39,47) THEN
' ISNULL(' + @quote+@quote+@quote+@quote +'+'+ RTRIM(col_name) +'+'+

@quote+@quote+@quote+@quote + ',''NULL'')'
ELSE ' ISNULL(CAST('+RTRIM(col_name)+ ' AS VARCHAR),''NULL'')'
END
FROM #sphelptab
WHERE colid = @colid

IF @first = 0
SELECT @sqltxt = @sqltxt + ', ' + @colname,
@select = @select + '+' + @quote + ',' + @quote + '+' + @txt
ELSE
SELECT @sqltxt = @sqltxt + ' ' + @colname,
@select = @txt

SELECT @colid = MIN(colid),
@first = 0
FROM #sphelptab
WHERE col_status & 128 <> 128
AND colid > @colid

END

SELECT @sqltxt = @sqltxt + ') VALUES (' + @quote + '+',
@select = @select + '+' + @quote + ')' + @quote + ' FROM [' + @objname + ']'

if (@includeID = 1) begin
select 'SET IDENTITY_INSERT [' + @objname + '] ON'
select 'GO'
end

EXEC( 'SELECT ' + @quote + @sqltxt + @select)

if (@includeID = 1) begin
select 'SET IDENTITY_INSERT [' + @objname + '] OFF'
select 'GO'
end

GO

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

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 8 and 1 and type the answer here: