使用另一行中除了一行的值插入行？(Insert row with values from another row except for one?)

使用另一行中除了一行的值插入行？(Insert row with values from another row except for one?)

我正在尝试创建一个存储过程，该行将使用a，b，c，d列，其中id = @id，并插入一个具有相同a，b，c值的新行，但d将不同。

在这种情况下，大约有50列，但只有一列需要不同。

I am trying to create a stored procedure that will take a row with columns a,b,c,d where id = @id and insert a new row with the same values for a,b,c except d would be different.

In this case there are around 50 columns, though only the one column needs to be different.

最满意答案

假设d是一个INT而你想插入15而不是dbo.oldtable的任何dbo.oldtable ，那么：

INSERT dbo.newtable(a,b,c,d) SELECT a,b,c,d = 15 FROM dbo.oldtable WHERE id = @id;

更可能是变量，所以：

INSERT dbo.newtable(a,b,c,d) SELECT a,b,c,d = @whatever FROM dbo.oldtable WHERE id = @id;

对不起，但没有简写说“除了d之外的所有列”......你需要单独列出它们。 如果键入是问题，有一个简单的方法来处理。 打开对象资源管理器，展开您的服务器，数据库，表和相关表，然后将列节点拖到查询窗口：

现在，您只需从该列表中删除d列。

Assuming d is an INT and you want to insert, say, 15 instead of whatever is in dbo.oldtable, then:

INSERT dbo.newtable(a,b,c,d) SELECT a,b,c,d = 15 FROM dbo.oldtable WHERE id = @id;

More likely it is from a variable, so:

INSERT dbo.newtable(a,b,c,d) SELECT a,b,c,d = @whatever FROM dbo.oldtable WHERE id = @id;

Sorry, but there is no shorthand to say "all the columns except d"... you need to list them out separately. If typing is the problem, there is an easy way to deal with that. Open Object Explorer, expand your server, database, tables, and the table in question, then drag the columns node onto the query window:

Now you'll just have to delete the d column from that list.