使用脚本在 SQL Server 中的其他列之间插入列

Inserting column between other columns in SQL Server using script

提问by Kip

I am trying to alter a table in SQL server with a script. In the past I have always done this kind of thing through a GUI, but now I need to generate a script to do it for customers.

我正在尝试使用脚本更改 SQL Server 中的表。过去我一直通过GUI来做这种事情，但现在我需要生成一个脚本来为客户做。

I have an SQL Server database table that is like this:

我有一个像这样的 SQL Server 数据库表：

MyTable
-------
ColA int NOT NULL
ColB int NOT NULL
ColC int NOT NULL
ColD VARCHAR(100)

The primary key is defined across ColA, ColB, and ColC.

主键是跨 ColA、ColB 和 ColC 定义的。

I want the SQL script to change the table like so:

我希望 SQL 脚本像这样更改表：

MyTable
-------
ColA int NOT NULL
ColB int NOT NULL
ColX int NOT NULL  (new column, default 0 for existing data)
ColC int NOT NULL
ColD VARCHAR(100)

The primary key would now be defined by ColA, ColB, ColX, and ColC.

主键现在由 ColA、ColB、ColX 和 ColC 定义。

This is easy to do through SQL Server GUI. But when I have it generate a script from that, it seems unnecessarily complex. Basically, the script creates a temporary table with the new schema, copies all the data, indexes, and constraints from the old table into the temp table, deletes the old table, then renames the new one to the name of the old one. In addition, it has lines like this:

这很容易通过 SQL Server GUI 完成。但是当我让它从中生成一个脚本时，它似乎不必要地复杂。基本上，脚本使用新模式创建一个临时表，将旧表中的所有数据、索引和约束复制到临时表中，删除旧表，然后将新表重命名为旧表的名称。此外，它还有这样的行：

ALTER TABLE dbo.Tmp_MyTable ADD CONSTRAINT
    MyTable21792984_ColC_DF DEFAULT ((0)) FOR ColC

I'm concerned that these random-looking numbers there (i.e. 21792984) will not be the same on all customer database instances. They look like something that the SQL server generates when creating the database that would be unique to each instance.

我担心这些看似随机的数字（即 21792984）在所有客户数据库实例上都不同。它们看起来像 SQL 服务器在创建每个实例唯一的数据库时生成的东西。

Is there a more straight-forward way of changing the table through SQL commands? I've looked online but what I've found is mostly basic and/or generic.

是否有通过 SQL 命令更改表的更直接的方法？我在网上看过，但我发现的大多是基本的和/或通用的。

Update: From the answers I have received, it looks like the difficulty lies in putting the new column "in between" two columns. I've realized it doesn't really matter what order the columns are in (if I am wrong feel free to leave an answer correcting me). In my case, the change is much simpler if I just add the column to the end of the table, and nothing in the code is relying on the specific column order.

更新：从我收到的答案来看，难点在于将新列“置于”两列之间。我已经意识到列的顺序并不重要（如果我错了，请随时留下更正我的答案）。在我的情况下，如果我只是将列添加到表的末尾，那么更改会简单得多，并且代码中没有任何内容依赖于特定的列顺序。

回答by Aaron Alton

There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table. That said, column order shouldn't matter - are you sure that the column needs to be inserted in order?

没有另一种方法可以在现有列之间的 SQL Server 表中插入一列 - 您需要构建一个临时表并重建旧表。也就是说，列顺序应该无关紧要 - 您确定该列需要按顺序插入吗？

Likely your best bet is to just use the GUI, script it out, and then change the constraint name to something reasonable within the script. You're right that the numerical constraint name isn't ideal, and it's not a best practice to allow SQL Server to determine your object names.

可能最好的办法是只使用 GUI，编写脚本，然后在脚本中将约束名称更改为合理的名称。您是对的，数字约束名称并不理想，并且允许 SQL Server 确定您的对象名称不是最佳做法。

回答by kemiller2002

If you are inserting a field into the middle of the table, you essentially have to drop the current table and recreate it which is what sql server is doing.

如果您在表的中间插入一个字段，您基本上必须删除当前表并重新创建它，这就是 sql server 正在做的事情。

The random numbers are making sure the constraint has a unique name. If you keep the script and run it on multiple databases, then they will all be the same. If you are going to modify each one through the gui, then yes they will most likely be different.

随机数确保约束具有唯一的名称。如果您保留脚本并在多个数据库上运行它，那么它们都将是相同的。如果您要通过 gui 修改每一个，那么是的，它们很可能会有所不同。

To modify the primary key, all you need to do is find out the primary key constraint name and drop it. Just add a new constraint defining the primary key. This is assuming you don't have the primary key listed as a foreign key somewhere else.

要修改主键，您需要做的就是找出主键约束名称并将其删除。只需添加一个定义主键的新约束。这是假设您没有将主键列为其他地方的外键。

回答by J. Polfer

It's not a good practice to depend on any sort of "natural" or "inherent" ordering of columns in a database table. All columns shouldbe referenced by name in any officially generated queries to return columns by name in the order specified by the query. If that rule isn't followed, any future schema changes are an absolute nightmare as the system code may need to be changed every time the database schema is updated.

依赖于数据库表中列的任何“自然”或“固有”排序都不是一个好习惯。在任何正式生成的查询中，所有列都应按名称引用，以按查询指定的顺序按名称返回列。如果不遵循该规则，那么未来的任何架构更改都是绝对的噩梦，因为每次更新数据库架构时都可能需要更改系统代码。

The only annoying thing here would be when running one-off queries using SELECT * FROM ...on production/test/development databases hand-coded by users and having your new column show up on the end of the column list. However, I believe many query tools will let you reorder the columns from a sort of GUI.

