Ok, I have adapted this to my needs as follows:
* I changed it from a local temp table to a table so I could do all of the tables into one output
* I change the explicit ID numbers to automatic
* I added a Select function
* I added the drops
* I added support for schema names
I also changed per someone's suggestions for nVarChar, but then I had to divide the lengths by two because it doubled every one for some reason.
I took out the stuff that presumed an Identity primary key and I added a bogus "where ID = @ID" which will have to be changed later manually to the real key.
Here is my version:
/*
----------------------------------------------------------------------------------------------------------------------------------------
Author : Prasad Bhogadi
Name of the Procedure : spGenerateInsUpdateScript
----------------------------------------------------------------------------------------------------------------------------------------
Purpose :This Procedure is used generate Insert Update scripts for a tablet
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
Input Parameters : Table Name
Expected Output : Generate script for Insert Update Stored procedure for a given table
---------------------------------------------------------------------------------------------------------------------------------------- */
DROP PROC spGenerateInsUpdateScript
GO
CREATE PROCEDURE spGenerateInsUpdateScript
@objname nvarchar(776) --object name we're after
as
SET NOCOUNT ON
DECLARE @shortObjName nVarChar(776) --strip off schema if present
DECLARE @periodPosition tinyint
SET @shortObjName = @objname
SET @periodPosition = CHARINDEX('.',@objname)
If (@periodPosition > 1) -- if there is a schema
BEGIN
SET @shortObjName = SUBSTRING(@objname,@periodPosition + 1,776)
END
DECLARE @objid int
DECLARE @sysobj_type char(2)
SELECT @objid = id, @sysobj_type = xtype
from sysobjects
where id = object_id(@objname)
DECLARE @colname sysname
SELECT @colname = name
from syscolumns
where id = @objid and colstat & 1 = 1
-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin
-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
DECLARE @numtypes nvarchar(80)
DECLARE @avoidlength nvarchar(80)
SELECT @numtypes = N'decimalreal,money,float,numeric,smallmoney'
SELECT @avoidlength = N'int,smallint,datatime,smalldatetime,text,bit'
---- INFO FOR EACH COLUMN
--CREATE TABLE MyProc
--(pkey INT NOT NULL IDENTITY (1, 1),
--ID INT ,
--MyStatement NVARCHAR(4000))
--
-- The "INSERT" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spInsert' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spInsert' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spInsert' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ' ' +
type_name(xusertype) + ' '
+ case when charindex(type_name(xtype),@avoidlength) > 0
then ''
else
case when charindex(type_name(xtype), @numtypes) <= 0
then '(' + convert(varchar(10), length / 2) + ')' else '(' +
case when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),ColumnProperty(id, name, 'precision'))
else '' end + case when charindex(type_name(xtype), @numtypes) > 0 then ',' else ' ' end +
case
when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),OdbcScale(xtype,xscale))
else ''
end + ')'
end
end + ', '
from syscolumns where id = @objid and number = 0 order by colid
update MyProc set MyStatement = Replace(MyStatement,', ',' ') where
pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT 'AS '
--BEGIN
--IF @' + @colname + ' <= 0
--BEGIN'
INSERT INTO MyProc (MyStatement)
SELECT ' INSERT INTO dbo.' + @objname + ' ('
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 4 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' )'
INSERT INTO MyProc (MyStatement)
SELECT ' VALUES ('
INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 7 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,'@DateCreated,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateCreated,'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateModified,'
update MyProc
set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
-- SET @colname = @@IDENTITY
INSERT INTO MyProc (MyStatement)
SELECT ')'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT ' '
-- The "UPDATE" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spUpdate' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spUpdate' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS UPDATE ' + @objname
INSERT INTO MyProc (MyStatement)
SELECT 'SET '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ' = @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%DateCreated %'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 11 AND MyStatement like '%@DateModified,'
update MyProc set MyStatement = Replace(MyStatement,',',' ')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
-- The "SELECT" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spGet' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spGet' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS Select '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spInsert' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
-- disply the results into the output
-- SELECT MyStatement from MyProc ORDER BY ID
end
GO
Then to call it for all my files I do this:
DROP TABLE dbo.MyProc
GO
CREATE TABLE MyProc
(pkey INT NOT NULL IDENTITY (1, 1),
ID INT ,
MyStatement NVARCHAR(4000))
EXEC spGenerateInsUpdateScript N'dbo.Table1'
GO
EXEC spGenerateInsUpdateScript N'ref.Table2'
GO
Select MyStatement
FROM dbo.MyProc
Order by ID
Go
DROP TABLE dbo.MyProc
GO
This seems to work ok in my environment.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com