--测试的数据
print(convert(varchar(120),getdate(),120))
declare @i int
set @i=0
while(@i<100000)
begin
insert into jin values(@i)
set @i=@i+1
end
print(convert(varchar(120),getdate(),120))
--执行的结果
/*
2010-02-05 11:12:36
受影响行数(100000)
2010-02-05 11:13:21*/
我想问的是sql当中有没有什么方法,
可以节省大数据(一起)插入表中的时间。
谢谢给位老大,这个有可行性吗?(前几天看见JAVA在代码中可以实现,效率很高。不知道.net中是否也有类似的函数。)
print(convert(varchar(120),getdate(),120))
declare @i int
set @i=0
while(@i<100000)
begin
insert into jin values(@i)
set @i=@i+1
end
print(convert(varchar(120),getdate(),120))
--执行的结果
/*
2010-02-05 11:12:36
受影响行数(100000)
2010-02-05 11:13:21*/
我想问的是sql当中有没有什么方法,
可以节省大数据(一起)插入表中的时间。
谢谢给位老大,这个有可行性吗?(前几天看见JAVA在代码中可以实现,效率很高。不知道.net中是否也有类似的函数。)
create table tb(n int)
go
set nocount on
--测试的数据
print(convert(varchar(120),getdate(),120))
declare @i int
declare @n intselect @i=1
select @n=@iinsert into tb values(1)while @n*2<=100000
begin
insert into tb(n)
select @n+n from tb
select @n=@n*2
end insert into tb(n)
select @n+n from tb where @n+n<=100000
print(convert(varchar(120),getdate(),120))
drop table tb/*
2010-02-05 11:29:45
2010-02-05 11:29:47
*/
create table tb(n int)
go
set nocount on
--测试的数据
print(convert(varchar(120),getdate(),120))
declare @i int
declare @n intselect @i=1
select @n=@iinsert into tb values(1)while @n*2<=100000
begin
insert into tb(n)
select @n+n from tb
select @n=@n*2
end insert into tb(n)
select @n+n from tb where @n+n<=100000
print(convert(varchar(120),getdate(),120))
drop table tb/*
2010-02-05 11:31:13
2010-02-05 11:31:15
*/
declare @i int
set @i=0
set @s=''
while(@i <9999)
begin
set @s=@s+'select '+ltrim(@i+1)+' union all '
set @i=@i+1
end set @s='insert into t(id) '+'@s+'select '+ltrim(@i+1)
exec(@s)
;with cte
as
(
select row_number() over(order by getdate()) as id
from syscolumns a,syscolumns b
)select id into tb from cte where id <=20000
print(convert(varchar(120),getdate(),120))
--2010-02-05 11:33:24
--2010-02-05 11:33:24
--select * from tb
select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9
insert into jin
select a.i + 10*b.i + 100*c.i + 1000*d.i + 10000*e.i
from @t a,@t b,@t c,@t d,@t e
print(convert(varchar(120),getdate(),121))
--执行的结果
/*
2010-02-05 11:33:52.077(10 行受影响)(100000 行受影响)
2010-02-05 11:33:53.280
*/
go
SET NOCOUNT ON;
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 100000;
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;
ENDINSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
print(convert(varchar(120),getdate(),121))
/*
2010-02-05 11:42:10.293
2010-02-05 11:42:11.107*/
go
SET NOCOUNT ON;
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 100000;
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;
GO
print(convert(varchar(120),getdate(),121))
/*
2010-02-05 11:42:10.293
2010-02-05 11:42:11.107 */
go
DECLARE @n AS BIGINT;
SET @n = 100000;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;
GOprint(convert(varchar(120),getdate(),121))
/*
2010-02-05 11:43:59.4832010-02-05 11:43:59.890*/
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 <= 100000;