Stored Procedure Newbie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Newbie

Author

Topic

Snapp
Starting Member

4 Posts

Posted - 2005-09-28 : 13:35:55

Hey folks,

I have done a decent amount of programming in VB .net, but I am brand new to this stored procedure stuff.

I am looking to transfer data between multiple identical tables and compile them into a single table. This compiled table will then be used by other applications.

After doing some research I believe I need to write stored procedures that will trigger on the UPDATE, INSERT, and DELETE commands of the original tables. Then these procedures would INSERT, UPDATE or DELETE from the compiled table.

If anyone could please let me know if I am on the right track, if there is a better way to do it and/or supply a bit of sample code. That would be just great!

Thanks

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 15:55:10

Can you give some examples of the tables you want to "combine" - if you provide DDL (Create table syntax) and 10 or so INSERT statements then folk where are more likely to experiment with solutions for you.

Kristen

Snapp
Starting Member

4 Posts

Posted - 2005-09-28 : 16:39:26

Well I can give it shot but the syntax checker is not a big fan of my work... I have used one of the tables here, the other aspect of this is I need to ensure that the Description field remains unique (as in only one row with a certain description). Anyways here goes, I'm sure its pretty far off:

CREATE TABLE WMPUBLIC.Route (ID smallint IDENTITY, Description nvarchar(35), StartLocation tinyint)
go
CREATE TRIGGER jp_route_INSERT
ON WMINNO.WMJOBS
FOR INSERT
AS
BEGIN
INSERT INTO WMPUBLIC.Route
SELECT ID, Description, StartLocation FROM inserted
RETURN
END

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 00:25:15

Are WMPUBLIC and WMINNO databases, or just users/owners?

Assuming databases then you need

CREATE TABLE WMPUBLIC.dbo.Route (ID smallint IDENTITY, Description nvarchar(35), StartLocation tinyint)
go
CREATE TRIGGER jp_route_INSERT
ON WMINNO.dbo.WMJOBS
FOR INSERT
AS
BEGIN
INSERT INTO WMPUBLIC.dbo.Route
(
ID, Description, StartLocation
)
SELECT ID, Description, StartLocation FROM inserted
RETURN
END

but other than that this looks fine. I've added a column list to the insert statement - otherwise if a column is added to that target table in the future this trigger will then break ("Insufficient columns provided" sort of error)

You should also specify a Primary Key on the table, and that some of the columns are NOT NULL - e.g. if the Description column must have a value.

"need to ensure that the Description field remains unique"

Easiest way is to place a unique index on it.

So what are you then needing to do for the "and compile them into a single table" part?

Kristen

Snapp
Starting Member

4 Posts

Posted - 2005-09-30 : 18:04:58

First off thanks a lot for your help.

To answer your questions:

1. Yes those are databases.
2. By remain unique I mean that the source databases could potentially contain identical records. In the case that an identical record would be inserted by the procedure into my PUBLIC or Compiled database and I had set description to be unique would this not cause some sort of error?
3. The compile them into a single table part is as follows: WMPUBLIC is the single database, the destintion database if you will, WMINNO is one of the source databases. There will be other source databases and I plan to simply apply the same triggers to them but my concern is what I have said in #2.

Again, thanks a lot for your help.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-09-30 : 21:17:14

Why not a view?

DavidM

A front-end is something that tries to violate a back-end.

Kristen
Test

22859 Posts

Posted - 2005-10-01 : 01:16:02

Worth looking into a "federated" view, or whatever its called when a view combines tables from multiple databases. Not really going to help remove duplicates though - however, if there are duplicates which one is the one you want to show? With the trigger method it will be the one from whichever database first gets inserted

2 & 3 : Prevent insertion if Description already exists in Target table


INSERT INTO WMPUBLIC.dbo.Route
(
    ID, Description, StartLocation
)
SELECT ID, Description, StartLocation 
FROM inserted AS I
WHERE NOT EXISTS (SELECT * FROM WMPUBLIC.dbo.Route AS R WHERE R.Description = I.Description)

Kristen

Snapp
Starting Member

4 Posts

Posted - 2005-10-07 : 14:43:53

I am not positive of what a view is per say.

This "combined" database is going to be used by other software, so this needs to be a permanent thing that the "other software" is going to be able to change and manipulate.

I havent had time to apply what you have said here Kirsten, but it definatly looks like what I am trying to do. Thank you very much.

Kristen
Test

22859 Posts

Posted - 2005-10-08 : 04:38:16

"needs to be a permanent thing that the "other software" is going to be able to change and manipulate"

OK, scratch the VIEW thingie then!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources