我有一个问题,我正在尝试将来自一张表的数据合并和整合到另一张表中。
源表和目标表完全相同,除了目标表需要在第一列上设置主键。
输入的数据和期望的结果如下:
data in Desired results
c1 c2 c3 c1 c2 c3
+--+--+--+ +--+------+------+
1 A x 1 A B C x y
2 B z 2 B C z
3 A z 3 A z x y
1 A y +--+------+------+
3 A y
1 B x
2 C z
1 C x
3 A x
1 A x
+--+--+--+
我初始化了表格...
CREATE TABLE s (c1 char(2), c2 char(8), C3 char(8))
INSERT INTO s VALUES
('1','A','x'), ('2','B','z'), ('3','A','z'),
('1','A','y'), ('3','A','y'), ('1','B','x'),
('2','C','z'), ('1','C','z'), ('3','A','x'),
('1','A','x')
CREATE TABLE d (c1 char(2) PRIMARY KEY, c2 char(8), C3 char(8))
我可以使用光标使它工作...
DECLARE @c1 Char(2)
DECLARE @c2 char(8)
DECLARE @C3 char(8)
DECLARE cur CURSOR FOR SELECT c1, c2, C3 FROM s
OPEN cur
FETCH NEXT FROM cur INTO @c1, @c2, @C3
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE d SET
c2 = (CASE WHEN CHARINDEX(RTRIM(@c2), c2) > 0 THEN c2 ELSE SUBSTRING(RTRIM(c2) + ' ' + @c2, 1, 8) END),
c3 = (CASE WHEN CHARINDEX(RTRIM(@c3), c3) > 0 THEN c3 ELSE SUBSTRING(RTRIM(c3) + ' ' + @c3, 1, 8) END)
WHERE c1 = @c1
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO d (c1, c2, c3)
VALUES (@c1, @c2, @c3)
END
FETCH NEXT FROM cur INTO @c1, @c2, @c3
END
CLOSE cur
DEALLOCATE cur
但是我无法让合并工作...
错误:违反了主键约束“PK__d__3213663B03BB8E22”。在对象“dbo.d”中无法插入重复键值(1 )。MERGE INTO d USING s
ON (s.c1 = d.c1)
WHEN MATCHED THEN
UPDATE SET
c2 = (CASE WHEN CHARINDEX(RTRIM(s.c2), d.c2) > 0 THEN d.c2 ELSE SUBSTRING(RTRIM(d.c2) + ' ' + s.c2, 1, 8) END),
c3 = (CASE WHEN CHARINDEX(RTRIM(s.c3), d.c3) > 0 THEN d.c3 ELSE SUBSTRING(RTRIM(d.c3) + ' ' + s.c3, 1, 8) END)
WHEN NOT MATCHED THEN
INSERT (c1, c2, c3) VALUES (s.c1, s.c2, s.c3);
有没有比逐行进行更为优雅的方法来完成这个任务?感谢您的帮助!
mysql
、postgresql
、sql-server
、oracle
或db2
,或者完全不同的内容。 - marc_ssql server
。 - Pரதீப்