如何在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屋！