我是存储过程的新手。
我有 400 万条记录,因此手动无法执行此操作,因此请使用存储过程。
我有一张桌子,比如:
Id Name
-----------------
1 abc
2 xyz
3 abc
4 pqr
5 abc
6 pqr
在该表中,一个文件称为名称。在名称列中,有些是记录是同名的,所以我想修改记录并想要:
Id Name
---------------------
1 abc
2 xyz
3 abc-1
4 pqr
5 abc-2
6 pqr-1
并将其插入到具有相同架构的另一个表中。
您可以使用可更新的 CTE 执行此操作:
with toupdate as (
select t.*, row_number() over (partition by name order by id) as seqnum
from onetable t
)
update toupdate
set name = name + '-' + cast(seqnum - 1 as varchar(255))
where seqnum > 1;
实际上,这会将其更新到位。 将其放入另一个表中:
with toinsert as (
select t.*, row_number() over (partition by name order by id) as seqnum
from onetable t
)
select id,
(case when seqnum = 1 then name
else name + '-' + cast(seqnum - 1 as varchar(255))
end) as name
into newtable
from toinsert;
这将更新表格
;WITH cte AS
(
SELECT id,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Id) AS rno,
FROM table1
)
update t.Name = t.Name + '-'+ c.rno
from table1 t
join cte c on c.id = t.id
where c.rno >1
要插入,只需使用带有charindex
的选择
select * into Table2 from table1
where CHARINDEX('-',name) > 1