how to update two table at once in MS SQL using Stored Prodecure

how to update two table at once in MS SQL using Stored Prodecure

Post by raj » Thu, 11 Oct 2001 02:18:13



Hi,
I have the following three tables

Table3                  Table 1                         Table 2
Text 2                  ID_1  (PK)                      Text 1
Column A (PK) -------->      Column A (FK)  --------------->      Column A (PK)
                1:1 Relationship        1:1 Relationship

Now the following forms

Form 1
Cloumn A: ______________________

Text 1:   ______________________

Form2:
Cloumn A: ______________________

Text 2:   ______________________

What I want to do is to insert data to Table 1 and Table to just by
filling the Forms 1 or Form 2 using at Stored Procedures in MS Sql.
Could any one help me with this problem.

Thank you

Rosh

 
 
 

how to update two table at once in MS SQL using Stored Prodecure

Post by Erland Sommarsko » Thu, 11 Oct 2001 06:35:20



> Hi,
> I have the following three tables

> Table3               Table 1                    Table 2
> Text 2               ID_1  (PK)               Text 1
> Column A (PK) -------->     Column A (FK)  --------------->     Column A
(PK)
>           1:1 Relationship     1:1 Relationship

> Now the following forms

> Form 1
> Cloumn A: ______________________

> Text 1:   ______________________

> Form2:
> Cloumn A: ______________________

> Text 2:   ______________________

> What I want to do is to insert data to Table 1 and Table to just by
> filling the Forms 1 or Form 2 using at Stored Procedures in MS Sql.
> Could any one help me with this problem.

Your question is somewhat unclear. There has to be something between
the forms and the stored procedures. That is, the stored procedures
are server-side, and the forms are client-side, and the SPs cannot
access them.

As for the tables I would probably better understand them, if I got
them as CREATE TABLE statements. You appeared to have used tab
characters in your illustrations, and if my tab stops are not the
same as yours, the pictures sort of breaks down.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

how to update two table at once in MS SQL using Stored Prodecure

Post by raj » Fri, 12 Oct 2001 02:42:44


Hi,
Thank you for replying.

As I mentioned I have following tables

Movie# (Table 1)
Movie_ID
Movie Name

MovieRented_Open (Table 2)
Movie Name
Date Rented

MovieRented_Close(Table 3)
Movie Name
Date Returned

I have created a 1:1 relationship between Movie# (Table 1)  and
MovieRented_Open (Table 2).  Also there is another 1:1 relationship
between Movie# (Table 1) and MovieRented_Close(Table 3).  Further more
is Acct_Number is the main table for this database.

On the front end I am using ASP pages.  Following is the description
of the ASP pages.
There two pages.

Page1
Thats where Movie Rented_Open would be tracked.  It consists of
the following fields.
a.      Movie Name
b.      Date Rented

Page2
Thats where MovieRented_Close will be tracked.  It consists of
the following fields.
c.      Movie Name
d.      Date Returned

