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

[Guest]

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

 

[Michel Walsh]

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

 

[Allen Browne]

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

 

[Guest]

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

 

[Allen Browne]

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.

 

[Michel Walsh]

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

 

[Allen Browne]

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.

 

[Michel Walsh]

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

 

[Guest]

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

 

[Michel Walsh]

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