如何在SQL中将列从一个表复制/移动到另一个表？ [英] How to copy/move a column from one table to another in SQL?

问题描述

我的数据库中有几张桌子。

我想从表格RD11复制/移动列Msk709到表RD21。

我尝试了以下方法：

Hi,

I have a couple of tables in my database.
I want to copy/move column Msk709 from table RD11 to table RD21.
I tried the following:

"INSERT INTO RD21 (Msk709) SELECT RD11.Msk709 FROM RD11"

我收到以下错误：

无效的列名''Msk709''



接下来尝试，我试着制作专栏：

I get the following error:
Invalid column name ''Msk709''

Next try, I tried to make the column:

"ALTER TABLE RD21 ADD Msk709 NVARCHAR(5) ";

之后：

and after that:

"INSERT INTO RD21 (Msk709) SELECT RD11.Msk709 FROM RD11"

我收到错误：

无法将值NULL插入列''AnställningsNummer''，表''KompetensMatris.dbo.RD21'';列不允许空值。 INSERT失败。

声明已被终止。



在RD21表格中，我有以下列：

I get the error:
Cannot insert the value NULL into column ''AnställningsNummer'', table ''KompetensMatris.dbo.RD21''; column does not allow nulls. INSERT fails.
The statement has been terminated.

In the RD21 table I have the following columns:

AnställningsNummer, Msk100, Msk201, Msk710

我错过了什么？

有人能指出我正确的方向吗？



Vidor

Am I missing something?
Can someone point me to the right direction?

Vidor

推荐答案

I认为你只需要改变你的新列以允许空值（因为源表中显然有一些空值）

I think you just need to change your new column to allow nulls (as there are obviously some nulls in the source table)

ALTER TABLE RD21 ADD Msk709 NVARCHAR(5) NULL

我设法解决了这个问题：



First

I managed to solve the problem:

First

string alterSQL = "ALTER TABLE <totable> ADD <column> NVARCHAR(5) ";

现在来了更新

Now comes the update

string qryMove ="UPDATE <totable> SET <column> = (SELECT <column> FROM <fromtable> WHERE <fromtable.keycolumn> = <totable.keycolumn>)";

更新后，我可以删除表格中的旧列：

After update I can remove the old column från the table:

string strDelColumn = "ALTER TABLE <fromtable> DROP COLUMN <column>;

执行SQL查询

Execute the SQL queries

Functions Execute = new Functions();
Execute.MySqlCommand(alterSQL, connCopy);
Execute.MySqlCommand(qryMove, connCopy);
Execute.MySqlCommand(strDelColumn, connCopy);

这就是全部。



Vidor

That''s all.

Vidor

这篇关于如何在SQL中将列从一个表复制/移动到另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！