0

In Sql Developer (SqlDev) (the Oracle tool) I most of the time use ';' to indicate the end of a batch.

Also we use the ';' when having a larger script with lots of batches in it. For example a script where the first batch creates a table, the second inserts data in that table, the third does a join with the just created table and another table, etc.

On SqlDev the script (with the different batches in it) works fine. But when we copied the exact script to SQL Server Management Studio (SMSS) and ran it, it gave errors that the table (of the third batch where the created table is joined) does not exist.

How can I make the scipt run on SMSS without the script failing?

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
cybork
  • 569
  • 2
  • 5
  • 24

3 Answers3

1

In SQL server you can use 'GO' to split a batch or block of statement. something like below.

ALTER TABLE [dbo].[Security] ADD  CONSTRAINT [DF_Security_ImportSettings]  DEFAULT ((11111011111111111.)) FOR [ImportSettings]
GO

ALTER TABLE [dbo].[Security] ADD  CONSTRAINT [DF_Security_PricingType]  DEFAULT ((-1)) FOR [PricingType]
GO

ALTER TABLE [dbo].[Security] ADD  CONSTRAINT [DF_Security_AutoUpdateCustomPricing]  DEFAULT ((1)) FOR [AutoUpdateCustomPricing]
GO
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
1

Go is the Keyword you are looking for ..

Example.. 

insert into t1
select 1
go

alter table t1
add abc int

this is also configurable in SSMS(i haven't tested though) to ; or some other word..

enter image description here

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

It appears that in SQL Server Management Studio (SMSS) sometimes it is needed to use 'GO' instead of ';'.

The ';' works differently in SQL Server Management Studio (SSMS) compared to the use of ';' in for example SQl Developer (SQLDev) (the Oracle tool).

In SQLDev the ';' acts as a end of batch indicator, where SSMS doesn't see it when using DLL. Instead SMSS first looks at the whole script and thinks of smart ways to run it. Which means all is run parallel, where some batches are dependent of others, but they are not run properply and gives failure of the script.

In my situation it meant I had to use 'GO' to tell the DBMS to run the first, second and third sequantial instead of parallel. I changed all the ';' with GO in the script (in fact it has a whole lot more batches in it) and that did the trick. I'm not sure it is completely right to do it this way, but at least it worked. :)

Also see:

What is the use of GO in SQL Server Management Studio & Transact SQL?

When do I need to use Begin / End Blocks and the Go keyword in SQL Server?

Community
  • 1
  • 1
cybork
  • 569
  • 2
  • 5
  • 24
  • 4
    It's not to do with running a script sequentially or in parallel, it's to do with scope and compilation. The OPs script is failing at compile time because the table referenced in the script doesn't exist before the script starts. The use of GO statement delimits the scope of each batch in a script of multiple batches, each of which only gets compiled after all preceding batches have completed. The `;` symbol, conversely, is only a delimiter between individual Statements within a batch. – MatBailie Aug 31 '16 at 11:20