I have three tables I'm dealing with for a signup form: [months], [members], and [months_members]. That last one is simply a join table. They look something like this:
我有三个表格要填写:[月]、[会员]和[月会员]。最后一个只是一个连接表。它们看起来是这样的:
[months]
========
[id] INT (Primary Key)
[name] VARCHAR(50)
[date] DATE
[members]
=========
[id] INT (Primary Key)
[fname] VARCHAR(50)
[lname] VARCHAR(50)
[email] VARCHAR(300)
[months_members]
================
[id] INT (Primary Key)
[month_id] INT
[member_id] INT
So, pretty simple, conceptually. There's a set list of members, and they can sign up for certain months. The form that I have, which allows them to sign up for different months, actually lets administrators enter (or change!) all of the signups all at once. And so I was hoping to use a MERGE statement to get the data into the database with a single query, rather than iterating through multiple queries.
所以,在概念上很简单,。有一组成员名单，他们可以注册几个月。我的表单允许他们注册不同的月份，实际上允许管理员一次输入(或更改)所有的签名。因此，我希望使用MERGE语句将数据以单个查询的形式输入到数据库中，而不是通过多个查询进行迭代。
So here's what I started to write (even though I know this is not correct syntax):
这是我开始写的东西(尽管我知道这不是正确的语法):
MERGE INTO [months_members] mm
INNER JOIN [months] mo ON mo.[id] = mm.[month_id]
USING (
SELECT 1 AS [month_id], 1 AS [member_id] UNION ALL
SELECT 2 AS [month_id], 3 AS [member_id] UNION ALL
SELECT 4 AS [month_id], 4 AS [member_id]
) AS u ON mm.[month_id] = u.[month_id] AND mm.[member_id] = u.[member_id]
WHEN NOT MATCHED THEN
INSERT ([month_id], [member_id]) VALUES (u.[month_id], u.[member_id])
WHEN NOT MATCHED BY SOURCE AND DATEPART(YEAR, mo.[start]) = 2013 THEN
DELETE;
Hopefully that illustrates the problem I'm running into. I want to insert any new data from the USING subquery into the [months_members] table. And I also want to delete anything from the [months_members] table that is not present in the USING subquery -- but only if it corresponds to a month in the current year. If there is data not present in the USING subquery that corresponds to a different year, I do not want to delete that. I want to leave that historical data alone. So the DATEPART condition is the kicker.
希望这能说明我遇到的问题。我想将use子查询中的任何新数据插入到[months_members]表中。而且，我还想删除在使用子查询中不存在的[months_members]表中的任何内容，但前提是它对应于当前年份中的一个月。如果USING子查询中没有对应于不同年份的数据，我不想删除它。我想把历史数据放在一边。所以DATEPART条件是踢球者。
So I'm inserting into one table, [months_members], but I also need it to be aware of the [months] table. Is there any way to accomplish this? Or will I have to concede and iterate through a number of SQL queries?
因此，我将插入到一个表中[months_members]，但我还需要它来了解[months]表。有什么方法可以做到这一点吗?或者我将不得不承认并迭代许多SQL查询?
1 个解决方案
#1
5
You can use a CTE in place of the target table (or a view). SQL Server can trace the updates through the CTE/view. Here is a hacked-together demo:
您可以使用CTE代替目标表(或视图)。SQL Server可以通过CTE/view跟踪更新。这里有一个hacked-together演示:
SELECT *
INTO #temp
FROM sys.objects
GO
;WITH cte AS (
SELECT t1.*
FROM #temp t1
JOIN #temp t2 ON t1.object_id = t2.object_id
)
MERGE cte USING (
SELECT 1 AS [month_id], 1 AS [member_id] UNION ALL
SELECT 2 AS [month_id], 3 AS [member_id] UNION ALL
SELECT 4 AS [month_id], 4 AS [member_id]
) AS u ON cte.object_id = u.[month_id]
WHEN MATCHED THEN DELETE;
#1
5
You can use a CTE in place of the target table (or a view). SQL Server can trace the updates through the CTE/view. Here is a hacked-together demo:
您可以使用CTE代替目标表(或视图)。SQL Server可以通过CTE/view跟踪更新。这里有一个hacked-together演示:
SELECT *
INTO #temp
FROM sys.objects
GO
;WITH cte AS (
SELECT t1.*
FROM #temp t1
JOIN #temp t2 ON t1.object_id = t2.object_id
)
MERGE cte USING (
SELECT 1 AS [month_id], 1 AS [member_id] UNION ALL
SELECT 2 AS [month_id], 3 AS [member_id] UNION ALL
SELECT 4 AS [month_id], 4 AS [member_id]
) AS u ON cte.object_id = u.[month_id]
WHEN MATCHED THEN DELETE;