VB Express 2010 : "INSERT INTO" does not seem to use "WHERE"

[Chewystar]

Guys, I am a newbie to using SQL inside the Query Builder of VB Express 2010.

Very sorry if this is a stupid question... .

I have 2 tables:
"Student Details" ( Primary key: "SID" )
and
"Level 2 Red Belt" (Primary key:"SID" )

I want after entering a record in "Student Details" to run an "INSERT INTO" query... to insert the new "Student Details" SID into the "Level 2 Red Belt".

So I use this:

INSERT INTO [LEVEL 2 RED BELT]
(SID)
SELECT SID
FROM [STUDENT DETAILS]

Now, this fine the first time. But , If add another record into "Student Details", I get an error: "Unable to perform query as there are duplicate primary keys".

So I researched and understood that I needed to add a WHERE NOT clause. My new code now is:

INSERT INTO [LEVEL 2 RED BELT]
(SID)
SELECT SID
FROM [STUDENT DETAILS]
WHERE NOT ('[STUDENT DETAILS].SID'='[LEVEL 2 RED BELT].SID')

I tried this but got the same error.

I tried to use INSERT IGNORE, but it seems that Visual Basic Express 2010 does not like this command.

Have I done something obviously wrong?

Or is it a limitation of the Visual Basic Express 2010 application?

Thanks!

[BitBlt]

Hmm..."Where Not" is probably not a construct you want to use. We'll bypass that for the moment and get straight to your syntax.

Inside the "WHERE NOT" construct, you are surrounding you column names with single-quotes. That instructs the parser to treat them as literals rather than column names. Not good.

What you probably want to do is have something like this:

INSERT INTO [LEVEL 2 RED BELT]
(SID)
SELECT SID
FROM [STUDENT DETAILS]
WHERE [STUDENT DETAILS].SID NOT IN
(
select * from [LEVEL 2 RED BELT].SID
where [STUDENT DETAILS].SID = [LEVEL 2 RED BELT].SID
)

It's kind of a clumsy way to do it, but it will get the job done. A better way to do it is to take advantage of different join types like so:

INSERT INTO [LEVEL 2 RED BELT]
(SID)
SELECT SID
FROM [STUDENT DETAILS]
left join [LEVEL 2 RED BELT]
on [STUDENT DETAILS].SID = [LEVEL 2 RED BELT].SID
where [LEVEL 2 RED BELT].SID is NULL

What this method avoids is having to do 2 queries (one to get the data, one to test for existence of that data elsewhere). This may not make much difference in a small database, but can be very important in larger ones.

Hope these gave some help!

[kitjo]

possibly you can change the primary key because i understand if your using the sql light, it does not allow to duplicate the primary key
add another field of index which is auto incremented and will not be used in the display