2

We have a custom database updater which runs various SQL scripts on SQL Server. Some of the scripts need to add a new column to a table, then populate the values, in a single script, within a transaction:

using (var scope = new TransactionScope()) {

    ... alter table MyTable add FOOBAR int;
    ... update schappointment set FOOBAR = 1;

}

Problem is, SQL spits back "Invalid column name 'FOOBAR' because the "alter table" command hasn't taken effect. Wrapping it in exec() makes no difference:

    ... exec('alter table MyTable add FOOBAR int;')
    ... update schappointment set FOOBAR = 1;

It works OK within SQL Management Studio because it splits it up with GO commands (which I know are not valid T-SQL commands).

I'd prefer not to create any new dependencies in the project.

My preference is not to split the schema & data scripts as this doubles the number of scripts for no reason other than to make SQL happy.

How can I solve this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Brendan Hill
  • 3,406
  • 4
  • 32
  • 61
  • Does this question give you any help? http://stackoverflow.com/questions/40814/how-do-i-execute-a-large-sql-script-with-go-commands-from-c – Blorgbeard Jun 12 '13 at 05:53
  • SQL Server. Blorgbeard - I've seen the page before, my preference is not to introduce dependency on SMO. Could do if necessary, though, if no better options. – Brendan Hill Jun 12 '13 at 06:13
  • what about putting update statement into `exec`? – cha Jun 12 '13 at 06:43

2 Answers2

4

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.

I tried this approach this is working.

alter table then update in single statement

Community
  • 1
  • 1
Sonali Jain
  • 41
  • 2
0

Our solution was to sprinkle "GO" commands throughout the scripts, and customize our script execution function to split them up and run them seperately.

This mimics what SQL management studio does and seems to work.

Brendan Hill
  • 3,406
  • 4
  • 32
  • 61