SQL数据库批量插入数据

ゞ浴缸里的玫瑰 2021-12-15 11:47 403阅读 0赞

数据插入：  
  
1、事实数据插入：通过DTS加调度实现将sales\_fact\_1997的数据进行复制。频率为每分钟10000条。主要用到的SQL语句：select top 10000 \* from sales\_fact\_1997  
  
2、维度数据插入：通过SQL语句插入数据到time\_by\_day.  
  
测试用到的SQL语句：  
  
1、单条插入  
  
  
INSERT INTO time\_by\_day  
  
(time\_id, the\_date, the\_year, month\_of\_year, quarter,day\_of\_month)  
  
VALUES ('1101', '1999-10-1', '1999', '10', 'Q4','1')  
  
  
2、单条插入:  
  
  
INSERT INTO time\_by\_day  
  
(time\_id, the\_date, the\_year, month\_of\_year, quarter, day\_of\_month)  
  
SELECT TOP 1 time\_id + 1 AS time\_id, the\_date + 1 AS the\_date, YEAR(the\_date + 1)  
  
AS the\_year, MONTH(the\_date + 1) AS month\_of\_year, \{ fn QUARTER(the\_date + 1)  
  
\} AS quarter, DAY(the\_date + 1) AS day\_of\_month  
  
FROM time\_by\_day  
  
ORDER BY time\_id DESC  
  
  
3、循环插入：  
  
  
DECLARE @MyCounter INT  
  
SET @MyCounter = 0 /\*设置变量\*/  
  
WHILE (@MyCounter < 2) /\*设置循环次数\*/  
  
BEGIN  
  
WAITFOR DELAY '000:00:10' /\*延迟时间10秒\*/  
  
INSERT INTO time\_by\_day  
(time\_id, the\_date, the\_year, month\_of\_year, quarter, day\_of\_month)  
  
SELECT TOP 1 time\_id + 1 AS time\_id, the\_date + 1 AS the\_date, YEAR(the\_date + 1)  
  
AS the\_year, MONTH(the\_date + 1) AS month\_of\_year, \{ fn QUARTER(the\_date + 1)  
  
\} AS quarter, DAY(the\_date + 1) AS day\_of\_month  
  
FROM time\_by\_day  
  
ORDER BY time\_id DESC  
  
  
SET @MyCounter = @MyCounter + 1  
  
END  
  
  
4、插入以时间为变量的数据  
  
  
DECLARE @MyCounter INT  
  
declare @the\_date datetime  
  
SET @MyCounter = 0  
  
SET @the\_date = '1999-1-4'  
  
WHILE (@MyCounter < 200000)  
  
BEGIN  
  
WAITFOR DELAY '000:00:10'  
  
/\*INSERT INTO time\_by\_day  
  
(time\_id, the\_date, the\_year, month\_of\_year, quarter, day\_of\_month)  
  
SELECT TOP 1 time\_id + 1 AS time\_id, the\_date + 1 AS the\_date, YEAR(the\_date + 1)  
  
AS the\_year, MONTH(the\_date + 1) AS month\_of\_year, \{ fn QUARTER(the\_date + 1)  
  
\} AS quarter, DAY(the\_date + 1) AS day\_of\_month  
  
FROM time\_by\_day  
  
ORDER BY time\_id DESC  
  
\*/  
  
insert into time\_by\_day (time\_id,the\_date)values('371',@the\_date)  
  
SET @the\_date = @the\_date + 1  
  
SET @MyCounter = @MyCounter + 1  
  
END

转自：[http://www.cnblogs.com/senzjx/archive/2010/01/25/1656189.html][http_www.cnblogs.com_senzjx_archive_2010_01_25_1656189.html]

自己写的一个实验：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1 DECLARE  @mycount  INT   
  2  SET  @mycount = **0**   
  3  WHILE  ( @mycount < **9** )  
  4  BEGIN   
  5  WAITFOR  DELAY  ' 000:00:10 '   
  6  INSERT  INTO  dbo. \[ User \]   
  7  ( ID ,  
  8  FristName ,  
  9  LastName ,  
 10  DepartName ,  
 11  City  
 12  )  
 13  SELECT  TOP  **1**  ID \+ **1**  ,  \--  ID - int   
 14   ' zhou ' \+ CONVERT ( NVARCHAR , @mycount ) ,  \--  FristName - varchar(50)   
 15   ' xiuguan ' \+ CONVERT ( NVARCHAR , @mycount ),  \--  LastName - varchar(50)   
 16   ' test ' \+ CONVERT ( NVARCHAR , @mycount ),  \--  DepartName - varchar(50)   
 17   ' wuqiao ' \+ CONVERT ( NVARCHAR , @mycount )  \--  City - varchar(50)   
 18   FROM  dbo. \[ User \]  ORDER  BY  ID  DESC   
 19   
 20  SET  @mycount =  @mycount \+ **1**   
 21  END

首先数据库中有条数据，再执行该循环，结果如下图：

![2011042011431277.jpg][]

转载于:https://www.cnblogs.com/zhouxiuquan/archive/2011/04/20/2022117.html

[http_www.cnblogs.com_senzjx_archive_2010_01_25_1656189.html]: http://www.cnblogs.com/senzjx/archive/2010/01/25/1656189.html
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211214/58c1a185e0e84107903e54318e412778.png
[2011042011431277.jpg]: /images/20211214/02ecca7aad19433c89617af601bb057d.png