insert sql

如何一次插入多条记录的SQL语句？ 

在使用SQL数据库的时候，我们也许会需要一次像数据库中添加多条记录，那么我们可以使用SQL语句来实现，该语句具体如下：
--添加一条记录    
INSERT INTO tableName(col1,col2,col3) VALUES (1,2,3)    
   
--添加多条记录    
INSERT INTO tableName(col1,col2,col3)    
SELECT 3,4,5    
UNION ALL   
SELECT 6,7,8    
   
--从另外的一张表中读取多条数据添加到新表中    
INSERT INTO tableName(col1,col2,col3)     
SELECT a,b,c FROM tableA    
   
--从其他的多张表中读取数据添加到新表中    
INSERT INTO tableName(col1,col2,col3)    
SELECT a,b,c FROM tableA WHERE a=1    
UNION ALL   
SELECT a,b,c FROM tableB WHERE a=2   
上边代码中的into都可以省略！
上边代码中的union all如果换成union，则相同记录只插入一次，不会重复插入。
另外一种方法是SQL Server2008特有的，所以，如果你不是SQL Server2008，就不能使用这种方法了。  
INSERT INTO MyTable(ID,NAME)VALUES(7,'003'),(8,'004'),(9,'005')
create table [TEST]
(
 [NUM_ID] int primary key
)
go
declare @temp int
set @temp=1;
while @temp<=1000000 
begin
insert into [TEST]([NUM_ID]) values(@temp)
set @temp=@temp+1;
end
go
----------------------------------------------------------
--试试下面的方法
--2005
DECLARE @n AS BIGINT;
SET @n = 1000000;
WITH Base AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n))
),
Expand AS
(
  SELECT 1 AS c
  FROM Base AS B1, Base AS B2
),
Nums AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
  FROM Expand
)
SELECT n FROM Nums WHERE n <= @n
OPTION(MAXRECURSION 0);
--2
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;
GO
--2000  这个会比前两个慢，但是前两个2000不能用
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;