Insert Into with Where Clause

[andycambo via AccessMonster.com]

Private Sub Command93_Click()


I am currently getting a syntax error with the code below. I can’t figure
out where I am going wrong with the syntax. I haven’t used an INSERT INTO
statement with a WHERE clause before… is it even possible? I’ve tried
debugging it and it seems that the WHERE clause isn’t correct (it’s not
looking up the right value, actually none at all it just selecting ID)

Dim strSQL As String
Dim ID As Integer

ID = Me.bMatterID

strSQL = "INSERT INTO tblBilling"
strSQL = strSQL & " (mBillID) "
strSQL = strSQL & " VALUES " & Me.bID & ", WHERE (tblMatters.MatterID = ID)
;"

CurrentDb.Execute strSQL, dbFailOnError

Any help towards a solution will be much appreciated.

Thanks,
Andy.

 

[Scott Whetsell, A.S. - WVSP]

Try this line. I haven't actually tested it, but looking at your code, you
seem to have an extra comma before your WHERE statement and your ID value
isn't going to match because it is in the quotes, so it will read it as
literal. Also, I don't know if it's considered bad practice, but i never
place a semicolon after my SQL statements.

strSQL = "INSERT INTO tblBilling (mBillID) VALUES (" & Me.bID & ") WHERE
(tblMatters.MatterID = " & ID & ")"

 

[David H]

You can't use a WHERE statement as apart of a simple INSERT INTO statement.
Think about it - an INSERT INTO statement is adding records to a table, what
is there that the WHERE can act upon?

If you are selecting records from another table and need to insert them the
syntax would be...

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] = [criteria]

You just have to be careful that you have order of the fields match exactly
or you'll wind up twisting up the data and having values in the wrong fields.

 

[David H]

And if I've actually learned something, the following SQL statement should
only add records that haven't already been inserted.

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN SELECT [field1] FROM
[destination]

....and if that does work, I am showboating.

You can't use a WHERE statement as apart of a simple INSERT INTO statement.
Think about it - an INSERT INTO statement is adding records to a table, what
is there that the WHERE can act upon?

If you are selecting records from another table and need to insert them the
syntax would be...

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] = [criteria]

You just have to be careful that you have order of the fields match exactly
or you'll wind up twisting up the data and having values in the wrong fields.

Private Sub Command93_Click()


I am currently getting a syntax error with the code below. I can’t figure
out where I am going wrong with the syntax. I haven’t used an INSERT INTO
statement with a WHERE clause before… is it even possible? I’ve tried
debugging it and it seems that the WHERE clause isn’t correct (it’s not
looking up the right value, actually none at all it just selecting ID)

Dim strSQL As String
Dim ID As Integer

ID = Me.bMatterID

strSQL = "INSERT INTO tblBilling"
strSQL = strSQL & " (mBillID) "
strSQL = strSQL & " VALUES " & Me.bID & ", WHERE (tblMatters.MatterID = ID)
;"

CurrentDb.Execute strSQL, dbFailOnError

Any help towards a solution will be much appreciated.

Thanks,
Andy.

 

[John W. Vinson]

And if I've actually learned something, the following SQL statement should
only add records that haven't already been inserted.

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN SELECT [field1] FROM
[destination]

...and if that does work, I am showboating.

It'll work with one tweak: the syntactically required parentheses:

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN (SELECT [field1] FROM
[destination])

A different syntax that may be more efficient:

INSERT INTO destination ([field1], [field2], [field3])
SELECT source.field1, source.field2, source.field3
FROM source LEFT JOIN destination
ON source.field1 = destination.field1
WHERE destination.field1 IS NULL;

assuming field1 is the Primary Key of destination or has a unique index.

 

[Banana]

FWIW, while one can indeed use NOT IN to filter out the unmatched
records, it is usually the case that a equivalent frustrated outer join
will perform better.

INSERT INTO MyTarget (Field1, Field2, Field3)
SELECT s.Field1, s.Field2, s.Field3
FROM MySource s
LEFT JOIN MyTarget t

And if I've actually learned something, the following SQL statement should
only add records that haven't already been inserted.

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN SELECT [field1] FROM
[destination]

...and if that does work, I am showboating.

You can't use a WHERE statement as apart of a simple INSERT INTO statement.
Think about it - an INSERT INTO statement is adding records to a table, what
is there that the WHERE can act upon?

If you are selecting records from another table and need to insert them the
syntax would be...

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] = [criteria]

You just have to be careful that you have order of the fields match exactly
or you'll wind up twisting up the data and having values in the wrong fields.

Private Sub Command93_Click()


I am currently getting a syntax error with the code below. I can’t figure
out where I am going wrong with the syntax. I haven’t used an INSERT INTO
statement with a WHERE clause before… is it even possible? I’ve tried
debugging it and it seems that the WHERE clause isn’t correct (it’s not
looking up the right value, actually none at all it just selecting ID)

