DECLARE @tablename VARCHAR(30),@sql VARCHAR(500)DECLARE
cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FORSELECT name FROM
sysobjects WHERE name LIKE ‘PUB%‘ AND
type=‘U‘OPEN cur_delete_tableFETCH NEXT FROM cur_delete_table
INTO @tablenameWHILE @@FETCH_STATUS = 0BEGINSELECT @sql=‘delete
from ‘+@tablenameEXEC (@sql)FETCH NEXT FROM cur_delete_table INTO
@tablenameENDCLOSE cur_delete_tabl...
单条插入语句---
insert into Reader(readerName) values(‘测试‘)那如何进行批量插入呢?除了多条insert 语句组合而成,还有其他吗?--Sql Server 的批量添加---
--方式1:多条insert语句插入----
insert into Reader(readerName) values(‘读者1‘);
insert into Reader(readerName) values(‘读者2‘);
insert into Reader(readerName) values(‘读者3‘);--方式2:union all方式插入---
insert into Reader(readerName)
selec...
-创建一个测试的员工表---
create table Employee(EmployeeNo int primary key, --员工编号EmployeeName nvarchar(50) null, --员工名称CreateUser nvarchar(50) null, --创建人CreateDate datetime null, --创建时间
) 执行后结果:那么假如我们要批量插入10000条数据,应该怎么办?
这里有四种方法(普通循环,事务循环、批量插入、cte插入)
1、普通循环插入(while)/***************************************...
b.name TableName,a.name TypeName,a.* from sysobjects a inner join sysobjects b on a.parent_obj=b.id and b.xtype=‘U‘--删除约束 F外键、PK主键、D 约束、UQ 唯一约束
declare @tableName varchar(max),@typeName varchar(max)
declare fk_cursor cursor for select b.name TableName,a.name TypeName from sysobjects a inner join sysobjects b on a.parent_obj=b.id and b.xtype=‘U‘ where a.xtype=‘PK‘open fk_cur...
sqlserver 批量删除存储过程和批量修改存储过程的语句,需要的朋友可以参考下。修改: 代码如下:declare proccur cursor for select [name] from sysobjects where name like Foods_% declare @procname varchar(100) declare @temp varchar(100) open proccur fetch next from proccur into @procname while(@@FETCH_STATUS = 0) begin set @temp=kcb_+@procname EXEC SP_RENAME @procname,@temp print(@procname + 已被删除) fe...
这里设定传过来的参数是拼接好的字符串,如:1,2,3,4,5 create procedure up_batchDeleteById ( @condition varchar(max) ) as delete from dt_name where id in(@condition) 以上的做法看似正确,实际会报错,具体原因是说id是int类型的,而@condition是字
这里设定传过来的参数是拼接好的字符串,如:1,2,3,4,5create procedure up_batchDeleteById(
@condition varchar(max))asdelete from dt_name where id in(@condition)以上...
当我们需要批量删除数据库中的表时,对于单个删除一些表是否感到烦躁,厌倦,干脆写个脚本用得了。
本脚本使用游标循环删除,对于数量比较小,用游标暂不会造成恶劣影响。代码如下:DECLARE @tablename VARCHAR(30),@sql VARCHAR(500)DECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FORSELECT name FROM sysobjects WHERE name LIKE PUB% AND type=UOPEN cur_delete_tableFETCH NEXT FROM cur_delete_table INTO @tablena...
修改: 代码如下: declare proccur cursor for select [name] from sysobjects where name like Foods_% declare @procname varchar(100) declare @temp varchar(100) open proccur fetch next from proccur into @procname while(@@FETCH_STATUS = 0) begin set @temp=kcb_+@procname EXEC SP_RENAME @procname,@temp print(@procname + 已被删除) fetch next from proccur into @procname end close proccur deallocate proccur...
原文链接:http://www.cnblogs.com/hjzhang/archive/2011/05/17/2048743.html/*--------------------------------
功能说明: 批量DropTable
使用说明: 使用时一定要小心,因为删选表的where条件是like所有必须保证where
后的like确定与你要删除表名相匹配
---------------------------------*/
--------参数定义-------------------
DECLARE @tableName AS
Nvarchar(50) --查询表名条件(小心!,
确保like条件是你要Drop的表....