Enter record (Sancronized) - 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

General SQL Server Forums

New to SQL Server Programming

Enter record (Sancronized)

Author

Topic

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-28 : 02:17:25

Hi,

I have two tables. Class & Faculty

"Class" table have column names ClassID, SchoolID and FacultyID(FacultyID column have just created therefore there is no data in the column)
"Faculty" table have column names FacultyID, SchoolID

In "class" table every SchoolID have 1 or 2 classID. Whereas in "Faculty" table every facultyID have more than 3 SchoolID.

Therefore when I update the table it only update the rows not entering new rows as per schoolID.

How can we add new rows in the class table according SchoolID,
Because I want to get data in the newly generated column FacultyID in "CLASS" table.

Thanks,

Navi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 02:58:33

Can you give some sample data to illustrate scenario?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 03:11:47

ok. i think i got what you require (just saw ur earlier post). Perhaps this is what you want:-

INSERT INTO Class (SchoodID,FacultyID)
SELECT s.SchoolID,s.FacultyID
FROM Class c
LEFT OUTER JOIN School s
ON c.SchoolID=s.SchoolID
WHERE c.SchoolID IS NULL

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-28 : 05:28:10

Thanks visakh16,

The earlier post That you have me is updated the class table only which rows already exist. It cannot include new rows in the Class table. I want to get all facultyID in the class table. FacultyID are 100 in the Faculty Table, Whereas the ClassID are only 50. I want to get all FacultyID in the class table. For which there should be more row added in the Class table.

Can you help me about this. If some thing is missing please tell me.

thanks.

Navi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 05:46:19

quote:
Originally posted by feejaz

Thanks visakh16,

The earlier post That you have me is updated the class table only which rows already exist. It cannot include new rows in the Class table. I want to get all facultyID in the class table. FacultyID are 100 in the Faculty Table, Whereas the ClassID are only 50. I want to get all FacultyID in the class table. For which there should be more row added in the Class table.

Can you help me about this. If some thing is missing please tell me.

thanks.

Navi

You need to do this in two steps:-
1.Update the existing record values
2.Insert the new records

soln:-

1.UPDATE c
SET c.FacultyID=f.FacultyID
FROM Class c
INNER JOIN Faculty f
ON f.SchoolID=c.SchoolID

2.INSERT INTO Class (SchoolID,FacultyID)
SELECT f.SchoolID,f.FacultyID
FROM Class c
LEFT OUTER JOIN Faculty f 
ON f.SchoolID=c.SchoolID
WHERE f.SchoolID IS NULL

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-01-28 : 06:17:47

Now I have used 2nd Query and found this error
Cannot insert the value NULL into column 'Total', table 'Collegedb.dbo.Class'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Navi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources