SQLServer 脚本

1. 批量插入数据

建表命令

create table dbo.t1(
	ID			INT, 
	NAME		VARCHAR(20),
	primary key(ID)
)

循环插入脚本1：

--最简单的批量插入
declare @i int;    
declare @name varchar(20)
set @i = 1;  
while @i <= 1000    
begin    
	set @name = 'aaa' + convert(varchar, @i);
    insert into dbo.t1 values (@i, @name);    
    set @i = @i + 1;    
end    
go   

循环插入脚本2：

脚本1中每插入一条数据便提交一次事物，速度较慢。

改为如下命令，每100条数据提交一次事物

-- 批量插入，每100条提交一次
declare @i int
declare @name varchar(20)
set @i = 1
begin transaction
while @i <= 10000
begin
	set @name = 'aaa' + convert(varchar, @i);
	insert into dbo.t1 values (@i, @name);
	set @i = @i + 1;
	
	if @i % 100 = 0
	begin
		commit;
		begin transaction;
	end
end
commit
go

2. 批量修改数据

-- update
declare @i int
declare @name varchar(20)
set @i = 1
begin transaction
while @i <= 100000
begin
	set @name = 'bbb' + convert(varchar, @i);
	update dbo.t1 set NAME = @name where ID = @i;
	set @i = @i + 1;
	
	if @i % 100 = 0
	begin
		commit;
		begin transaction;
	end
end
commit
go

3. 批量建表

-- 批量建表
declare @tab_name varchar(20) 
declare @i int 
set @i = 1   
while @i < 10  
begin  
set @tab_name = 'table' + convert(varchar, @i);
exec('create table  '+@tab_name+'(  
id int,  
name nvarchar(20),  
primary key (id)
)')  
set @i = @i+1  
END  
GO