alter table然后在单个语句中更新

问题描述

我需要改变（添加2列），然后更新同一个表。

这是我试过的查询：

  ALTER TABLE A 
 ADD c1 int，c2 varchar（10）
 
 UPDATE A set c1 = 23 ，c2 ='ZZXX'
  

我需要一次运行上面的两个查询。 >

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

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

我在DataBase Microsoft SQL中测试。我收到以下错误：

div>

您不能在单个语句（或批处理）中完全 ，似乎您使用的工具不支持 GO 作为批处理分隔符。

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

  ALTER TABLE A 
 ADD c1 INT，c2 VARCHAR（10） 
 
 EXEC（'
 UPDATE A 
 SET c1 = 23，
 c2 =''ZZXX''; 
'）; 
  

注意：查询中的所有单引号都需要加倍，文字。

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

  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; 
  

但这与原始查询不完全相同，因为默认约束会留下，需要删除。

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

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).

But the above query is not working.

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

can any one help me resolve this problem.

解决方案

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.

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

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.

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.

这篇关于alter table然后在单个语句中更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！