SQL使用技巧-批量删除-批量更新-bcp导出-跨服务器sql
1、循环删除数据
while @@rowcount>0
begin
delete top (1000) from T
where OperateTime >=20140807
end
清除表数据
truncate table score
2、循环更新数据
方案1：
while(1>0)
begin
update top(10000) tablename
set col1=B.col1
from tablename A with(nolock)
left join tablename2 B with(nolock) on A.col2=B.col2
where A.col3 is not null
IF @@ROWCOUNT = 0
begin
break
end
end
方案2：
declare @startid as int,@endid as int
select @startid=1 ;
select @endid=COUNT(1) FROM T WHERE ...);
print @startid;
print @endid;
while @startid<@endid
begin
UPDATE ... SET ... FROM ... WHERE Id IN(
select Id from (
SELECT Id,ROW_NUMBER()over (order by Id) as rowid FROM ... T WHERE ... ) a
where rowid>=@startid and rowid<@startid+2000
);
set @startid=@startid+2000;
print @startid;
end
select @startid=1 ;
select @endid=COUNT(1) FROM T WHERE ...);
print @startid;
print @endid;
while @startid<@endid
begin
UPDATE ... SET ... FROM ... WHERE Id IN(
select Id from (
SELECT Id,ROW_NUMBER()over (order by Id) as rowid FROM ... T WHERE ... ) a
where rowid>=@startid and rowid<@startid+2000
);
set @startid=@startid+2000;
print @startid;
end
3、--BCP 导出数据
-- bcp "SELECT ... FROM TableName with (nolock) where ... " queryout "D:\score.txt" -c -t"$$" -S服务器 -U用户名 -P密码
--BCP 导入数据
-- bcp "TableName" in "D:\score.txt" -c -t"$$" -S服务器 -U用户名 -P密码
select top 100 * from score
--BCP 导入数据
-- bcp "TableName" in "D:\score.txt" -c -t"$$" -S服务器 -U用户名 -P密码
select top 100 * from score
4、跨服务器请求其他数据库中的表
OPENDATASOURCE('SQLOLEDB','Data Source=服务器;User ID=用户名;Password=密码').数据库名.dbo.表名
