RE: Insert Into with Where Clause

  • Thread starter Thread starter David H
  • Start date Start date
D

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.
 
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.

David H said:
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.

andycambo via AccessMonster.com said:
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)

Code: 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...

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.
 
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.

David H said:
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.

andycambo via AccessMonster.com said:
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)

Code: 
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.
Click to expand...
Click to expand...
Click to expand...
 
You have to admit though that off the top of my head it wasn't bad for
someone whose SQL skills such as mine.

John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
You have to admit though that off the top of my head it wasn't bad for
someone whose SQL skills such as mine.
Click to expand...

I was impressed. Just didn't want the OP frustrated by a trivial syntax error.
 
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...
 
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'
)
 
The most efficient way is to use [NOT] EXISTS:
Click to expand...

Thanks, Krzysztof! There's so many ways to do this, I really didn't know which
was best. Noted for future reference.
 
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!
 
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!
Click to expand...

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