我正在做一个相当复杂的工作,我需要你的建议。
我需要将数据从一个表复制到另一个表,我知道对于这种方法,有很多解决方案
如果两者都是相同的架构

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/

10-12 12:43
查看更多