请求完善每日同步event表新记录至event_1表的T-SQL语句

Hey Jason, let's get that daily sync between your event and event_1 tables working properly!

First off, your original query has two key issues to fix:

1. SELECT ... INTO is typically used to create a new table with the results of the query, but you want to insert into an existing table (event_1). So we need to switch to INSERT INTO ... SELECT instead.
2. You can't directly use MAX(event_1.eventdate) in the WHERE clause—aggregate functions like MAX() need to be wrapped in a subquery here.

Correct Basic Query #

Here's the adjusted version that will insert all new records from event where EventDate is later than the most recent date in event_1:

INSERT INTO event_1
SELECT *
FROM event
WHERE event.EventDate > (SELECT MAX(eventdate) FROM event_1)

Handling Empty event_1 (First Sync) #

If event_1 is empty (like on your first run), MAX(eventdate) will return NULL, and event.EventDate > NULL won't match any records. To fix this, use COALESCE to fall back to a very early date, ensuring all existing event records get inserted:

INSERT INTO event_1
SELECT *
FROM event
WHERE event.EventDate > COALESCE((SELECT MAX(eventdate) FROM event_1), '1900-01-01')

(Adjust the fallback date to something that makes sense for your data.)

Pro Tips for Robustness #

• Avoid SELECT * long-term: If either table's schema changes later (like adding/removing columns), this query will break. Instead, explicitly list all columns:

  INSERT INTO event_1 (EventID, EventDate, EventName, ...)
  SELECT EventID, EventDate, EventName, ...
  FROM event
  WHERE event.EventDate > COALESCE((SELECT MAX(eventdate) FROM event_1), '1900-01-01')
  

• Use a primary key if possible: If your tables have an auto-incrementing primary key (like EventID), using that to check for new records is more reliable than EventDate (avoids issues if multiple records share the same date):

  INSERT INTO event_1
  SELECT *
  FROM event
  WHERE event.EventID > COALESCE((SELECT MAX(EventID) FROM event_1), 0)
  

• Schedule it: Set up a database job (like SQL Server Agent, MySQL Event Scheduler, or a cron job) to run this query daily automatically—no manual work needed!

内容的提问来源于stack exchange，提问作者Jason Smith