批量生成数据SQL Server

Question

我需要在这个伪代码后面的fakeData表中创建假数据：

foreach(t1.id in table1)
   foreach(t2.id in table2)
      foreach(t3.id in table3)
        INSERT INTO fakeData (t1.id, t2.id, t3.id, random(30,80))

其中id是该表的主键。

我需要尽可能快地完成这项工作，因为我计划插入数十亿条记录。我不确定使用SQL语句是最好的方法，还是使用c#，或者将这些数据放入表中的最佳选择是什么。

这个问题实际上有两个部分，我如何在SQL Server中执行伪代码，以及最好的快速执行方法是什么。(我目前没有设置索引)

这看起来像是所有其他“大容量插入的最快方法”的翻版。我认为这个问题是不同的，因为我正在加载的数据实际上可以在我的SQL Server中生成，因此与BULK INSERT相比，批量生成

PS:我得到了SQL Server 2012

编辑:更多数据

这是一个星型架构。fakeData将成为事实数据表。

table2是一个20年的日期维度，有7300条记录。table3是一个包含96条记录的时间维度。table1是另一个拥有1亿条记录的维度。

Answer 1

通过使用BCP，您可以非常快速地插入数据。如果您的主键列是标识列，它仍然有效。如果不是，那么您可以跳过BCP部分，只使用while循环插入。

要创建BCP文件，请执行以下操作：

1. 在for循环中，使用固定行数(例如100万行)快速填充表。
2. BCP将数据输出到BCP文件中。
3. BCP根据需要多次返回数据

对每个必需的表执行此操作(下面的伪代码)：

示例：--如果这是dev机器，那么我会将恢复模式更改为简单，--如果还没有这样做，则减少日志记录量。然后把它改回原来的样子

BEGIN TRAN

declare @x int = 1
while @x <= 1000000
begin
    insert into t1 (col1, col2) values (rand() * 100, rand(123) * 100)

    if (@x % 10000) = 0
    begin
        COMMIT TRAN
        BEGIN TRAN
    end

    set @x = @x + 1
end

COMMIT TRAN

然后，(从命令提示符) bcp输出数据以创建您的bcp文件

bcp out [dbname].dbo.t1 c:\file1.bcp -T

现在，bcp (从命令提示符)根据需要多次输入数据

bcp in [dbname].dbo.t1 c:\file1.bcp -T
bcp in [dbname].dbo.t1 c:\file1.bcp -T
bcp in [dbname].dbo.t1 c:\file1.bcp -T
bcp in [dbname].dbo.t1 c:\file1.bcp -T
bcp in [dbname].dbo.t1 c:\file1.bcp -T

Answer 2

第一部分很简单；

Insert Into FakeData
Select T1.id as T1ID, T2.id as T2ID, T3.id as T3ID
From Table1 T1, Table2 T2, Table3 T3

这将执行三个表的笛卡尔连接，并返回所有可能的组合。但是，如果您谈论的是数十亿种可能的组合，那么批处理可能是一个好主意。我将使用偏移量fetch，因为它很容易理解，但您可能会找到一种性能更好的方法，我还将把结果存储在一个临时表中，所以您只运行一次连接，而rest是selects和inserts。

Declare @BatchSize int = 100000
Declare @RowsToBeInserted bigint = 
((select count(1) as t1count from Table1) * 
(select count(1) as t2count from Table2) * 
(select count(1) as t3count from Table3))
Declare @Iterations int = 0
Declare @CompletedRowCount bigint = 0

Select T1.id as T1ID, T2.id as T2ID, T3.id as T3ID
Into #TempTable
From Table1 T1, Table2 T2, Table3 T3

While @CompletedRowCount < @RowsToBeInserted
begin

Insert into FakeData
select T1ID, T2ID, T3ID
Order by T1ID, T2ID, T3ID
Offset @CompletedRowCount
Fetch next @BatchSize Only
set @Iterations = @Iterations + 1
set @CompletedRowCount = @Iterations * @BatchSize

end

这应该允许您拨入最适合您的服务器的批处理大小。在没有设置索引的情况下，您不需要删除它们并重新创建它们来提高性能。希望这能为你指明正确的方向。

Answer 3

好的那么..。因为没有人真正展示了如何处理随机值。到目前为止，我将贡献我的解决方案。我现在正在做这件事，以及简单的恢复模型：

BEGIN TRAN

declare @x int = 1
while @x <= 5000
begin
INSERT INTO dimSpeed
Select T1.id as T1ID, T2.DateValue as T2ID, T3.TIME_ID as T3ID, ABS(Checksum(NewID()) % 70) + 20
From lines T1, dimDate T2, dimTime T3
WHERE T1.id = @x AND T2.DateValue > '1/1/2015' AND T2.DateValue < '1/1/2016'

    if (@x % 100) = 0
    begin
        COMMIT TRAN
        BEGIN TRAN
    end

    set @x = @x + 1
end

COMMIT TRAN

其中，5000是我要插入的TABLE1 (t1)元素的数量。仅仅做5000就需要5分钟左右。按照这个速度，将需要70天的时间来插入我需要的所有数据。对于sure，需要更快的选项