循环插入: 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
数据哪来的?凭空生成? IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 VARCHAR(50),COL2 VARCHAR(50),COL3 VARCHAR(50)) INSERT INTO TB SELECT TOP 1000000 NEWID(),NEWID(),NEWID() FROM MASTER..SPT_VALUES T1 INNER JOIN MASTER..SPT_VALUES T2 ON 1=1 INNER JOIN MASTER..SPT_VALUES T3 ON 1=1 --1分28秒
我发现一个问题 一般 CSDN 有好几个星星的ID ,CSDN 时时在线 ,呵呵
select a.name,a.column_id,a.system_type_id into #t from sys.columns a, sys.columns b,sys.columns c
鸭子试试select into 据说比insert into 快 呵呵你测一下 告诉我
SELECT checksum(newid()) FROM syscolumns c1, syscolumns c2, syscolumns c3
SQL 一般总是完整 的记录修改操作,但是对数据库恢复模式不是FULL而且执行的是BULK操作的时候,它总是以最小的方式记录.BULK操作包括:创建和重新生产索引,使用BULK引起插入,select into ,LOB(大型对象)的操作. 设计插入时候:先考虑BULK操作;再考虑基于集合的多行INSERT(insert select );最后才选择单行的INSERT 。所以你的假设是对的
如果是连续的100W 下面2个方法 其中第二种要快 而且灵活 /* 生产一百万连续数据之临时表 */ SET NOCOUNT ON CREATE TABLE NUM(N INT PRIMARY KEY) INSERT NUM VALUES(1); DECLARE @MAX INT,@RC INT SET @MAX=1000000 SET @RC=1 WHILE @RC*2<=@MAX BEGIN INSERT NUM SELECT N+@RC FROM NUM SET @RC=@RC*2 END INSERT NUM SELECT N+@RC FROM NUM WHERE N+@RC<=@MAX SELECT * FROM NUM go ----------------- /* 函数生产100W连续数据 */ 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<=@nselect * from dbo.fn_nums(1000000)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 VARCHAR(50),COL2 VARCHAR(50),COL3 VARCHAR(50))
INSERT INTO TB
SELECT TOP 1000000 NEWID(),NEWID(),NEWID()
FROM MASTER..SPT_VALUES T1
INNER JOIN MASTER..SPT_VALUES T2 ON 1=1
INNER JOIN MASTER..SPT_VALUES T3 ON 1=1
--1分28秒
from sys.columns a, sys.columns b,sys.columns c
据说比insert into 快 呵呵你测一下 告诉我
FROM syscolumns c1, syscolumns c2, syscolumns c3
SQL 一般总是完整 的记录修改操作,但是对数据库恢复模式不是FULL而且执行的是BULK操作的时候,它总是以最小的方式记录.BULK操作包括:创建和重新生产索引,使用BULK引起插入,select into ,LOB(大型对象)的操作.
设计插入时候:先考虑BULK操作;再考虑基于集合的多行INSERT(insert select );最后才选择单行的INSERT 。所以你的假设是对的
/*
生产一百万连续数据之临时表
*/
SET NOCOUNT ON
CREATE TABLE NUM(N INT PRIMARY KEY)
INSERT NUM VALUES(1);
DECLARE @MAX INT,@RC INT
SET @MAX=1000000
SET @RC=1
WHILE @RC*2<=@MAX
BEGIN
INSERT NUM
SELECT N+@RC FROM NUM
SET @RC=@RC*2
END
INSERT NUM
SELECT N+@RC FROM NUM WHERE N+@RC<=@MAX
SELECT * FROM NUM
go
-----------------
/*
函数生产100W连续数据
*/
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<=@nselect * from dbo.fn_nums(1000000)
比如就可以不用spt_values了。也可以打破它的局限性