比如原来的表是table1,后来的表是table2,要插入的列为column1 update table2 set column1=select (case when A = 1 then 2 else A end) from table1
是同一个表的操作。字段1 字段2 字段3 1 A A 1 A A 1 A A 1 A A 1 A A 1 A A 复制字段1为1的数据,重新插入这个表。插入时要奖字段1的值改为2。
2005以后适用: with cte as ( select 2 AS A,B,D from 表 where a=1 ) insert into 表 select * from cte
DECLARE @a TABLE ( A int, B varchar(10), C varchar(10) );insert into @a select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 2,'5','6'--检查已经存在的重复记录 ;with c1 as ( select a.A,a.B,a.C,COUNT(a.A) sumQuantity from @a a group by a.A,a.B,a.C ) ,c2 as ( select A,B,C from c1 where sumQuantity>1 --查询@a表中所有重复记录 ) /*INSERT INTO @a*/ select a.A/*将字段a的数据换掉 a.A+2*/,a.B,a.C --查询原表中所有重复数据记录 from c2 c join @a a on c.A=a.A and c.B=a.B and c.C=a.C --查询出@a中的所有重复记录并修改A字段的值,重新插入到@a数据表中
select (case when a = 1 then 2 else a end) a,b,c from tb
with cte as ( select 2 AS A,B,D from 表 where a=1 ) insert into 表 select * from cte
if exists(select * from sysobjects where name='t') drop table t go create table t(a int,b char(10),c char(10)) insert into t select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' declare @i int,@k int set @i=0 set @k=0 update t set a=a+@i --如果字段a不是int类型,但是字段a可转化为int类型,则这句改成这样: --update t set a=convert(int,a)+@i while @k<10 --假设你要插入的数据量为10条 begin set @i=@i+1 --如果只是把插入的数据从1变成2,这句变成set @i=1就可以了 insert into t values(1+@i,'5','6') set @k=@k+1 end select * from t 运行结果如下:a b c 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 2 5 6 3 5 6 4 5 6 5 5 6 6 5 6 7 5 6 8 5 6 9 5 6 10 5 6 11 5 6 上面这个是保留原始数据并插入新的数据,其中字段b和字段c都相同,变的只是字段a的值, 使用这种方法有一个前提,就是字段a必须为int类型或可转化为int的类型。 使用循环来插入
if exists(select * from sysobjects where name='t') drop table t go create table t(a int,b char(10),c char(10)) insert into t select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' union all select 1,'5','6' declare @i int,@k int set @i=0 set @k=0 update t set a=a+@iwhere b='5' and c='6' --如果字段a不是int类型,但是字段a可转化为int类型,则这句蓝字部分可以改成这样:set a=convert(int,a)+@i while @k<10 --假设你要插入的数据量为10条 begin set @i=@i+1 --如果只是把插入的数据从1变成2,这句变成set @i=1就可以了 insert into t values(1+@i,'5','6') set @k=@k+1 end select * from t 运行结果如下:a b c 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 1 5 6 2 5 6 3 5 6 4 5 6 5 5 6 6 5 6 7 5 6 8 5 6 9 5 6 10 5 6 11 5 6 加多红字部分比较好一点
INSERT INTO [TableName](A,B,C) SELECT A=2 ,B ,C FROM[TableName] WHERE A=1
update tb
set a = 2
where a = 1
select (case when a = 1 then 2 else a end) a,b,c
from tb
http://www.cnblogs.com/frustrate2/archive/2009/02/15/1390831.html
update table2 set column1=select (case when A = 1 then 2 else A end) from table1
1 A A
1 A A
1 A A
1 A A
1 A A
1 A A
复制字段1为1的数据,重新插入这个表。插入时要奖字段1的值改为2。
with cte
as
(
select 2 AS A,B,D
from 表
where a=1
)
insert into 表
select * from cte
(
A int,
B varchar(10),
C varchar(10)
);insert into @a
select 1,'5','6'
union all
select 1,'5','6'
union all
select 1,'5','6'
union all
select 2,'5','6'--检查已经存在的重复记录
;with c1 as
(
select a.A,a.B,a.C,COUNT(a.A) sumQuantity
from @a a
group by a.A,a.B,a.C
)
,c2 as
(
select A,B,C from c1
where sumQuantity>1 --查询@a表中所有重复记录
)
/*INSERT INTO @a*/
select a.A/*将字段a的数据换掉 a.A+2*/,a.B,a.C --查询原表中所有重复数据记录
from c2 c
join @a a on c.A=a.A and c.B=a.B and c.C=a.C
--查询出@a中的所有重复记录并修改A字段的值,重新插入到@a数据表中
from tb
as
(
select 2 AS A,B,D
from 表
where a=1
)
insert into 表
select * from cte
drop table t
go
create table t(a int,b char(10),c char(10))
insert into t
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6'
declare @i int,@k int
set @i=0
set @k=0
update t set a=a+@i --如果字段a不是int类型,但是字段a可转化为int类型,则这句改成这样:
--update t set a=convert(int,a)+@i
while @k<10 --假设你要插入的数据量为10条
begin
set @i=@i+1 --如果只是把插入的数据从1变成2,这句变成set @i=1就可以了
insert into t values(1+@i,'5','6')
set @k=@k+1
end
select * from t
运行结果如下:a b c
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
2 5 6
3 5 6
4 5 6
5 5 6
6 5 6
7 5 6
8 5 6
9 5 6
10 5 6
11 5 6 上面这个是保留原始数据并插入新的数据,其中字段b和字段c都相同,变的只是字段a的值,
使用这种方法有一个前提,就是字段a必须为int类型或可转化为int的类型。
使用循环来插入
drop table t
go
create table t(a int,b char(10),c char(10))
insert into t
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6' union all
select 1,'5','6'
declare @i int,@k int
set @i=0
set @k=0
update t set a=a+@i where b='5' and c='6'
--如果字段a不是int类型,但是字段a可转化为int类型,则这句蓝字部分可以改成这样:set a=convert(int,a)+@i
while @k<10 --假设你要插入的数据量为10条
begin
set @i=@i+1 --如果只是把插入的数据从1变成2,这句变成set @i=1就可以了
insert into t values(1+@i,'5','6')
set @k=@k+1
end
select * from t
运行结果如下:a b c
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
1 5 6
2 5 6
3 5 6
4 5 6
5 5 6
6 5 6
7 5 6
8 5 6
9 5 6
10 5 6
11 5 6 加多红字部分比较好一点
SELECT
A=2
,B
,C
FROM[TableName]
WHERE A=1