如何使用具有相同列名的查询构建器合并5个数据表 [英] How do I merge 5 data tables using query builder with same column name
问题描述
你好，我想问一下，我是否有5个表名相同，但记录不同，
我可以将所有表中的记录添加到1个表中并显示它吗？
我尝试了什么：
i尝试查询构建器，但我不知道，所以有些人可以帮我这个或建议任何东西
hello, i want to ask if i have 5 tables with same column names but different records,
can i add the records form all tables into 1 table and display it?
What I have tried:
i tried query builder but i didn't know , so some can plz help me with this or suggest anything
推荐答案
我有5个具有相同列名但不同记录的表，
我可以将所有表中的记录添加到1个表中并显示它吗？
i have 5 tables with same column names but different records,
can i add the records form all tables into 1 table and display it?
如果有5个具有相同列的表名字但不同的记录然后您所拥有的是一个非常糟糕的数据库设计。
重新访问您的数据库设计。
例如：您可能有一些表，如 [学生] ， [教师] ， [Parent] ， [CateringStaff] 等
所有这些表都会有类似的东西e [FirstName]，[LastName]，[DateOfBirth]等等。所有相同。
更好的设计是有一个名为 [Person] 的表，其中包含列[FirstName]，[LastName]，[DateOfBirth]等加上一个附加列 [PersonType] ，可以链接到另一个表
If you have 5 tables with the same column names but different records then what you do have is a very bad database design.
Revisit your database design.
For example: You might have some tables like [Student], [Teacher], [Parent],[CateringStaff] etc.
All of those tables will have things like [FirstName],[LastName],[DateOfBirth], etc etc. All the same.
The better design is to have a table called [Person] with columns like [FirstName],[LastName],[DateOfBirth], etc etc. plus an additional column [PersonType] which can link to another table
Create table [PersonType](Id int, PersonType nvarchar(125))后一个表有一行学生 ，老师，家长，餐饮服务员等等。
如果你想让你的数据库中的所有人都是一个简单的单桌查询：
This latter table has a row for "Student", "Teacher", "Parent", "Caterer" etc etc.
If you want to get all the people on your database it is a simple one-table query :
SELECT * FROM Person如果你希望获得所有教师这是另一个简单的查询：
If you want to get all of the teachers it is another simple query:
SELECT * FROM Person P
INNER JOIN PersonType PT on P.PersonType = PT.Id
WHERE PT.PersonType = 'Teacher'等等。
如果你真的，真的想保留你的五张桌子（请不要这样做）然后除了使用UNION之外你没有办法。但是，您不必须使用临时表，并且您绝对不需要使用MERGE。 UNION ALL 不会删除重复的行，但 UNION 将会删除。所以你需要的只是
and so forth.
If you really, really want to keep your five tables (please don't do it that way) then you have no recourse other than to use UNION. However, you do not have to use a temporary table, and you absolutely do not need to use MERGE. UNION ALL does not remove duplicate rows but UNION will. So ALL you need is
SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3
UNION
SELECT * FROM Table4
UNION
SELECT * FROM Table5
（在这种情况下你应该永远不要使用SELECT *，请列出你的列明确地想要）
(With the caveat that you should never use SELECT * in situations like this, list the columns you want explicitly)
不需要临时表
- Craete一个临时表。
IF OBJECT_ ID（'tempdb ..＃tmpTemp'）IS NOT NULL
BEGIN
DROP TABLE #tmpTemp;
END
CREATE TABLE #tmpTemp（
ID INT身份（1,1）
，YourColumn VARCHAR（200）
）
---将所有表记录插入此临时表中
INSERT INTO #tmpTemp（YourColumn）
SELECT Column1
来自FirstTable
UNION
SELECT第1栏
来自SecondTable
UNION
SELECT Column1
FROM ThirdTable
UNION
SELECT Column1
FROM FourthTable
--------------------
MERGE YourMainTable AS T
使用#tmpTemp AS S
ON（T.YourMainColumnName = S.YourColumn）
当没有与目标比赛时
那么
INSERT（YourMainColumnName）
VALUES（S.YourColumn）
匹配的时候
那么
更新
SET T.YourMainColumnName = S.YourColumn
OUTPUT
--Craete one temp table.
IF OBJECT_ID('tempdb..#tmpTemp') IS NOT NULL
BEGIN
DROP TABLE #tmpTemp;
END
CREATE TABLE #tmpTemp (
ID INT identity(1, 1)
,YourColumn VARCHAR(200)
)
--- Insert all table records into this temp table
INSERT INTO #tmpTemp (YourColumn)
SELECT Column1
FROM FirstTable
UNION
SELECT Column1
FROM SecondTable
UNION
SELECT Column1
FROM ThirdTable
UNION
SELECT Column1
FROM FourthTable
--------------------
MERGE YourMainTable AS T
USING #tmpTemp AS S
ON (T.YourMainColumnName = S.YourColumn)
WHEN NOT MATCHED BY TARGET
THEN
INSERT (YourMainColumnName)
VALUES (S.YourColumn)
WHEN MATCHED
THEN
UPDATE
SET T.YourMainColumnName = S.YourColumn
OUTPUT
这篇关于如何使用具有相同列名的查询构建器合并5个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
