473,287 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Alter more than one view

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 si*******@gmail.com.

Thanks in advance

Jan 4 '07 #1
9 8262
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" <si*******@gmail.comwrote in message
news:11**********************@31g2000cwt.googlegro ups.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 si*******@gmail.com.

Thanks in advance
Jan 4 '07 #2
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.

Jan 4 '07 #3

--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
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Jan 4 '07 #4
Alex Kuznetsov (AK************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 4 '07 #5
--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

Jan 5 '07 #6

Erland Sommarskog wrote:
Alex Kuznetsov (AK************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Jan 5 '07 #7


On Jan 4, 3:35 pm, "--CELKO--" <jcelko...@earthlink.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.

--

Jan 5 '07 #8
onedaywhen (ja**********@xsmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 5 '07 #9
On Fri, 5 Jan 2007 23:13:00 +0000 (UTC), Erland Sommarskog wrote:
>onedaywhen (ja**********@xsmail.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
Jan 9 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ...
6
by: tim | last post by:
In view of the mixed bag of answers to the "positioning: newbie question" thread, and in view of the fact that the float and the positioning seems to cause trouble (again and again people have...
10
by: Jane | last post by:
Does any one know why this statement is failing? db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51) DB21034E The command was processed as an SQL statement because it was...
4
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
3
by: Rajesh Kumar Mallah | last post by:
Hi, Looks like alter table does not tells about the indexes it dropped PG version: 7.4.3 Regds mallah.
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
1
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table...
2
by: nkumarin001 | last post by:
Hi, Can any one please help me in this matter. I have created a table :- create table Employee ( id number, name varchar2(10), city varchar2(10)
4
by: smanojgroup | last post by:
I have a view defined as select * on base table. When base table is altered to add new column, new column does not appear when selected from view. Here is what I did to test. db2 "create table...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.