Insert INTO from one table to another not alread exist

Question

Post reply

Insert INTO from one table to another not alread exist

Tom Chesley

SSC Enthusiast

Points: 143
More actions
September 26, 2006 at 3:55 pm

#192389

I want to be able to only copy the rows that dont exist in the backup table

So far I have this for my insert command, but of course it will attempt to copy all the records even if they already exist in the backup table, thanks in advance.

INSERT INTO tblBackupCourses SELECT tblCourses.* FROM tblCourses,tblGrades WHERE couCourseID = graCourseID AND graStudentID = 'ID1'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Use a left outer join... and for good measure always reference your tables. I'm assuming couCourseID is your primary key.

INSERT INTO tblBackupCourses

SELECT tc.*

FROM tblCourses tc

JOIN tblGrades tg

ON tc.couCourseID = tg.graCourseID

LEFT JOIN tblBackupCourses tbc

ON tc.couCourseID = tbc.couCourseID

WHERE tg.graStudentID = 'ID1'

AND tbc.couCourseID IS NULL

The final line will make sure that only unmatched rows from the product of the LEFT JOIN will be moved into the backup table.

Tom Chesley SSC Enthusiast Points: 143 More actions · Answer 2

That did the trick thanx, using the same concept but with 3 tables the same?

And yes CourseID = Primary Key

SELECT tblCourseDesc.* FROM tblCourseDesc,tblCourses,tblGrades

WHERE codCourseID = couCourseID

AND couCourseID = graCourseID AND graStudentID = 'ID1'

Would be? (I did 2 joins because a coma errored)

SELECT tcd.* from tblCourseDesc tcd JOIN tblCourses tc JOIN tblGrades tg

ON tcd.CourseID = tc.CourseID and tc.CourseID = tg.CourseID

LEFT JOIN tblBackupCourseDesc tbcd ON tc.CourseID = tbcd.codCourseID

WHERE tg.graStudentID = 'ID1'

AND tbcd.codCourseID IS NULL

but I get syntax error at or near the WHERE

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Tom - you can always run your t-sql through the parser in QA to help you debug...

You have 2 joins in your first line without specifying what column(s) you're joining them on....change it to:

SELECT tcd.* from tblCourseDesc tcd 
JOIN tblCourses tc 
ON tcd.CourseID = tc.CourseID 
JOIN tblGrades tg 
ON tc.CourseID = tg.CourseID
LEFT JOIN tblBackupCourseDesc tbcd 
ON tc.CourseID = tbcd.codCourseID
WHERE tg.graStudentID = '######'
AND tbcd.codCourseID IS NULL

**ASCII stupid question, get a stupid ANSI !!!**

Ignas Chilewa Mr or Mrs. 500 Points: 516 More actions · Answer 4

You can also use the following script to accomplish the same request.

INSERT INTO tblBackupCourses

SELECT tblCourses.*

FROM tblCourses, tblGrades

WHERE couCourseID = graCourseID AND graStudentID = 'ID1'

AND not exists(select 1 from tblBackupCourses where couCourseID = BackupCourseID)

Assumptions: couCourseID from tblCourses table is Unique and and also BackupCourseID from tblBackupCourses is unique.

Ignas

Tom Chesley SSC Enthusiast Points: 143 More actions · Answer 5

Tom Chesley

SSC Enthusiast

Points: 143

September 28, 2006 at 9:06 am

#663035

Thanx

I now have this working

How do I mark resolved?