SQL数据库批量插入数据
数据插入：
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
自己写的一个实验：
View Code
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
首先数据库中有条数据，再执行该循环，结果如下图：
