Link to home
Start Free TrialLog in
Avatar of midfde
midfdeFlag for United States of America

asked on

Insert Into Table with Overwriting

What is the best (simplest and most reliable) way of implementing the following routine:
Sub insertWithOverwrite (sqlInsert as string)
' (1)Subroutine must execute the statement (Insert into [tableName]... Select..."), inserting into a certain table [b]all[/b] rows returned by given SELECT sql statement. [/indent]
'I know
DoCmd.RunSql  sqlInsert  
'(2) but it needs to be able to overwrite:
' If primary key conflict occurs then corresponding rows in [TableName] table 
' must be overwritten.[/indent]
end sub 

Open in new window

Avatar of bigbigpig
bigbigpig

Try the INSERT statement first.  If there is a primary key violation then catch it with an On Error and do an UPDATE statement on that primary key.
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of midfde

ASKER

Excellent input. Thanks.
It's not about my exact problem since, as -- I think -- I said, the sub DOES NOT KNOW anything but SQL INSERT... SELECT statement.
It may be something like:
INSERT INTO [Peak Periods]([Tot UseHrs],
[Mtr Or Group],
[Start],
[Stop],
[Units]) Select distinctrow TOP 10 Sum(UseHrs) * 0.25 AS [Tot UseHrs],
'AP_06A_CHW_Tons' As MtrKey,
CDate(Int([Date_Stamp]*(24/3))/(24/3)) AS Start,
[Start]+(1/(24/3)) AS Stop,
[Units] FROM [Peak Period Detail] GROUP BY [Mtr or Group], CDate(Int([Date_Stamp]*(24/3))/(24/3)), Units  ORDER BY Sum(UseHrs) DESC;