This is what I want to do on the Front end whenever user types the
Movie Name; I want my page to first search the Name of the movie in
the first Table if it exists than I want it to populate the Page.  If
it doesnt exist I want user to be able to Type the name of
movie in Movie Name at the front end, which will automatically add the
record to my main table (Movie# ).  But I dont want to write a
huge script on the Front end to do all this for me.

So I thought if I write a Stored procedure on the server side to do
the above scenario would be much better than writing a lengthy ASP
script.  And I can just called that script in the ASP pages.

Please help me with this problem.

Thank you
Raj



> > Hi,
> > I have the following three tables

> > Table3               Table 1                    Table 2
> > Text 2               ID_1  (PK)               Text 1
> > Column A (PK) -------->     Column A (FK)  --------------->     Column A
>  (PK)
> >           1:1 Relationship     1:1 Relationship

> > Now the following forms

> > Form 1
> > Cloumn A: ______________________

> > Text 1:   ______________________

> > Form2:
> > Cloumn A: ______________________

> > Text 2:   ______________________

> > What I want to do is to insert data to Table 1 and Table to just by
> > filling the Forms 1 or Form 2 using at Stored Procedures in MS Sql.
> > Could any one help me with this problem.

> Your question is somewhat unclear. There has to be something between
> the forms and the stored procedures. That is, the stored procedures
> are server-side, and the forms are client-side, and the SPs cannot
> access them.

> As for the tables I would probably better understand them, if I got
> them as CREATE TABLE statements. You appeared to have used tab
> characters in your illustrations, and if my tab stops are not the
> same as yours, the pictures sort of breaks down.

 
 
 

how to update two table at once in MS SQL using Stored Prodecure

Post by Stev » Fri, 12 Oct 2001 08:19:46




If (Select count(movieID) from #temp1) = 0
Begin  
Insert Into Movie (MovieName)

Insert Into #temp1 (MovieName)

End

Select MovieName from #temp1

I am not sure exactly what it is you need to do, but this may help.
You can use the "If Exists" operator for the is statement as well


> Hi,
> Thank you for replying.

> As I mentioned I have following tables

> Movie# (Table 1)
> Movie_ID
> Movie Name

> MovieRented_Open (Table 2)
> Movie Name
> Date Rented

> MovieRented_Close(Table 3)
> Movie Name
> Date Returned

> I have created a 1:1 relationship between Movie# (Table 1)  and
> MovieRented_Open (Table 2).  Also there is another 1:1 relationship
> between Movie# (Table 1) and MovieRented_Close(Table 3).  Further more
> is Acct_Number is the main table for this database.

> On the front end I am using ASP pages.  Following is the description
> of the ASP pages.
> There two pages.

> Page1
> That’s where Movie Rented_Open would be tracked.  It consists of
> the following fields.
> a. Movie Name
> b. Date Rented

> Page2
> That’s where MovieRented_Close will be tracked.  It consists of
> the following fields.
> c. Movie Name
> d. Date Returned

> This is what I want to do on the Front end whenever user types the
> Movie Name; I want my page to first search the Name of the movie in
> the first Table if it exists than I want it to populate the Page.  If
> it doesn’t exist I want user to be able to Type the name of
> movie in Movie Name at the front end, which will automatically add the
> record to my main table (Movie# ).  But I don’t want to write a
> huge script on the Front end to do all this for me.

> So I thought if I write a Stored procedure on the server side to do
> the above scenario would be much better than writing a lengthy ASP
> script.  And I can just called that script in the ASP pages.

> Please help me with this problem.

> Thank you
> Raj



> > > Hi,
> > > I have the following three tables

> > > Table3               Table 1                    Table 2
> > > Text 2               ID_1  (PK)               Text 1
> > > Column A (PK) -------->     Column A (FK)  --------------->     Column A
>  (PK)
> > >           1:1 Relationship     1:1 Relationship

> > > Now the following forms

> > > Form 1
> > > Cloumn A: ______________________

> > > Text 1:   ______________________

> > > Form2:
> > > Cloumn A: ______________________

> > > Text 2:   ______________________

> > > What I want to do is to insert data to Table 1 and Table to just by
> > > filling the Forms 1 or Form 2 using at Stored Procedures in MS Sql.
> > > Could any one help me with this problem.

> > Your question is somewhat unclear. There has to be something between
> > the forms and the stored procedures. That is, the stored procedures
> > are server-side, and the forms are client-side, and the SPs cannot
> > access them.

> > As for the tables I would probably better understand them, if I got
> > them as CREATE TABLE statements. You appeared to have used tab
> > characters in your illustrations, and if my tab stops are not the
> > same as yours, the pictures sort of breaks down.

 
 
 

how to update two table at once in MS SQL using Stored Prodecure

Post by Erland Sommarsko » Sat, 13 Oct 2001 07:08:09



> As I mentioned I have following tables

> Movie# (Table 1)
> Movie_ID
> Movie Name

> MovieRented_Open (Table 2)
> Movie Name
> Date Rented

> MovieRented_Close(Table 3)
> Movie Name
> Date Returned

First, I did actually ask for table definitions in form of CREATE TABLE
scripts. That's captures what you have done better than a narrative,
which often are less precise than the SQL code.

That said, I take the freedoom to question to your table design. The
normal design would be somthing like:

   CREATE TABLE movies(movieid    int            NOT NULL,
                       moviename  moviename_type NOT NULL,
                       -- Other movie attributes go here
                       CONSTRAINT pk_movies PRIMARY KEY (movieid))

   CREATE TABLE movierentals
                (movieid      int       NOT NULL,
                 daterented   datetime  NOT NULL,
                 datereturned datetime  NULL,
                 CONSTRAINT pk_rentals PRIMARY KEY (movieid, daterented),
                 CONSTRAINT fk_rentals FOREIGN KEY (movieid)
                    REFERENCES movies (movieid))

Then again, it immediate strikes me that this is not very likely to
be a useful design. You are likely to have more than copy of the
most popular movies, and you also need references to the customers
etc.

--
Erland Sommarskog, Abaris AB

SQL Server MVP