复制行并更改一小列列? [英] Copy row and change a small subset of columns?

问题描述

首先，让我作为序言说，我知道针对类似问题的INSERT/SELECT解决方案(请参见

First, let me preface by saying that I'm aware of the INSERT/SELECT solution to similar problems (see here), but it is not applicable to my scenario.

我正在寻找一种不需要显式列出所有列的解决方案，因为这些列事先是未知的.该查询将在数十个单独的数据库中的同一表上运行.所有数据库中都有一个公用列的子集，但是每个列都有一个特定于该数据库的唯一列.

I'm looking for a solution which does not require explicitly listing all columns, as these are unknown ahead of time. This query will be run on the same table in dozens of separate databases. There is a subset of common columns across all databases, but each has its own unique columns specific to just that database, as well.

那么，有没有一种方法可以复制整个行，更改某些已知/常量列子集，然后在不显式列出所有列的情况下重新插入它呢?唯一的解决方案是使用从架构中收集的DDL信息动态构建查询吗?

So, is there a way to copy an entire row, change some known/constant subset of columns, and reinsert it without explicitly listing all columns? Would the only solution be dynamically constructing the query using DDL info gathered from the schema?

推荐答案

注意:此答案适用于SQL Server.在此答案之后，向问题中添加了 oracle 标签的问题

NOTE: This answer is for SQL Server. The oracle tag was added to the question after this answer

根据良好设计的原则，我将假设您的表具有一个IDENTITY列，该列也是主键.我们还假设它没有具有计算列(或时间戳或任何需要更多操作的类型).最后，让我们假设您至少知道此ID列的名称，这是标准名称，例如"id".

I'm going to assume that your table has an IDENTITY column that is also the primary key, according to principles of good design. Let's also assume it does not have computed columns (or timestamps or any type that will require more manipulation). Let's finally assume that you know at least the name of this ID column, which is standard, e.g. "id".

您可以使用以下顺序:

SELECT * INTO #tmp FROM tbl WHERE id = @copyfrom;
ALTER TABLE #tmp DROP COLUMN id;
UPDATE #tmp SET
   column1 = ...,
   column2 = ...,
   column3 = ...;  --- the subset of columns you want to change
INSERT tbl SELECT * FROM #tmp;

SQL小提琴演示

这篇关于复制行并更改一小列列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！