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

Stored Procedures and updating multiple tables Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 4:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
Points: 15, Visits: 31
Was hoping someone could kick me in the right direction in regards stored procedures.
My issue is if I have a table that utilises foreign keys for other tables how do I update or use that data. Specifically I have a database for sports I want to enter a draw so teams matches in rounds at venues over a season. So i created a draw table

CREATE TABLE [dbo].[Draw](
[DrawId] [int] IDENTITY(1,1) NOT NULL,
[RoundNumberFk] [int] NULL,
[HomeTeam] [nchar](20) NULL,
[AwayTeam] [nchar](20) NULL,
[VenueFk] [int] NULL,
[Date] [datetime] NULL,
[TeamListFk] [int] NULL,
CONSTRAINT [PK_Draw] PRIMARY KEY CLUSTERED
(


As you can see there is a RoundNumberFK field. This comes from round.
CREATE TABLE [dbo].[Round](
[RoundID] [int] IDENTITY(1,1) NOT NULL,
[RoundNumber] [smallint] NULL,
CONSTRAINT [PK_Round] PRIMARY KEY CLUSTERED
(
Round goes to a join SeasonRound I have entered the round numbers and season details into that table already. No I want to enter in the teams home & away and the round and venue they are in.

In other words if I have inputted "season 2013" into the seasons table and added 1-26 in the rounds table, when i am updating the draw in the draw table, how to I use season 2013 with round 1 and add the teams to it so that they all match up?

So how do I link use the data from the round & season details to enter the match details?

Since I will consistently do this should it be a stored procedure?
Post #1378015
Posted Sunday, October 28, 2012 11:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
Points: 15, Visits: 31
Is there any extra info I could provide that would help
Post #1378106
Posted Monday, October 29, 2012 1:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:55 PM
Points: 3,648, Visits: 5,321
I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan.

However you have two fields shown as:
[RoundNumberFk] [int] NULL,
[TeamListFk] [int] NULL,

Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.

Here's a pretty good tutorial on doing that:
http://www.w3schools.com/sql/sql_foreignkey.asp



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1378135
Posted Monday, October 29, 2012 4:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
Points: 15, Visits: 31
dwain.c (10/29/2012)
I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan.

However you have two fields shown as:
[RoundNumberFk] [int] NULL,
[TeamListFk] [int] NULL,

Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.

Here's a pretty good tutorial on doing that:
http://www.w3schools.com/sql/sql_foreignkey.asp


Thanks I have defined the foreign keys already. I just don't know how to access them.
So to my logic to reduce the replication of fond numbers I created a round table and season table.

This allows me to say for years 2011, 2012, 2013 all featured 10 rounds. Each round has say 8 matches of 2 teams. It's doing this in the draw table that is causing me bother, so as an example this is the data I want to enter using the season and round tables but can't:

Season: 2012
Round: 1
Venue: Canberra
HomeTeam: Canberra
AwayTeam: Melbourne

But how do I use the season and round details from their tables when entering draw details?
Post #1378178
Posted Monday, October 29, 2012 4:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
how about trigger on primary or on Child table base on your requirment.

why you want to use foreign key?
foreign key is for the relation and CONSTRAINT on table


Post #1378185
Posted Monday, October 29, 2012 4:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
Points: 15, Visits: 31
BriPan (10/29/2012)
how about trigger on primary or on Child table base on your requirment.

why you want to use foreign key?
foreign key is for the relation and CONSTRAINT on table




> how about trigger on primary or on Child table base on your requirment.
I don't really understand what this means to respond properly.

I created a database design like a snowflake design everything comes to one central table games. This is the first database I have designed.

> why you want to use foreign key?

Well I thought I had to relate the tables, since draws are done for seasons and seasons are defined in rounds. Games and draws are separate as draws are for most part future and games are past(historical).
Post #1378188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse