插入-选择保留标识映射

我有两个表，我试图将数据从一个表插入到另一个表，并保持id之间的映射。我在这里找到了一个有同样问题的人，但这个解决方案对我不好

这是一个例子：

的两张表

CREATE TABLE [source] (i INT identity PRIMARY KEY, some_value VARCHAR(30))
CREATE TABLE [destination] (i INT identity PRIMARY KEY, some_value VARCHAR(30))
CREATE TABLE [mapping] (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column

一些样本数据

INSERT INTO [source] (some_value)
SELECT TOP 30 name
FROM sysobjects
INSERT INTO [destination] (some_value)
SELECT TOP 30 name
FROM sysobjects

在这里，我想将所有内容从源传输到目的地，但能够在两个表上保持映射：我尝试使用OUTPUT子句，但不能引用插入的列之外的列：

INSERT INTO [destination] (some_value)
--OUTPUT inserted.i, s.i INTO [mapping] (i_new, i_old) --s.i doesn't work
SELECT some_value
FROM [source] s

有人能解决这个问题吗？

不确定它的写入方式，但它可以工作：D

MERGE [#destination] AS D
USING [#source] AS s
    ON s.i <> s.i
WHEN NOT MATCHED BY TARGET
THEN 
    INSERT (some_value) VALUES (some_value)
OUTPUT inserted.i, s.i INTO [#mapping] (i_new, i_old);

如果您没有修改表的权限，请尝试下面的sql：这个想法是使用临时表作为目标表和映射表之间的桥梁。

SQL查询：

declare @source table (i INT identity PRIMARY KEY, some_value VARCHAR(30))
declare @destination table (i INT identity PRIMARY KEY, some_value VARCHAR(30))
declare @mapping table (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column

declare @tempSource table
(
 id_source INT identity , source_value VARCHAR(30)
 ,Id_New int,source_new VARCHAR(30)
)

insert into @source
output inserted.i, inserted.some_value into @tempSource(id_source,source_value)
SELECT TOP 10 name
FROM sysobjects

--select * from @tempsource

insert into @destination
OUTPUT inserted.i, inserted.some_value  INTO @tempSource (Id_New,source_new)
select source_value from @tempSource

insert into @mapping
select Id_source, Id_New from 
(
    select a.id_source, a.source_value
    from
     @tempSource a
    where id_source is not null and source_value is not null
) aa
inner join 
(
    select a.Id_New, a.source_new
    from
     @tempSource a
    where Id_New is not null and source_new is not null
) bb on aa.source_value = bb.source_new

select * from @mapping

映射表结果：

i_old       i_new
----------- -----------
1           1
2           2
3           3
4           4
5           5
6           6
7           7
8           8
9           9
10          10

相关内容

最新更新

热门标签：