然后在一条语句中更新alter table

I have a requirement where I need to Alter (Add 2 columns) and then update the same table.

我有一个需要修改的地方(添加2列)，然后更新同一个表。

Here is the query I tried:

以下是我尝试的查询:

ALTER TABLE A
ADD c1 int,c2 varchar(10)

UPDATE  A set c1 = 23, c2 = 'ZZXX'

I need to run the above two queries at a time.

我需要一次运行上述两个查询。

I am using Talend ETL tool, in this we have a component tMssqlrow, which allow us to run multiple queries (I am using 10 to 15 update queries in single component).

我正在使用Talend ETL工具，在这里我们有一个组件tMssqlrow，它允许我们运行多个查询(我在单个组件中使用10到15个更新查询)。

But the above query is not working.

但是上面的查询不起作用。

I tested in DataBase Microsoft SQL. i am getting the below error :

我在数据库Microsoft SQL中测试过。我得到以下错误:

Msg 207, Level 16, State 1, Line 5

Msg 207, 16级，状态1，第5行

Invalid column name 'c1'. Msg 207,

无效列名“c1”。207年味精,

Level 16, State 1, Line 5

第16级，状态1，第5行

Invalid column name 'c2'.

无效列名c2。

can any one help me resolve this problem.

谁能帮我解决这个问题吗?

3 个解决方案

#1

You can't do this exactly in a single statement (or batch) and it seems the tool you are using does not support GO as a batch delimiter.

您不能在单个语句(或批处理)中完成这一操作，而且您所使用的工具似乎不支持GO作为批处理分隔符。

You can use EXEC to run it in a child batch though.

不过，您可以使用EXEC在子批处理中运行它。

ALTER TABLE A
  ADD c1 INT, c2 VARCHAR(10);

EXEC('
UPDATE A
SET    c1 = 23,
       c2 = ''ZZXX'';
    ');

NB: All single quotes in the query need to be doubled up as above to escape them inside a string literal.

NB:查询中的所有单引号都需要像上面那样进行双引号才能在字符串中转义。

Or alternatively you could achieve similar results in a single statement with the aid of some default constraints.

或者，您也可以通过一些默认约束来在单个语句中实现类似的结果。

ALTER TABLE A
  ADD c1 INT NULL CONSTRAINT DF_A_c1 DEFAULT 23 WITH VALUES, 
     c2 VARCHAR(10) CONSTRAINT DF_A_c2 NULL DEFAULT 'ZZXX' WITH VALUES;

But this is not exactly the same as the original query as the default constraints will be left behind and may need to be dropped.

但这与原始查询并不完全相同，因为默认约束将被遗留下来，可能需要删除。

#2

Use GO between your 2 queries.

在两个查询之间使用GO。

#3

-1

Try this

试试这个

ALTER TABLE A ADD c1 int,c2 varchar(10)

GO

UPDATE  A set c1 = 23, c2 = 'ZZXX'

GO

#1