MS SQL Basics: Insert into multiple tables at once.

I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general.

I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties:

Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime)

Course (CourseID as UniqueIdentifier PK, Name as VarChar(50))

I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query.

The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere.

I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great.

Thanks.

Is Schedule supposed to represent timeslots for when Courses can take place (so that a Course has a Schedule)? Or is Schedule a collection of Courses (so that a Schedule has a Course)?

Either way, I would think that you need to insert a Foreign Key into the child table that contains the Primary Key value from the parent table.

For instance, let's assume that you intend for Course for have a Schedule:

Schedule
================================
ScheduleID uniqueidentifier (PK)
Time datetime

Course
================================
CourseID uniqueidentifier (PK)
ScheduleFK uniqueidentifier
Name varchar(50)

In this case, multiple courses can be assigned to the same schedule. You would use the "Relationships" functionality of the design mode for either table to create the foreign key relationship. From queries, you would use an INNER JOIN:

select * from course inner join schedule on course.schedulefk=schedule.scheduleid

(btw--this concept is shared across all relational databases, not just SQL Server)

|||

Sorry I should have been a bit more clear. Course holds golf course information and the schedule holds the tee times for a golf course. So one golf course has one schedule.

I understand the PK FK relationship. I assumed, incorrectly I guess in this case, that the FK is created automatically when I create the relationship. I also understand the inner join.

I'll give this a try.

|||If you have a one-to-one relationship between the two tables it may be advisable to normalise them into a singular table therefore reducing the complexity|||

Ya I was out to lunch on the one to one relationship, it is one to many; sorry I was rushing out the door this morning when I wrote this.

Couple more questions:

Weird thing happens sometimes when I make a column the PK. I get an error message when I try to save it, the system says that the column cannot be null. I have not put any data into the table yet so I don't understand where it is getting this null thing from. If I del and create the row again there is no problme, is this is bug?

Secondly, do I need to check of 'identity' in the PK column? Do the values automatically go from the PK column to the FK column or do I have to mannualy tell it to do that as the rows are being inserted?

Thanks.

|||

*I meant to say table not row for the null error part.

The error message is this:

'Course' table
- Unable to modify table.
Cannot insert the value NULL into column 'CourseID', table 'GolfPlanner.dbo.Tmp_Course'; column does not allow nulls. INSERT fails.
The statement has been terminated.

|||

I've been playing with this a bit today. What I don't understand is how to generate the PK's. Do I have to call a counter to do it?

When I try to add stuff now it tells me that courseID cannot be null, but I can't make that column into an identity because the datatype is uniqueIdentifier...??

|||

NewID() as in:

INSERT INTO MyTable(Col1,Col2,Col3) VALUES (NewID(),@.Col2,Col3)

|||Great thanks, that solved one problem. Now how to I get taht PK to become the FK in the second table. I tried to do a select inside the insert but it's telling me I'm not allowed to do that?|||

I got it to work, probably not the proper solution but here it is for anyone having the same issue:

-- =============================================

ALTER

PROCEDURE [dbo].[PopulateDatabase]-- Add the parameters for the stored procedure here

AS

declare @.CourseIDuniqueIdentifier

BEGIN

SETNOCOUNTON;INSERTINTO

Course

(CourseID,Name, Address, PhoneNumber)VALUES (NewID(),'Prospect Lake','123 Prospect St', 2508129832)

SET @.CourseID=(SELECT CourseIDFROM CourseWHEREName='Prospect Lake')

INSERTINTO

Schedule

(ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)VALUES (NewID(), @.CourseID,getDate(),'06:00','2')