I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.
In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.
In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.In SQL Server (or in Access...) you are better off using a LEFT OUTER JOIN against your destination table, and filtering where the destination table key is null:
select [YourFields]...
from SourceTable
left outer join DestinationTable on SourceTable.PKey = DestinationTable.PKey
where Destination.PKey is null
You could also use NOT EXISTS, but I prefer this method.|||Thanks, that worked really well!
没有评论:
发表评论