如何在 SQL Server 2005 Express 中将一个表的所有字段复制到另一个更宽的表? [英] How can I copy all fields of one table to another, wider table in SQL Server 2005 Express?

问题描述

我知道我的标题措辞并不准确，所以让我澄清一下.我正在使用 SQL Server 2005 Express.

I know my title isn't exactly worded well, so let me clarify. I'm using SQL Server 2005 Express.

如果你愿意的话，我有一个基本上存储模板"的表格.以汽车为例，字段类似于:

I have a table that basically stores a "template," if you will. Using a car as an example, the fields would be something like:

TemplateID
Color
Make
Model

现在，我有另一个代表模板实例"的表格.它包含模板表的所有字段以及实例独有的一些字段:

Now, I have another table that represents an "instance" of the template. It contains all the fields of the template table plus some fields that are unique to an instance:

InstanceID
VIN
SerialNumber
Color
Make
Model

我正在编写一个存储过程来在实例"表中插入一个新行.此存储过程将采用VIN"和SerialNumber"的输入参数，但我希望它从相应的模板"中复制颜色"、制造"和型号".模板由已知的TemplateID"定位.

I am writing a stored procedure to insert a new row into the "Instance" table. This stored procedure will take input parameters for "VIN" and "SerialNumber," but I want it to copy "Color", "Make", and "Model" from the appropriate "Template." The template is located by the "TemplateID," which is already known.

我正在寻找有关如何编写此存储过程的建议.如果不是因为这些表包含 100 多个字段(我没有设计它们，但我一直在使用它们)，那将很简单.我想做类似的事情:

I am looking for advice on how to write this stored procedure. It would be simple if it weren't for the fact that these tables contain 100+ fields (I didn't design them but I'm stuck using them.) I would like to do something like:

INSERT INTO Instance(VIN, SerialNumber, "EverythingElse") 
VALUES (@VIN, @SerialNumber, SELECT * FROM Template WHERE TemplateID = 1)

换句话说，我只想提供(通过参数)那些未存储在模板"中的字段，并从模板中复制其他所有内容.如果没有对每个字段进行手动编码，这样的事情是否可行?

In other words, I only want to supply (through parameters) those fields not stored in the "Template" and copy everything else from the template. Is anything like this possible without hand coding for every field?

推荐答案

只需执行 INSERT INTO 并使用 SELECT 语句而不是 values 语句.

Just do a INSERT INTO and use a SELECT statement instead of a values statement.

INSERT INTO Instance(VIN, SerialNumber, a, b, c) 
SELECT @VIN, @SerialNumber, a, b, c FROM Template WHERE TemplateID = 1;

你真的不想使用 *.您可以让 SQL Server Managemnt Studio 为您编写一个 SELECT 语句，它会生成字段名称，因此您不必键入它们.

You really don't want to use *. You can have SQL Server Managemnt Studio script a SELECT statement for you and it will generate the field names so you don't have to type them.

这篇关于如何在 SQL Server 2005 Express 中将一个表的所有字段复制到另一个更宽的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！