Dim strSQL As String
Dim ID As Integer

ID = Me.bMatterID

strSQL = "INSERT INTO tblBilling"
strSQL = strSQL & " (mBillID) "
strSQL = strSQL & " VALUES " & Me.bID & ", WHERE (tblMatters.MatterID = ID)
;"

CurrentDb.Execute strSQL, dbFailOnError

Any help towards a solution will be much appreciated.

Thanks,
Andy.

 

[Graham Mandeno]

Hi Andy

It makes no sense to have a WHERE clause if you do not also have a
SELECT...FROM statement.

Since you are using INSERT INTO with a VALUES clause, you are inserting a
single record with a literal value. Also, your list of values (in your case
only one) needs to be in parentheses.

Try this:

strSQL = "INSERT INTO tblBilling (mBillID)"
strSQL = strSQL & " VALUES (" & Me.bID & ");"

 

[David H]

You have to admit though that off the top of my head it wasn't bad for
someone whose SQL skills such as mine.

And if I've actually learned something, the following SQL statement should
only add records that haven't already been inserted.

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN SELECT [field1] FROM
[destination]

...and if that does work, I am showboating.

It'll work with one tweak: the syntactically required parentheses:

INSERT INTO [destination] ([field1],[field2],etc.) SELECT [field1],
[field2], etc FROM [source] WHERE [field1] NOT IN (SELECT [field1] FROM
[destination])

A different syntax that may be more efficient:

INSERT INTO destination ([field1], [field2], [field3])
SELECT source.field1, source.field2, source.field3
FROM source LEFT JOIN destination
ON source.field1 = destination.field1
WHERE destination.field1 IS NULL;

assuming field1 is the Primary Key of destination or has a unique index.

 

[John W. Vinson]

You have to admit though that off the top of my head it wasn't bad for
someone whose SQL skills such as mine.

I was impressed. Just didn't want the OP frustrated by a trivial syntax error.

 

[Krzysztof Naworyta]

John W. Vinson wrote:


(...)
| A different syntax that may be more efficient:
|
| INSERT INTO destination ([field1], [field2], [field3])
| SELECT source.field1, source.field2, source.field3
| FROM source LEFT JOIN destination
| ON source.field1 = destination.field1
| WHERE destination.field1 IS NULL;
|
| assuming field1 is the Primary Key of destination or has a unique
| index.

The most efficient way is to use [NOT] EXISTS:

INSERT INTO destination (field1, field2, field3)
SELECT field1, field2, field3
FROM source s
WHERE
NOT EXISTS
(Select * from destination d
Where
s.field1 = d.field1
)

of course if field1 is indexed in both tables...

 

[Krzysztof Naworyta]

Juzer Krzysztof Naworyta <[email protected]> napisa³
| John W. Vinson wrote:
|
|
| (...)
|| A different syntax that may be more efficient:
||
|| INSERT INTO destination ([field1], [field2], [field3])
|| SELECT source.field1, source.field2, source.field3
|| FROM source LEFT JOIN destination
|| ON source.field1 = destination.field1
|| WHERE destination.field1 IS NULL;
||
|| assuming field1 is the Primary Key of destination or has a unique
|| index.
|
| The most efficient way is to use [NOT] EXISTS:
|
| INSERT INTO destination (field1, field2, field3)
| SELECT field1, field2, field3
| FROM source s
| WHERE
| NOT EXISTS
| (Select * from destination d
| Where
| s.field1 = d.field1
| )
|
| of course if field1 is indexed in both tables...

To insert some value list we can use any table ("source", the better if it
is as small as possible):


INSERT INTO destination (field1, field2, field3)
SELECT DISTINCT
'A', 12.34, #2009/01/02#
FROM source s
WHERE
NOT EXISTS
(
Select * from destination d
Where
d.field1 = 'A'
)

 

[John W. Vinson]

The most efficient way is to use [NOT] EXISTS:

Thanks, Krzysztof! There's so many ways to do this, I really didn't know which
was best. Noted for future reference.

 

[Krzysztof Naworyta]

Juzer John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> napisal


(...)
|| The most efficient way is to use [NOT] EXISTS:

| Thanks, Krzysztof! There's so many ways to do this, I really didn't
| know which was best. Noted for future reference.


Do not believe in my words; maybe I'm wrong! Just test it!

 

[John W. Vinson]

Juzer John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> napisal


(...)
|| The most efficient way is to use [NOT] EXISTS:

| Thanks, Krzysztof! There's so many ways to do this, I really didn't
| know which was best. Noted for future reference.


Do not believe in my words; maybe I'm wrong! Just test it!

"Trust... but verify." - Ronald Reagan