• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

rerunable query

Hello,

I have this script that insert few records in two tables. I have to modify it so it colud be rerun.
Is there a simply way to do that.
thanks in advance.

script:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON;
GO

DECLARE @GID UNIQUEIDENTIFIER

INSERT INTO Error (ErrorCode) VALUES ('MPCD0102')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0102')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'There is no workspace version of the SiteNavigation for this user to check in.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0103')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0103')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'Display start date can not be more than 24 hours in the past.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0104')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0104')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'Workspace version is already checked out.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0105')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0105')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'@AuthenticatedUser_UserID is Required.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0106')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0106')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'SiteNavigationVersion can not be NULL.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0107')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0107')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'DisplayOrder can only be changed on current and future versions.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0108')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0108')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'DisplayOrder can not be changed on excluded versions.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0109')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0109')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'Adds are not allowed for a non-Workspace version of the SiteNavigation.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0110')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0110')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'Removes are not allowed for a non-Workspace version of the SiteNavigation.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0111')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0111')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'Moving categories to a different parent is not allowed for a non-WorkSpace version of the SiteNavigation.')

INSERT INTO Error (ErrorCode) VALUES ('MPCD0112')
SET @GID = (SELECT ErrorGID FROM Error WHERE ErrorCode = 'MPCD0112')
INSERT INTO [CMS].[dbo].[Localization]([LocalizationGID] ,[LangID] ,[Phrase])
     VALUES (@GID ,10000000 ,'There is no workspace version of the SiteNavigation for this user to save.')
0
SA4
Asked:
SA4
1 Solution
 
SQL_SERVER_DBACommented:
can't you associate with a scheduler, just like backups
0
 
SA4Author Commented:
this script needed to ran in the build, and for the recovery.
0
 
Bradley HaynesCommented:
Actually save this script as a Stored Procedure and you can Call when you need it.
It can be schedule for automation or run manually.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SA4Author Commented:
thanks for your responce b_haynes, but making it a Sproc will save it as an object in the db that I don't want. it will be run through cmd file.
0
 
Bradley HaynesCommented:
That's why it (sql server) supports script files... but you already knew that.  You
could use a script file and run OSQL from the SQL Server Agent. That way
you only have one file.
Check SQL server online for the process.
0
 
CragCommented:
I presume when you say you want to re-run the scipt that you want it to insert the rows in the table without generating duplicates or an error that a key already exists?

If that's the case then you need to make sure when the script is run that the rows are deleted if they already exist. This can be done with two statements:

-- drop the error messages
delete from [CMS].[dbo].[Localization] where [LocalizationGID] in (
SELECT ErrorGID FROM Error WHERE ErrorCode between ('MPCD0102', 'MPCD0112')
);
-- drop the error codes
delete from error where ErrorCode between ('MPCD0102', 'MPCD0112');
0
 
SA4Author Commented:
Thanks Crag, I was looking for something like that. But there is exsiting data in the table that might be same as 'MPCD0102'-- 'MPCD0112' which i don't want to delete. so I'll use if statment to check if the ErrorGID already exist if not then insert.

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now