我正在做一个相当复杂的工作,我需要你的建议。
我需要将数据从一个表复制到另一个表,我知道对于这种方法,有很多解决方案
如果两者都是相同的架构
INSERT INTO newTable
SELECT * FROM oldTable
如果两者都是不同的模式
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable
也可以使用
SQL Cursor
,并且存在更多的变化。但让我总结一下问题:
问题
我有一个csv文件,里面有大约150万条记录。每个记录当前有6个正在导入的字段。
现在,为了将csv文件中的数据插入到sql server中,我将c与entity framework结合使用。
为了提高性能,我将把所有这些记录插入一个临时表中。
这是临时表的架构:
CREATE TABLE [dbo].[TEMP_GENERIC_ARTICLE](
[Id] [int] IDENTITY(1,1) NOT NULL,
[GlnCode] [nvarchar](100) NULL,
[Description] [nvarchar](max) NULL,
[VendorId] [nvarchar](100) NULL,
[VendorName] [nvarchar](100) NULL,
[ItemNumber] [nvarchar](100) NULL,
[ItemUOM] [nvarchar](max) NULL,
[DateCreatedInternal] [datetime] NOT NULL,
[DateUpdatedInternal] [datetime] NOT NULL,
CONSTRAINT [PK_dbo.TEMP_GENERIC_ARTICLE] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
然后我确实有一个表,另一个应用程序将使用它,称为
T_GENERIC_ARTICLE
哪个模式是:CREATE TABLE [dbo].[T_GENERIC_ARTICLE](
[GlnCode] [nvarchar](100) NOT NULL,
[Description] [nvarchar](max) NULL,
[VendorId] [nvarchar](100) NOT NULL,
[VendorName] [nvarchar](100) NULL,
[ItemNumber] [nvarchar](100) NOT NULL,
[ItemUOM] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.T_GENERIC_ARTICLE] PRIMARY KEY CLUSTERED
(
[GlnCode] ASC,
[VendorId] ASC,
[ItemNumber] ASC,
[ItemUOM] ASC
)
因此,真正的表不再具有“id”字段,而是具有主键,主键跨越数据库中的4列。
现在,我想做的是:
例如,一旦数据存储在
Temp
表中,或者每1000条记录中,我就需要运行一个sql存储过程,它将数据从temp
表复制到destination
表中。在副本上,我需要检查一个带有主键的记录是否已经存在。如果是这种情况,那么我想更新记录,否则我想插入一个新记录。
复制完成后,我想删除
temp
表中的所有记录。问题
对于这样一个大的数据集(150万条记录),为了尽可能高效、快速地将记录从
temp
表传输到destination
表,最好的工作方法是什么?我从来没有处理过这么大的数据集,所以我真的需要一些建议。
亲切的问候
最佳答案
使用MERGE
将数据从临时表复制到主表,处理1.5m行应该没有问题:
MERGE [dbo].[T_GENERIC_ARTICLE] AS t
USING [TEMP_GENERIC_ARTICLE] AS s
ON s.GlnCode = t.GlnCode
AND s.VendorId = t.VendorId
AND s.ItemNumber = t.ItemNumber
AND s.ItemUOM = t.ItemUOM
WHEN MATCHED THEN UPDATE
SET Description = s.Description,
VendorName = s.VendorName
WHEN NOT MATCHED THEN
INSERT (GlnCode, Description, VendorId, VendorName, ItemNumber, ItemUOM)
VALUES (s.GlnCode, s.Description, s.VendorId, s.VendorName, s.ItemNumber, s.ItemUOM);
ssis适合插入,但它不能很好地处理upsert,因为oledb命令任务操作rbar,所以插入到临时表并使用merge比直接插入到主表要高效得多。
编辑
要回答评论中的问题:
在插入每个记录之前检查一下吗?还是只在第一次检查?我的意思是,如果合并命令首先插入一个记录,下面的记录将检查它是否存在于目标表中(因为以前的合并语句已经添加了一个新记录)?
它首先进行检查,但是如果源表中有两个匹配的行,则合并将失败,并显示错误消息
merge语句多次尝试更新或删除同一行。当目标行与多个源行匹配时会发生这种情况。merge语句不能多次更新/删除目标表的同一行。优化ON子句以确保目标行最多与一个源行匹配,或使用GROUPBY子句对源行进行分组。
如果在临时表中有重复行,则在执行合并之前需要对它们进行删除,因此,如果只从临时表中获取最新记录,则可以使用
NOT EXISTS
对相同密钥存在新记录的记录进行删除:WITH LatestTempGenericArticle AS
(
SELECT GlnCode, Description, VendorId, VendorName, ItemNumber, ItemUOM
FROM dbo.[TEMP_GENERIC_ARTICLE] AS t
WHERE NOT EXISTS
( SELECT 1
FROM dbo.[TEMP_GENERIC_ARTICLE] AS t2
WHERE t2.GlnCode = t.GlnCode
AND t2.VendorId = t.VendorId
AND t2.ItemNumber = t.ItemNumber
AND t2.ItemUOM = t.ItemUOM
AND t2.DateCreatedInternal > t.DateCreatedInternal
)
)
MERGE [dbo].[T_GENERIC_ARTICLE] AS t
USING LatestTempGenericArticle AS s
ON s.GlnCode = t.GlnCode
AND s.VendorId = t.VendorId
AND s.ItemNumber = t.ItemNumber
AND s.ItemUOM = t.ItemUOM
WHEN MATCHED THEN UPDATE
SET Description = s.Description,
VendorName = s.VendorName
WHEN NOT MATCHED THEN
INSERT (GlnCode, Description, VendorId, VendorName, ItemNumber, ItemUOM)
VALUES (s.GlnCode, s.Description, s.VendorId, s.VendorName, s.ItemNumber, s.ItemUOM);
关于c# - 如何在Microsoft SQL Server中将数据从一个表复制到另一个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29668665/