create table if not exists syntax error

CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);

For some reason the above statements are giving me syntax errors?

I want to create table only if it does not already exist.

Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?

thx in advance .

IF NOT EXISTS
( SELECT [name]
FROM sys.tables
WHERE [name] = MyTable
)
CREATE TABLE MyTable (Col1 int IDENITY, etc. )

Use 'IF EXISTS' test to TRUNCATE or DROP.

Create Store Procedure to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION

Create Script

I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RMWhat error did you get exactly?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RM" <darasingha_nospam@.yahoo.com> wrote in message
news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RM|||Incorrect syntax - near primary (at the end of the first constraint
statement)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDhhMYzAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> What error did you get exactly?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "RM" <darasingha_nospam@.yahoo.com> wrote in message
> news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I am trying to use a script to create a table that already exists in one
DB
> in another DB. I used Enterprise Manager, "copied" the table and pasted
it
> into notepad which gave me the following script. However, I cannot get
SQL
> query analyzer to accept this script. Not sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Hello RM,
In your create script you have your table name as [dbo.ReportSite] which
if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
So change your script to be dbo.[ReportSite] and everything should be ok.
Aaron Weiker
http://aaronweiker.com/

> I am trying to use a script to create a table that already exists in
> one DB in another DB. I used Enterprise Manager, "copied" the table
> and pasted it into notepad which gave me the following script.
> However, I cannot get SQL query analyzer to accept this script. Not
> sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Thanks - worked.
RM.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:138326632422751463292620@.news.microsoft.com...
> Hello RM,
> In your create script you have your table name as [dbo.ReportSite] which
> if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
> So change your script to be dbo.[ReportSite] and everything should be ok.
> --
> Aaron Weiker
> http://aaronweiker.com/
>
>sql

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:[vbcol=seagreen]
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, Troy
Troy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy
|||Worked like a charm - Thanks
"Russell Fields" wrote:

> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
>
>

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:
>>
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, TroyTroy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy|||Worked like a charm - Thanks :)
"Russell Fields" wrote:
> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> > Hi,
> >
> > I have a problem executing this statement on sqlserver2005:
> >>
> > if not exists(select 1 from information_schema.schemata where
> > schema_name='testtest')
> > create schema TestTest AUTHORIZATION testuser;
> > go
> > <<<
> >
> > I get a "Incorrect syntax near the keyword 'schema'", so what am i
> > missing?!
> >
> > TIA, Troy
>
>sql

Create SCHEMA - Basic Question - 2005

I am trying to execute the following T-Sql snippet and it gives an
error:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
Error = Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SCHEMA'.
I can't for the life of me work out what's worng. Can someone point me
in the right direction please? Thanks.'create schema' must the be the first line in a batch. here is the
workaround.
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
Exec('CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]')
-oj
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1150474587.056097.296010@.c74g2000cwc.googlegroups.com...
>
> I am trying to execute the following T-Sql snippet and it gives an
> error:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
> CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
> Error = Msg 156, Level 15, State 1, Line 26
> Incorrect syntax near the keyword 'SCHEMA'.
> I can't for the life of me work out what's worng. Can someone point me
> in the right direction please? Thanks.
>|||> 'create schema' must the be the first line in a batch. here is the
> workaround.
Wouldn't it be nice if the error message were similar to the one you get
when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't this
error returned instead of incorrect syntax:
Msg 111, Level 15, State 1, Line 2
'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
A|||yeah...you know how to send a bug/wish report, right. ;-)
-oj
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23V9N94WkGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Wouldn't it be nice if the error message were similar to the one you get
> when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't
> this error returned instead of incorrect syntax:
> Msg 111, Level 15, State 1, Line 2
> 'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
>
> A
>sql

Create procedure error on computed column.

I have the following script that was generated using SMO:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[proc_InsertCaseNote]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Erin D. Rowley

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[proc_InsertCaseNote]

-- Add the parameters for the stored procedure here

@.ReasonCodeSubCategoryID int,

@.OrderGroupID uniqueidentifier,

@.NoteText text,

@.CustomerEmail varchar(75),

@.EmployeeFirstName varchar(255),

@.EmployeeLastName varchar(255)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)

values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY

END

GO

But when I try to run it (in SQL Management Studio) I get the following error:

Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18

The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any ideas on how to debug this problem?

Thank you.

Kevin

Please post the table DDL.|||

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

|||

Arnie Rowland wrote:

Please post the table DDL.

Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.

Kevin

|||

Louis Davidson wrote:

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.

Kevin

|||

Right click the table in SSMS, click "Script table to..."

The error is not really all that strange, it is not letting your procedure do something that won't work.

|||

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

|||

Tom Werz wrote:

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

The table looks like:

/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]

The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.

Reproduced here for reference.

USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY
END

Thank you for your suggestions.

Kevin

Create or update with updategram

Without knowing whether a row already exists in a table, is it possible to
construct an updategram that would create the row it does not exist or
update it if it does?No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Xerox" <info@.thinkscape.com> wrote in message
news:O1aI5TWIFHA.580@.TK2MSFTNGP15.phx.gbl...
> Without knowing whether a row already exists in a table, is it possible to
> construct an updategram that would create the row it does not exist or
> update it if it does?
>

Create or update with updategram

Without knowing whether a row already exists in a table, is it possible to
construct an updategram that would create the row it does not exist or
update it if it does?
No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Xerox" <info@.thinkscape.com> wrote in message
news:O1aI5TWIFHA.580@.TK2MSFTNGP15.phx.gbl...
> Without knowing whether a row already exists in a table, is it possible to
> construct an updategram that would create the row it does not exist or
> update it if it does?
>

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
Thanks for your help
Patrick
On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to
desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> > theprocedureexists...
> > AsCREATEPROCEDUREcan not be combined with any other Transact-SQL
> > statement in a single batch, I was wondering if there were any way to
> > achieve something like this :
> > (..)
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create file if does not exist but use if it does

I have the following SQL code. How can I put a check in here to see
if the file already exists and create it if not but insert the data
into it if it does? Thanks for your help
--[ Declare Variables ]--
DECLARE @.ThisWeek as smallDatetime
DECLARE @.ThisQtr as Integer
DECLARE @.ThisYear as Integer
DECLARE @.ThisYrQtr as Varchar(4)
DECLARE @.CmdStr as Varchar(1000)
--[ Populate the Variable with Month/Quarter/Year info ]--
Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
@.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>From dbHistory.dbo.tbQtrNdx
Where Mondate = fn_Mondate(GetDate())
--[ Build the SQL script to execute ZZZ123 archive ]--
Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
' ' +
'From dbHostdata.dbo.tbZZZ123 ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)
--[ Build the SQL script to execute Bills archive ]--
Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
' ' +
'From dbMetrics.dbo.tbBills ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)Why not use
if EXISTS (SELECT ...)
BEGIN
-- Update record
END
ELSE
BEGIN
-- create new record
END
-- End Else
taxidermist@.cableone.net wrote:
> I have the following SQL code. How can I put a check in here to see
> if the file already exists and create it if not but insert the data
> into it if it does? Thanks for your help
> --[ Declare Variables ]--
> DECLARE @.ThisWeek as smallDatetime
> DECLARE @.ThisQtr as Integer
> DECLARE @.ThisYear as Integer
> DECLARE @.ThisYrQtr as Varchar(4)
> DECLARE @.CmdStr as Varchar(1000)
>
> --[ Populate the Variable with Month/Quarter/Year info ]--
> Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
> @.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
> 'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>>From dbHistory.dbo.tbQtrNdx
> Where Mondate = fn_Mondate(GetDate())
>
> --[ Build the SQL script to execute ZZZ123 archive ]--
> Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
> ' ' +
> 'From dbHostdata.dbo.tbZZZ123 ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>
> --[ Build the SQL script to execute Bills archive ]--
> Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
> ' ' +
> 'From dbMetrics.dbo.tbBills ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>

Create Destination Table Dynamically in a program

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

Hi Pavan,

If you have the schema and the name, can't you execute a CREATE TABLE T-Sql statement?

Andy

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

The most I've done is supply a name dynamically and keep the mappings constant. I dont think you can do mappings dynamically.

|||

Pavan Kurimilla wrote:

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan

You can usually do this with a "select ... into destination_table from source_table..." statement. That SQL statement will create the table using the results of the select statement.

http://msdn2.microsoft.com/en-us/library/ms189499.aspx