Alter more than one view

**Dan Guzman** · Jan 4 '07, 12:25 PM

Re: Alter more than one view

You can include GO batch delimiter following each CREATE VIEW statement.
Tools like OSQL, SQLCMD, SSMS and Query Analyzer send the preceding batch of
SQL statements whenever a GO is encountered.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Siddu" <siddu.roy@gmai l.comwrote in message
news:1167892621 .237538.321780@ 31g2000cwt.goog legroups.com...

Hi All,
>
I am new to this group and this is my first doubt i am facing at
present.
>
>
I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.
>
I want to do it inside one single script. If i run the script all views
should get updated.
>
Any help on this will be greatful.
>
>
my mail id is siddu.roy@gmail .com.
>
Thanks in advance
>

**--CELKO--** · Jan 4 '07, 03:45 PM

Re: Alter more than one view

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.

That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.

**Alex Kuznetsov** · Jan 4 '07, 04:05 PM

Re: Alter more than one view

--CELKO-- wrote:

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

BEGIN TRANSACTION
go
CREATE VIEW aaa
AS
SELECT 1 n
go
SELECT n FROM aaa
/*
n
-----------
1

(1 row(s) affected)
*/
go
CREATE VIEW aab
AS
SELECT n FROM aaa
go
SELECT n FROM aab
/*
n
-----------
1

(1 row(s) affected)
*/
go
ROLLBACK
go
SELECT n FROM aaa
/*
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'aaa'.
*/
go
DROP VIEW aaa
DROP VIEW aab
/*
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'aaa', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'aab', because it does not exist in the system
catalog.
*/

-----------------------
Alex Kuznetsov

SQL Server Tips

http://sqlserver-tips.blogspot.com/

SQL Server Puzzles

http://sqlserver-puzzles.blogspot.com/

**Erland Sommarskog** · Jan 4 '07, 10:55 PM

Re: Alter more than one view

Alex Kuznetsov (AK_TIREDOFSPAM @hotmail.COM) writes:

--CELKO-- wrote:

>SQL Server is weird on this, but each VIEW statement has to be in a
>batch by itself. The reason is that VIEWs can be built on VIEWs, so
>you need to commit the first VIEW to do this.
>>

>
Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:

CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview

would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Damien** · Jan 5 '07, 08:25 AM

Re: Alter more than one view

--CELKO-- wrote:

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.

Hi Joe,

Since we're picking on your answer here, can I also point out that GO
is a keyword for query analyzer (by default) and for the command line
tools. It is *not* a keyword for SQL Server, and is never sent to the
server.

This becomes obvious if ever you try to comment out a batch of code
that includes GOs. Because Comments are intepreted by SQL Server, but
the GOs are interpreted by the tool, you'll get error messages galore
(unterminated comments, unexpected * found, etc), plus whatever is
batched within the GOs within the commented out block still get
executed.

Damien

**Alex Kuznetsov** · Jan 5 '07, 02:55 PM

Re: Alter more than one view

Erland Sommarskog wrote:

Alex Kuznetsov (AK_TIREDOFSPAM @hotmail.COM) writes:

--CELKO-- wrote:

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

>
Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:
>
CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview
>
would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yeah, right, his post makes more sence if one replaces 'commit' with
'submit'.

-----------------------
Alex Kuznetsov

SQL Server Tips

http://sqlserver-tips.blogspot.com/

SQL Server Puzzles

http://sqlserver-puzzles.blogspot.com/

**onedaywhen** · Jan 5 '07, 04:05 PM

Re: Alter more than one view

On Jan 4, 3:35 pm, "--CELKO--" <jcelko...@eart hlink.netwrote:

VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead.

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

Jamie.

--

**Erland Sommarskog** · Jan 5 '07, 11:15 PM

Re: Alter more than one view

onedaywhen (jamiecollins@x smail.com) writes:

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

And still be the only.

(And I would suggest that ; is not a statement terminator in T-SQL - It's
statement initiator.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Hugo Kornelis** · Jan 9 '07, 10:05 PM

Re: Alter more than one view

On Fri, 5 Jan 2007 23:13:00 +0000 (UTC), Erland Sommarskog wrote:

>onedaywhen (jamiecollins@x smail.com) writes:

>FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
>it must still be "the first statement in a query batch".

>
>And still be the only.
>
>(And I would suggest that ; is not a statement terminator in T-SQL - It's
>statement initiator.)

Hi Erland,

I would have to disagree with that suggestion. The ; is statement
terminator in ANSI, and has been the (optional) statement terminator in
T-SQL since at least SQL Server 2000 (but I think it was allowed in
earlier versions as well). The fact that *some* statements now require
the preceding statement to be terminated doesn't change it into a
statement initiator.

Check out the location of the ; in the syntax diagrams in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis