July 15, 2011 at 10:06 am
Trying to generate a T-SQL script for create view, if it does not exist.
Instead of the traditional approach of "if exists drop and Create", would like create the
view if not exists.
Getting syntax error. Script below.
--------------------------
----------------------------
CREATE TABLE [dbo].[TestTable](
[TestCol2] [int] IDENTITY(1,2) NOT NULL,
[Description2] [varchar](50) NULL
) ON [PRIMARY]
GO
Insert into TestTable ([Description2])
select ('Test1')
union
select ('Test2')
GO
--------------Below code works...Can create the view-----------------
IF OBJECT_ID ('TestTable_view', 'V') IS NOT NULL
DROP VIEW TestTable_view ;
GO
CREATE VIEW TestTable_view
AS
SELECT testcol2, description2, 'VIEW' as SourceData from testtable
GO
----------------------------------------------------------------------
--Below code gives error. Trying to create if View if it does not exist.
-------------------------------------------------------------------
IF OBJECT_ID ('TestTable_view', 'V') IS NULL
BEGIN
CREATE VIEW TestTable_view
AS
SELECT testcol2, description2, 'VIEW' as SourceData from testtable
END
GO
July 15, 2011 at 10:26 am
The won't work because create view must be the first statement in a batch. What is wrong with dropping and recreating it? That would at least ensure that your view definition is current.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2011 at 10:51 am
Nothing wrong with "If exists....drop...create" approach.
Instead of performing two actions being done prior to Create, I was wondering if it could be minimized to perform only One action (check if not exists.)
July 15, 2011 at 1:45 pm
Please vote 😀
http://connect.microsoft.com/SQLServer/feedback/details/127219/create-or-replace
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 6:09 pm
satishchandra (7/15/2011)
Trying to generate a T-SQL script for create view, if it does not exist.Instead of the traditional approach of "if exists drop and Create", would like create the
view if not exists.
Getting syntax error. Script below.
--------------------------
----------------------------
CREATE TABLE [dbo].[TestTable](
[TestCol2] [int] IDENTITY(1,2) NOT NULL,
[Description2] [varchar](50) NULL
) ON [PRIMARY]
GO
Insert into TestTable ([Description2])
select ('Test1')
union
select ('Test2')
GO
--------------Below code works...Can create the view-----------------
IF OBJECT_ID ('TestTable_view', 'V') IS NOT NULL
DROP VIEW TestTable_view ;
GO
CREATE VIEW TestTable_view
AS
SELECT testcol2, description2, 'VIEW' as SourceData from testtable
GO
----------------------------------------------------------------------
--Below code gives error. Trying to create if View if it does not exist.
-------------------------------------------------------------------
IF OBJECT_ID ('TestTable_view', 'V') IS NULL
BEGIN
CREATE VIEW TestTable_view
AS
SELECT testcol2, description2, 'VIEW' as SourceData from testtable
END
GO
I feel your pain. I've written a lot of scripts where I need to generate many similar stored procedures and views for one reason or another but only if they don't already exist. Here's the workaround.
----------------------------------------------------------------------
--"CREATE VIEW" must be the first statement in a batch. The workaround
--is to use dynamic SQL to do what you want.
-------------------------------------------------------------------
IF OBJECT_ID ('TestTable_view', 'V') IS NULL
BEGIN
EXEC (
'CREATE VIEW TestTable_view
AS
SELECT testcol2, description2, ''VIEW'' as SourceData from testtable'
)
END
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2011 at 11:35 pm
I normally write such stubs the other way around, to avoid all that tedious messing about with quotes in the view definition:
IFOBJECT_ID(N'dbo.MyView', N'V')
IS NULL
EXECUTE ('CREATE VIEW dbo.MyView AS SELECT 1 AS dummy');
GO
ALTER VIEW dbo.MyView AS
SELECT * FROM master.dbo.spt_values;
GO
DROP VIEW dbo.MyView;
July 17, 2011 at 6:16 am
SQLkiwi (7/16/2011)
I normally write such stubs the other way around, to avoid all that tedious messing about with quotes in the view definition:...
+1 because of the lack of 'create or replace' in sqlserver.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 17, 2011 at 7:16 am
ALZDBA (7/17/2011)
SQLkiwi (7/16/2011)
I normally write such stubs the other way around, to avoid all that tedious messing about with quotes in the view definition:...
+1 because of the lack of 'create or replace' in sqlserver.
'create or alter' would also be nice to have for purposes of maintaining object-level security. It depends on the environment and how code is managed as to which method is most useful at the time, ALTER or DROP/CREATE. In environments where permissions are managed at the object level it makes sense to have permissions live with the object in version control so each script held onto their own grants/denies and were structured as DROP/CREATE/GRANT.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 17, 2011 at 8:05 am
opc.three...
IIRC, in the "CREATE or REPLACE" in Oracle (and, I believe, in the ANSI standard), the "REPLACE" is equivalent to an "ALTER".
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2011 at 8:16 am
SQLkiwi (7/16/2011)
I normally write such stubs the other way around, to avoid all that tedious messing about with quotes in the view definition:
IFOBJECT_ID(N'dbo.MyView', N'V')
IS NULL
EXECUTE ('CREATE VIEW dbo.MyView AS SELECT 1 AS dummy');
GO
ALTER VIEW dbo.MyView AS
SELECT * FROM master.dbo.spt_values;
GO
DROP VIEW dbo.MyView;
I forgot about that wonderful trick. That's really easy and effective for ad-hoc code and code to promote other code to production. It's probably just what the OP needed.
Shifting gears a bit, it's not so good for some of the stored procedures I (and others, I assume) write to build objects on the fly because both CREATE and ALTER must be the first line of code (except for comments) when building such objects.
Why would I build such objects? Simple... poor-man's partitioning using Partitioned Views in an SQL Server Standard Edition world. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2011 at 4:08 pm
Jeff Moden (7/17/2011)
opc.three...IIRC, in the "CREATE or REPLACE" in Oracle (and, I believe, in the ANSI standard), the "REPLACE" is equivalent to an "ALTER".
You're right. IMHO though REPLACE is ambiguous 🙂
These are a bit more to the point:
CREATE OR ALTER
CREATE OR DROP_CREATE
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply