Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Insert Update Stored Procedure for a table Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 11:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
I copied the script off of the page, and pasted it into a Notepad++ 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 GenerateInsUpdateScript 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 great, 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) = 0
BEGIN
--Do the INSERT statement
END
ELSE
BEGIN
--Do the UPDATE statement
END

Does that make sense to you?



Kindest Regards,

Rod
Post #447727
Posted Monday, January 28, 2008 4:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 967, Visits: 450
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.

Thanks
Prasad


Prasad Bhogadi
www.inforaise.com
Post #448202
Posted Monday, January 28, 2008 9:57 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
Prasad Bhogadi (1/28/2008)
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.

Thanks
Prasad


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



Kindest Regards,

Rod
Post #448440
Posted Wednesday, February 27, 2008 8:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:08 AM
Points: 331, Visits: 1,356
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?
Post #460918
Posted Tuesday, May 20, 2008 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2008 1:49 PM
Points: 5, Visits: 13
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?
Post #503868
Posted Friday, June 20, 2008 5:33 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
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.



"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #521123
Posted Saturday, June 21, 2008 10:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
What would be involved in having this execute automatically for the output of this procedure that lists the user table names?

http://www.sqlservercentral.com/scripts/Miscellaneous/30070/

Then scripts for all tables could be generated in one shot!


"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #521266
Posted Saturday, June 21, 2008 10:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
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:


CREATE PROCEDURE InsUpd_ChopDates
@Chop_Date date
NULL
INSERT INTO dbo.ChopDates (
Chop_Date
)
VALUES (
@Chop_Date
NULL

UPDATE dbo.ChopDates SET
Chop_Date = @Chop_Date
NULL


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.


"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #521270
Posted Saturday, June 21, 2008 3:48 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
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 spUpd

Then 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.


"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #521332
Posted Sunday, June 22, 2008 2:00 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
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
Post #521451
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse