SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Prasad Bhogadi / Insert Update Stored Procedure for a table / Latest Posts

RE: Insert Update Stored Procedure for a table

Kent Roberts-146295 — Tue, 05 May 2009 10:33:05 GMT

It works ok but beware, the values list generated for the Insert statement do not match the order of the columns in the Insert Into portion.Easily remedied and it still saves a bunch of typing.Thanks!!

RE: Insert Update Stored Procedure for a table

Keith DuAime — Fri, 09 Jan 2009 12:32:42 GMT

I've been working on similar issues including reproducing creating table and missing columns and thought this would help a lot. It does, thank you. However there are some clitches in it. I created a table[code]CREATE TABLE dbo.tbl( bi BIGINT, d DECIMAL (18, 2), f FLOAT, i INT, m MONEY, n NUMERIC (15, 3), r REAL, si SMALLINT, sm SMALLMONEY, ti TINYINT, sChar CHAR(10), sVarchar VARCHAR(100), sMax VARCHAR (MAX), id INT IDENTITY(1, 1))GO[/code][quote]CREATE PROCEDURE InsUpd_tbl @bi bigint (8), @d decimal (18,2), NULL @i int , @m money (19,4), @n numeric (15,3), NULL @si smallint , @sm smallmoney (10,4), @ti tinyint (1), @sChar char (10), @sVarchar varchar (100), @sMax varchar (-1), @id int AS BEGIN IF @id <= 0 BEGIN INSERT INTO dbo.tbl ( bi, d, f, i, m, n, r, si, sm, ti, sChar, sVarchar, sMax ) VALUES ( @bi, @d, @f, @i, @m, @n, @r, @si, @sm, @ti, @sChar, @sVarchar, @sMax ) SET @id = @@IDENTITY SELECT @id AS id END ELSE BEGIN UPDATE dbo.tbl SET bi = @bi, d = @d, f = @f, i = @i, m = @m, n = @n, r = @r, si = @si, sm = @sm, ti = @ti, sChar = @sChar, sVarchar = @sVarchar, sMax = @sMax WHERE id= @id SELECT @id AS id END END[/quote]and there are quite a few null fields. I think the precision or scale is causing this.I have also have written a query that does the trick and thought I'd share it here.[code]SET NOCOUNT ONGOIF OBJECT_ID('dbo.fGetColumnList') IS NOT NULL DROP FUNCTION dbo.fGetColumnListGOCREATE FUNCTION dbo.fGetColumnList( @TableId INT, @VariableList BIT )RETURNS VARCHAR(MAX)ASBEGIN DECLARE @ColumnList VARCHAR(MAX) SET @ColumnList = '' SELECT @ColumnList = @ColumnList + CASE @VariableList WHEN 1 THEN '@' ELSE '' END + LOWER(c.NAME) + ',' FROM sys.columns c WHERE c.[object_id] = @TableId AND c.is_identity = 0 ORDER BY c.column_id SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1) RETURN @ColumnListENDGOIF OBJECT_ID('dbo.GenerateInsUpdateScript') IS NOT NULL DROP PROCEDURE dbo.GenerateInsUpdateScriptGOCREATE PROCEDURE dbo.GenerateInsUpdateScript( @TableId INT)ASSET NOCOUNT ONBEGIN SELECT t.NAME TableName, 1 OrderBy, 0 column_id, 'IF OBJECT_ID(''pInsUpd_' + t.NAME + ''') IS NOT NULL' + CHAR(13) + CHAR(10) + ' DROP PROCEDURE pInsUpd_' + LOWER(t.NAME) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE pInsUpd_' + LOWER(t.NAME) + '(' FROM sys.tables t WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) UNION ALL SELECT t.NAME TableName, 2 OrderBy, c.column_id, ' @' + LOWER(c.NAME) + ' ' + typ.NAME + CASE --No Precision or scale WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','text') THEN '' --Precision ELSE CASE WHEN c.max_length = -1 THEN '(MAX' ELSE '(' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Precision') AS VARCHAR(10)) + --and Scale CASE WHEN typ.NAME IN ('decimal','numeric') THEN ',' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Scale') AS VARCHAR(10)) ELSE '' END END + ')' END + CASE WHEN c.column_id < t.max_column_id_used THEN ',' ELSE CHAR(13) + CHAR(10) + ')' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + 'SET NOCOUNT ON' + CHAR(13) + CHAR(10) + 'BEGIN' END FROM sys.tables t INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) UNION ALL SELECT OBJECT_NAME(c.[object_id]) TableName, 3 OrderBy, NULL column_id, ' IF @' + LOWER(c.NAME) + ' = 0' FROM sys.tables t INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1 WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) UNION ALL SELECT t.NAME TableName, 4 OrderBy, null, ' INSERT INTO ' + OBJECT_NAME(t.[object_id]) + '(' + dbo.fGetColumnList(t.OBJECT_ID, 0) + ')' FROM sys.tables t WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) UNION ALL SELECT t.NAME TableName, 5 OrderBy, null, ' VALUES(' + dbo.fGetColumnList(t.OBJECT_ID, 1) + ')' FROM sys.tables t WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) UNION ALL SELECT t.NAME TableName, 6 OrderBy, c.column_id, CASE WHEN c.column_id = MinMaxColumn.MinColumn THEN ' ELSE' + CHAR(13) + CHAR(10) + ' UPDATE ' + t.[name] + ' SET' + CHAR(13) + CHAR(10) ELSE '' END + ' ' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) + CASE WHEN c.column_id < MinMaxColumn.MaxColumn THEN ',' ELSE '' END FROM sys.tables t INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 0 INNER JOIN ( SELECT c.[object_id], MIN(c.column_id) MinColumn, MAX(c.column_id) MaxColumn FROM sys.columns c WHERE c.is_identity = 0 GROUP BY c.[object_id] ) MinMaxColumn ON MinMaxColumn.[object_id] = c.[object_id] WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) UNION ALL SELECT OBJECT_NAME(c.[object_id]) TableName, 7 OrderBy, NULL column_id, ' WHERE ' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.tables t INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1 WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID) AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1) ORDER BY TableName, OrderBy, column_idENDGO[/code]It can be changed a bit but the reason I'm getting tablename, orderby, column_id, etc... is becuase this is part of something bigger and i use osql and bcp to get the carriage return line feeds the way I want.

RE: Insert Update Stored Procedure for a table

nigelevenden — Wed, 26 Nov 2008 03:35:15 GMT

Ok, I've read some advice on here. I paste the script into notepad, removed all the bad characters (that are easy to spot) and now the script works... I look forward to trying it out later in the day (when I get a few minutes without customers complaining all day long...lol).Many thanks to all :-)

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Wed, 26 Nov 2008 03:32:59 GMT

[quote][b]nigelevenden (11/26/2008)[/b][hr]Hi, Please forgive me sounding silly but I am a bit of a novice...I am getting the follwoing errors. Are these as a result of my copying and pasting the script..? If so it might take me an age to try and debug as I can't see anything wrong with the transact SQL. Any tips please..? Msg 102, Level 15, State 1, Line 22Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 27Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 28Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 29Incorrect syntax near ' '.Msg 137, Level 15, State 1, Line 29Must declare the scalar variable "@objid".Msg 102, Level 15, State 1, Line 31Incorrect syntax near ' '.[/quote]The issue is due to the special characters that are generated in the editor. What I would request you is to copy the script and paste it in a note pad and you just replace the special characters that are shown on each line where a tab is used and replace them. It should get compiled without any issues.

RE: Insert Update Stored Procedure for a table

nigelevenden — Wed, 26 Nov 2008 03:26:54 GMT

Hi, Please forgive me sounding silly but I am a bit of a novice...I am getting the follwoing errors. Are these as a result of my copying and pasting the script..? If so it might take me an age to try and debug as I can't see anything wrong with the transact SQL. Any tips please..? Msg 102, Level 15, State 1, Line 22Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 27Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 28Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 29Incorrect syntax near ' '.Msg 137, Level 15, State 1, Line 29Must declare the scalar variable "@objid".Msg 102, Level 15, State 1, Line 31Incorrect syntax near ' '.

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Tue, 25 Nov 2008 08:13:08 GMT

[quote][b]Prasad Bhogadi (9/1/2008)[/b][hr]Are you using the initial script or the modified script? Would you mind posting your table structure please.Thanks[/quote]Hello hsitas953I found that when I copy and paste the code in a text editor like Editplus the tabs that are in the script are converted to special characters "?". So you may want to paste the code in a text editor remove this characters and run from the query analyzer. I believe I have not taking enough precautions while posting the script to avoid such problems.Thanks

RE: Insert Update Stored Procedure for a table

kodracon — Fri, 21 Nov 2008 03:06:06 GMT

hi guys/girlstry this as an alternative as wellhttp://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/cheerschris

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Mon, 01 Sep 2008 02:20:18 GMT

Are you using the initial script or the modified script? Would you mind posting your table structure please.Thanks

RE: Insert Update Stored Procedure for a table

hsitas953 — Sat, 30 Aug 2008 13:21:57 GMT

[quote][b]vblllove (12/6/2007)[/b][hr]I try to execute the Script but I get Msg 102, Level 15, State 1, Line 23Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 29Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 31Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 33Incorrect syntax near ' '.Msg 137, Level 15, State 1, Line 33Must declare the scalar variable "@objid".Msg 102, Level 15, State 1, Line 37Incorrect syntax near ' '.Msg 137, Level 15, State 1, Line 39Must declare the scalar variable "@colname".Msg 137, Level 15, State 2, Line 44Must declare the scalar variable "@sysobj_type".Msg 102, Level 15, State 1, Line 52Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 54Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 56Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 58Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 62Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 72Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 72Must declare the scalar variable "@objname".Msg 102, Level 15, State 1, Line 78Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 112Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 116Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 118Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 122Must declare the scalar variable "@colname".Msg 102, Level 15, State 1, Line 130Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 130Must declare the scalar variable "@objname".Msg 102, Level 15, State 1, Line 136Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 138Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 144Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 150Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 154Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 156Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 160Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 162Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 166Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 168Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 170Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 176Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 182Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 188Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 194Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 198Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 204Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 208Must declare the scalar variable "@colname".Msg 102, Level 15, State 1, Line 226Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 230Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 232Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 232Must declare the scalar variable "@objname".Msg 102, Level 15, State 1, Line 240Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 242Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 248Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 254Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 260Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 266Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 270Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 272Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 274Must declare the scalar variable "@colname".Msg 102, Level 15, State 1, Line 288Incorrect syntax near 'end'.a lot of errors[/quote]

RE: Insert Update Stored Procedure for a table

billross — Sun, 22 Jun 2008 14:00:17 GMT

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 namesI 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:[quote]/* ---------------------------------------------------------------------------------------------------------------------------------------- Author : Prasad BhogadiName 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 spGenerateInsUpdateScriptGOCREATE PROCEDURE spGenerateInsUpdateScript@objname nvarchar(776) --object name we're afterasSET 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 endGO[/quote]Then to call it for all my files I do this:[quote]DROP TABLE dbo.MyProcGOCREATE TABLE MyProc(pkey INT NOT NULL IDENTITY (1, 1),ID INT ,MyStatement NVARCHAR(4000))EXEC spGenerateInsUpdateScript N'dbo.Table1'GOEXEC spGenerateInsUpdateScript N'ref.Table2'GOSelect MyStatement FROM dbo.MyProcOrder by IDGoDROP TABLE dbo.MyProcGO[/quote]This seems to work ok in my environment.

RE: Insert Update Stored Procedure for a table

billross — Sat, 21 Jun 2008 15:48:06 GMT

Okay, I'm thinking that this is intended to be deployed as two separate scripts. To be more useful, I would suggest the following:* have this automatically run for each table in the database (per my suggestion above)* have the Insert and the Update be parsed into separate create statements, separated by a GO* have all of the output for the whole job go to a single file (with all of the creates preceded by a Drop Procedure and followed by a GO)* also add a simple SELECT ** have the proc names begin with spIns and spSel and spUpdThen you could, in a click, generate the whole deal!I think that on one hand this script argues for the power of TSQL to do significant stuff while on the other hand it argues that CLR is probably going to come into play in the future for complicated stuff.

RE: Insert Update Stored Procedure for a table

billross — Sat, 21 Jun 2008 10:55:23 GMT

I ran this script against one of my tables which does not have an identity column and seemed to do okay but with a couple of problems that I think are unrelated to the lack of an identity column.The problem is that Sql Server complains abou the syntax of the word "NULL" after the field names in three places, like so:[quote]CREATE PROCEDURE InsUpd_ChopDates @Chop_Date date NULL INSERT INTO dbo.ChopDates ( Chop_Date ) VALUES ( @Chop_DateNULL UPDATE dbo.ChopDates SET Chop_Date = @Chop_DateNULL[/quote]In order to get it to work in my test I had to delete the word "NULL" in all three places, add an "AS" keyword and close the parentheses on Values.Shouldn't these be separate procedures? Why both in one? It looks like it will always add and always update. Or is this just to get you going, and you will just split the code up into two procedures?I'm a major newbie, so many thanks.

RE: Insert Update Stored Procedure for a table

billross — Sat, 21 Jun 2008 10:18:43 GMT

What would be involved in having this execute automatically for the output of this procedure that lists the user table names?[u]http://www.sqlservercentral.com/scripts/Miscellaneous/30070/[/u]Then scripts for all tables could be generated in one shot!

RE: Insert Update Stored Procedure for a table

billross — Fri, 20 Jun 2008 17:33:22 GMT

The spaces that it adds are Unicode characters. In order to get rid of them I saved it in notepad and it replaced them with ? - then I did a global replace.

RE: Insert Update Stored Procedure for a table

L-JEFF — Tue, 20 May 2008 10:23:41 GMT

I need to insert and update a table that I created from AS400 tp sql server2000. The table gets the information, yet I need to continue to recieve new inserts and updates/changes from as400. How can I do this with DTS?

RE: Insert Update Stored Procedure for a table

Jonathan AC Roberts — Wed, 27 Feb 2008 08:13:51 GMT

I realy like this stored procedure. I have a system that uses UPSERTs where first you try to update a row using the primary key and if it the @@rowcount is zero then insert into the table instead.Do you think you could do a version that does this instead?

RE: Insert Update Stored Procedure for a table

Rod at work — Mon, 28 Jan 2008 09:57:51 GMT

[quote][b]Prasad Bhogadi (1/28/2008)[/b][hr]Hello Rod,Thank you for the comments, I see what you were doing! You tried to generate the stored procedure for a table that has no Identity defined and as you might have noticed in my description about the stored procedure "Script asssumes that it has a primary key with auto identity defined". If you try generating the stored procedure for a table that has Identity defined for the primary key, you will be able to generate a complete stored procedure script with BEGIN and END.Thanking you for your suggestions and I would incorporate the logic to handle tables without IDENTITY column.ThanksPrasad[/quote]Prasad, I completely missed that line ("Script assumes that it has a primary key with auto identity defined"). WOW, no wonder!! We have several tables, the vast majority of which don't have an identity column in them. Well, even so your script gives me the basics, which I think I can work with.Rod

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Mon, 28 Jan 2008 04:32:13 GMT

Hello Rod,Thank you for the comments, I see what you were doing! You tried to generate the stored procedure for a table that has no Identity defined and as you might have noticed in my description about the stored procedure "Script asssumes that it has a primary key with auto identity defined". If you try generating the stored procedure for a table that has Identity defined for the primary key, you will be able to generate a complete stored procedure script with BEGIN and END.Thanking you for your suggestions and I would incorporate the logic to handle tables without IDENTITY column.ThanksPrasad

RE: Insert Update Stored Procedure for a table

Rod at work — Fri, 25 Jan 2008 11:39:28 GMT

I copied the script off of the page, and pasted it into a [url=http://notepad-plus.sourceforge.net/uk/site.htm]Notepad++[/url] buffer to look at it. One thing that was kind of odd were the strange characters, which Notepad++ represented by a several "?" characters, which I assume was Notepad++ trying to give me something that went beyond the normal ANSI characterset. All I did was replace each of those "?" with a blank, and that seems to have worked. I ran the script which created the [b]GenerateInsUpdateScript[/b] stored procedure.In looking at your code I see that your write everything that would generate the CREATE PROCEDURE script into a temporary table and then perform a SELECT against that. However, one thing that I also noticed was that the script that GenerateInsUpdateScript generates isn't quite a complete stored proc. It is missing the BEGIN and END statements for a SP. And also I noticed that between the declaration of the SP and its parameters there is a NULL, as well as there is a NULL between the INSERT statement and the UPDATE statement that GenerateInsUpdateScript comes up with. Still, this is [u]great[/u], because I would hate to do all that GenerateInsUpdateScript does for me! I was thinking that I could just go to the NULL statements and replace them with logic. Something like:IF (SELECT COUNT(*) FROM myTable WHERE LastName = @LastName) = 0BEGIN --Do the INSERT statementENDELSEBEGIN --Do the UPDATE statementENDDoes that make sense to you?

RE: Insert Update Stored Procedure for a table

Sreejithsql — Wed, 23 Jan 2008 14:22:29 GMT

hi,its soo helpful script for me. so simple to run. really appreaciatable.thxsreejithMCAD

RE: Insert Update Stored Procedure for a table

Marty Comella — Thu, 10 Jan 2008 13:07:13 GMT

Excellent Stored Proc! This will save me a ton of time!

RE: Insert Update Stored Procedure for a table

Eros Zica — Thu, 06 Dec 2007 12:55:44 GMT

So simple and so useful. Thanks a lot!Eros

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Thu, 06 Dec 2007 08:20:16 GMT

Thank You, thats a good idea I would probably create a tool which would automate creation of the Insert, Update, Select and Delete Stored Procedures.Thanks

RE: Insert Update Stored Procedure for a table

vblllove — Thu, 06 Dec 2007 07:54:46 GMT

I actually use the mygeneration tool to create insert,update,delete, loadbyprimarykey stored procedures which works fine But I thought to give a shot to this oneany how : good work

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Thu, 06 Dec 2007 07:12:20 GMT

It just generates the script as it is a generic script. You need to execute the script and it would create a stored procedure. I thought this is generic and based on the based on the requirements may need little customization. So just generating the script.Thanks

RE: Insert Update Stored Procedure for a table

vblllove — Thu, 06 Dec 2007 07:04:16 GMT

Never mind it was some spaces that were created when I copy paste the code. I fixed and it works.now another question when I run the stored procedure ( exec GenerateInsUpdateScript countries) isnt that suppose to generate another stored procedure and save to my database or it just display it to me????Because It just display it to me.

RE: Insert Update Stored Procedure for a table

Prasad Bhogadi — Thu, 06 Dec 2007 06:41:15 GMT

Can you please post what errors you are getting?Thanks

RE: Insert Update Stored Procedure for a table

vblllove — Thu, 06 Dec 2007 03:08:16 GMT

I try to execute the Script but I get a lot of errors

Insert Update Stored Procedure for a table

Prasad Bhogadi — Thu, 11 Oct 2007 17:25:08 GMT

Comments posted to this topic are about the item [B]Insert Update Stored Procedure for a table[/B]