Access SQL：基于技师表批量生成日志关联条目记录的方法问询

Alright, let's break this down into two reliable steps that should fix your issue. The key here is making sure you correctly capture the auto-generated dateID from your first insert, then use that ID to link all the new technician entries. Below are examples for the most common database systems:

Step 1: Insert Today's Date & Capture the New dateID #

First, we'll add today's date to tbl_dateLog and grab the unique ID that's generated for this entry. The method to get this ID varies by database:

For MySQL/MariaDB #

-- Insert today's date into the date log table (replace `date_column` with your actual date column name)
INSERT INTO tbl_dateLog (date_column)
VALUES (CURDATE());

-- Store the auto-generated ID in a variable
SET @new_date_id = 560817;

For SQL Server #

DECLARE @new_date_id INT;

-- Insert today's date (cast to DATE to remove time component)
INSERT INTO tbl_dateLog (date_column)
VALUES (CAST(GETDATE() AS DATE));

-- Get the ID of the newly inserted record (SCOPE_IDENTITY() ensures we get the ID from our insert, not another process)
SET @new_date_id = SCOPE_IDENTITY();

Step 2: Bulk Insert Entries for All Technicians #

Now we'll use the captured dateID to create a new entry in tbl_entries for every technician in tbl_technicians, setting totalClaims to 0.

For Both MySQL/MariaDB & SQL Server #

-- Insert a new entry for each technician, linking to the date log we just created
INSERT INTO tbl_entries (technicianID, totalClaims, dateID)
SELECT 
    id, -- Replace with your actual technician ID column name in tbl_technicians
    0,
    @new_date_id
FROM tbl_technicians;

Pro Tip: Wrap in a Transaction #

To avoid partial data (e.g., the date log is inserted but technician entries fail), wrap both steps in a transaction. This ensures either both steps succeed or neither does:

MySQL/MariaDB Transaction #

START TRANSACTION;

INSERT INTO tbl_dateLog (date_column)
VALUES (CURDATE());

SET @new_date_id = 560817;

INSERT INTO tbl_entries (technicianID, totalClaims, dateID)
SELECT id, 0, @new_date_id
FROM tbl_technicians;

COMMIT; -- If everything works, commit. Use ROLLBACK to undo if there's an error.

SQL Server Transaction #

BEGIN TRANSACTION;

DECLARE @new_date_id INT;

INSERT INTO tbl_dateLog (date_column)
VALUES (CAST(GETDATE() AS DATE));

SET @new_date_id = SCOPE_IDENTITY();

INSERT INTO tbl_entries (technicianID, totalClaims, dateID)
SELECT id, 0, @new_date_id
FROM tbl_technicians;

COMMIT TRANSACTION;

Common Pitfalls to Avoid #

• Incorrect Column Names: Double-check that date_column (in tbl_dateLog), id (in tbl_technicians), and technicianID/dateID (in tbl_entries) match your actual schema.
• Not Capturing the Right ID: Using functions like 560817 (MySQL) or SCOPE_IDENTITY() (SQL Server) ensures you get the ID from your specific insert, not another concurrent operation.
• Missing Transaction: Without a transaction, you could end up with a date log entry that has no matching technician entries if the second step fails.

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