how to insert a where clause in Insert Into... statement?

how to insert a where clause in Insert Into... statement

Hi,



What you are inserting starts with a standard SELECT ... which can
have the standard WHERE clause.

Note that the WHERE clause is applied to limit what you inserted, not to
specify a position of the insertion has to be done, since tables do not have
"position" (for their records). Rercordsets have a position (move next, move
previous), but tables don't.



Hoping it may help,
Vanderghast, Access MVP



wrote in message
...
how to insert a where clause in Insert Into... statement

In what context?

In code, you might store the stub of of the statement (up to the Where
clause) and any tail (after the Where clause) as two separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ & Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
how to insert a where clause in Insert Into... statement

thanx Allen,
actually i have a table with some datas, i want to insert
some more datas in that table through an append query,
but i want that new datas should come with the old data
like:
field1 of table(prev)data abcd & field2 is blank but
after running the append query field1 data abcd field2
data 1234.
how to do that??

-----Original Message-----
In what context?

In code, you might store the stub of of the statement
(up to the Where
clause) and any tail (after the Where clause) as two
separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ &
Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
how to insert a where clause in Insert Into...
statement


.

So you want some records to update, and others to append?

You will need to execute 2 queries: one for the INSERT, and one for the
UPDATE.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
thanx Allen,
actually i have a table with some datas, i want to insert
some more datas in that table through an append query,
but i want that new datas should come with the old data
like:
field1 of table(prev)data abcd & field2 is blank but
after running the append query field1 data abcd field2
data 1234.
how to do that??

-----Original Message-----
In what context?

In code, you might store the stub of of the statement
(up to the Where
clause) and any tail (after the Where clause) as two
separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ &
Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

wrote in message
...
how to insert a where clause in Insert Into...
statement

Hi,


An append-update is doable in Jet (not in MS SQL Server):


UPDATE oldInventory As o RIGHT JOIN newList As n
ON o.ItemID = n.ItemID
SET o.ItemID = n.ItemID,
o.UnitPrice= n.UnitPrice,
o.whatever = n.whatever



After the update, the oldinventory will got the new price, for existing
data, and will also got the brand new items in newList but not previously in
oldInventory. In MS SQL Server, you have to do two queries: one update (with
an inner join instead of the outer join illustrated here), and one insert
query to insert the new stuff).

Hoping it may help,
Vanderghast, Access MVP



wrote in message
...
thanx Allen,
actually i have a table with some datas, i want to insert
some more datas in that table through an append query,
but i want that new datas should come with the old data
like:
field1 of table(prev)data abcd & field2 is blank but
after running the append query field1 data abcd field2
data 1234.
how to do that??

-----Original Message-----
In what context?

In code, you might store the stub of of the statement
(up to the Where
clause) and any tail (after the Where clause) as two
separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ &
Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
how to insert a where clause in Insert Into...
statement


.

Michel, that's a wild idea. I like it.

Does not seem to work if ItemID is an AutoNumber though.
JET complains that the autonum field is not updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,

An append-update is doable in Jet (not in MS SQL Server):

UPDATE oldInventory As o RIGHT JOIN newList As n
ON o.ItemID = n.ItemID
SET o.ItemID = n.ItemID,
o.UnitPrice= n.UnitPrice,
o.whatever = n.whatever

After the update, the oldinventory will got the new price, for existing
data, and will also got the brand new items in newList but not previously
in oldInventory. In MS SQL Server, you have to do two queries: one update
(with an inner join instead of the outer join illustrated here), and one
insert query to insert the new stuff).

Hoping it may help,
Vanderghast, Access MVP

Hi Allen,


Indeed, if an autonumber is used, in old inventory, to validate the
"key", that creates a problem in that case.



Vanderghast, Access MVP


"Allen Browne" wrote in message
...
Michel, that's a wild idea. I like it.

Does not seem to work if ItemID is an AutoNumber though.
JET complains that the autonum field is not updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,
how to do it in SQL SERVER?? as i am trying to the data
present in SQL SERVER only.


-----Original Message-----
Hi,


An append-update is doable in Jet (not in MS SQL Server):


UPDATE oldInventory As o RIGHT JOIN newList As n
ON o.ItemID = n.ItemID
SET o.ItemID = n.ItemID,
o.UnitPrice= n.UnitPrice,
o.whatever = n.whatever



After the update, the oldinventory will got the new
price, for existing
data, and will also got the brand new items in newList
but not previously in
oldInventory. In MS SQL Server, you have to do two
queries: one update (with
an inner join instead of the outer join illustrated
here), and one insert
query to insert the new stuff).

Hoping it may help,
Vanderghast, Access MVP



wrote in message
...
thanx Allen,
actually i have a table with some datas, i want to
insert
some more datas in that table through an append query,
but i want that new datas should come with the old data
like:
field1 of table(prev)data abcd & field2 is blank but
after running the append query field1 data abcd field2
data 1234.
how to do that??

-----Original Message-----
In what context?

In code, you might store the stub of of the statement
(up to the Where
clause) and any tail (after the Where clause) as two
separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ &
Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

wrote in message
.. .
how to insert a where clause in Insert Into...
statement


.



.

Hi,


You can update through a view (that makes the inner join ON itemID) or, if
you don't want to make a view just for that, you can still do (it is a
little bit different than with Jet):


UPDATE oldInventory
SET oldInventory.whatever= n.whatever
FROM newData As n
WHERE n.itemID=oldInventory.itemID




The insert is something that could look like:



INSERT INTO oldInventory(ItemID, field2, field3)
SELECT newData.ItemID, newData.field2, newData.field3
FROM newData LEFT JOIN oldInventory ON
newData.ItemID = oldInventory.itemID
WHERE oldInventory.ItemID IS NULL



Hoping it may help,
Vanderghast, Access MVP




wrote in message
...
Hi,
how to do it in SQL SERVER?? as i am trying to the data
present in SQL SERVER only.


-----Original Message-----
Hi,


An append-update is doable in Jet (not in MS SQL Server):


UPDATE oldInventory As o RIGHT JOIN newList As n
ON o.ItemID = n.ItemID
SET o.ItemID = n.ItemID,
o.UnitPrice= n.UnitPrice,
o.whatever = n.whatever



After the update, the oldinventory will got the new
price, for existing
data, and will also got the brand new items in newList
but not previously in
oldInventory. In MS SQL Server, you have to do two
queries: one update (with
an inner join instead of the outer join illustrated
here), and one insert
query to insert the new stuff).

Hoping it may help,
Vanderghast, Access MVP



wrote in message
...
thanx Allen,
actually i have a table with some datas, i want to
insert
some more datas in that table through an append query,
but i want that new datas should come with the old data
like:
field1 of table(prev)data abcd & field2 is blank but
after running the append query field1 data abcd field2
data 1234.
how to do that??

-----Original Message-----
In what context?

In code, you might store the stub of of the statement
(up to the Where
clause) and any tail (after the Where clause) as two
separate strings, and
insert your WHERE clause at runtime.

Function DoAnUpdate()
Dim strSql As String
Const strcStub = "INSERT ..."
Const strcTail = "ORDER BY MyField;"

strSql = strcStub & " WHERE SomeField = """ &
Me.SomeTextbox & """ " &
strcTail
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

wrote in message
. ..
how to insert a where clause in Insert Into...
statement


.



.