这里唯一令人讨厌的事情是在SELECT * FROM ...用户手动编码的生产/测试/开发数据库上运行一次性查询并让您的新列显示在列列表的末尾时。但是，我相信许多查询工具都可以让您从某种 GUI 重新排序列。

回答by HLGEM

I just want to point out why you never ever want to use the GUI to insert a column inthe middle of an existing table. When you do that it, creates a speatare new table, moves the data from the old table, renames the old table, renames the new table to the old tablename and drops the old table. This is bad enough ifyou havea small databaset. In a production world where tables can be quite large, you could be locking the users out of access to the table for several hours. Any database design where the order of columns in the database needs to be rearranged when a new column is added is a database headed for disaster. Becasue there are people who insist on doing this though, it is another reason why Select * is also a problem waiting to happen. You really don't want the state to show up in your zip column in a report because someone rearranged the columns in the table and you relied on select * from the column order.

我只想指出为什么您永远不想使用 GUI 在现有表的中间插入一列。当你这样做时，创建一个 speatare 新表，从旧表中移动数据，重命名旧表，将新表重命名为旧表名并删除旧表。如果你有一个小的数据库，这已经够糟糕了。在表可能非常大的生产环境中，您可能会在几个小时内锁定用户无法访问表。任何在添加新列时需要重新排列数据库中列顺序的数据库设计都是走向灾难的数据库。因为有些人坚持这样做，这也是为什么 Select * 也是一个等待发生的问题的另一个原因。你真的不

回答by Alex Martelli

If in your CREATE TABLEyou just specified, say, PRIMARY KEY (ColA, ColB, ColC), you were telling SQL Server that you didn't care about the name of this constraint, that you'd never have to refer to it directly -- so SQL Server was fully justified in generating some semi-random-looking unique name for it, like the MyTable21792984_ColC_DFthat rightly worries you (though that particular one seems to be a ColC-specific single-column constraint, the same kind of naming will apply to other constraints).

如果CREATE TABLE您刚刚指定，例如，PRIMARY KEY (ColA, ColB, ColC)您告诉 SQL Server 您不关心此约束的名称，则您永远不必直接引用它——因此 SQL Server 完全有理由生成一些半随机的唯一名称，就像MyTable21792984_ColC_DF您担心的那样（尽管该特定名称似乎是ColC特定的单列约束，但相同类型的命名将适用于其他约束）。

While this issue is probably too late to fix for your current schema, it would help you in the future if you followed the principle of always name your constraints-- as it's generally quite possible that you may need to refer to them in the future, so you want the name to be fully under your control, just like the name for any other schema object (table, column, and so forth). In this case, using a clause like CONSTRAINT PK_MyTable PRIMARY KEY (ColA, ColB, ColC)in the CREATE TABLEwould have helped. (If the GUI tools you're using for your DBA tasks don't let you control this kind of things, they're not suitable tools for a DBA: find better ones!-).

虽然这个问题对于您当前的架构来说修复可能为时已晚，但如果您遵循始终命名约束的原则，它将在将来对您有所帮助- 因为通常您将来可能需要引用它们，所以您希望名称完全在您的控制之下，就像任何其他模式对象（表、列等）的名称一样。在这种情况下，使用像CONSTRAINT PK_MyTable PRIMARY KEY (ColA, ColB, ColC)中的子句CREATE TABLE会有所帮助。（如果您用于 DBA 任务的 GUI 工具不允许您控制此类事情，那么它们不适合 DBA：寻找更好的工具！-）。

回答by megha kashinkunti

Add the new column to the existing table.(its added to the end of all columns ) Then create a new table using this table and in the sub query which is used to build the new table, mention the columns in the order that you want them in the new table..

将新列添加到现有表中。（将其添加到所有列的末尾）然后使用此表创建一个新表，并在用于构建新表的子查询中，按您想要的顺序提及列他们在新桌子上..

Ex:

前任：

create table person(name varchar2(10),age number);
alter table person add salary number; (now salary is added at last position)
desc person
 name ...
 age  ...
 salary ... so now salary is at the end.

say now I want to create employee table using this person table.

现在说我想使用这个人表创建员工表。

create table employee as select name,salary,age from person;

when you describe employee table it has the definition as that of person table AS WELL AS DATA.

当您描述员工表时，它的定义与人员表以及数据的定义相同。

回答by Cade Roux

If you want the column in the middle like that, that's what you have to do. I've found the scripts it generates to be pretty good about only including the neceessary work.

如果您希望中间的列像那样，这就是您必须做的。我发现它生成的脚本非常好，只包含必要的工作。

If you were to add a column at the end for instance, you would only need to do:

例如，如果您要在最后添加一列，则只需执行以下操作：

ALTER TABLE ADD COLUMN ColX int NOT NULL DEFAULT(0)

And you would see this in the script it generates.

你会在它生成的脚本中看到这一点。

As far as altering the primary key, it can be dropped (on all existing columns) and recreated without rewriting the table, but if it's clustered or not you would probably get a different script.

至于改变主键，它可以被删除（在所有现有列上）并重新创建而不重写表，但如果它是集群的，你可能会得到一个不同的脚本。

回答by DaveE

Take the sample code & modify it. If you want the column to land in a particular place, you'll have to go the temp table redefine/rename route. You can name the indexes and constraints as you like this way, also.

获取示例代码并修改它。如果您希望该列位于特定位置，则必须使用临时表重新定义/重命名路由。您也可以通过这种方式随意命名索引和约束。

回答by J.W.

What tool do you use to generate script. I use Red-Gate Sql compare tool, and it works beautifully.

你用什么工具生成脚本。我使用Red-Gate Sql 比较工具，它工作得很好。