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

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

Post by anonymou » Fri, 10 Dec 2004 19:32:13


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

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

Post by Michel Wal » Fri, 10 Dec 2004 21:02:16

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

 
 
 

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

Post by Allen Brow » Fri, 10 Dec 2004 21:06:35

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://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

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

Post by anonymou » Fri, 10 Dec 2004 21:56:07

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

(up to the Where
separate strings, and
Me.SomeTextbox & """ " &


statement
 
 
 

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

Post by Allen Brow » Sat, 11 Dec 2004 00:28:08

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://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

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

Post by Michel Wal » Sat, 11 Dec 2004 00:37:00

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
 
 
 

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

Post by Allen Brow » Sat, 11 Dec 2004 01:01:07

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://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

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

Post by Michel Wal » Sat, 11 Dec 2004 02:55:40

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
 
 
 

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

Post by anonymou » Sat, 11 Dec 2004 14:15:36

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


price, for existing
but not previously in
queries: one update (with
here), and one insert


insert
Australia.
http://www.yqcomputer.com/
org.
 
 
 

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

Post by Michel Wal » Sat, 11 Dec 2004 20:27:18

i,


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




< XXXX@XXXXX.COM > wrote in message
news:2ac501c4de77$48591a50$ XXXX@XXXXX.COM ...