Script to run same commands for different values 2

[annethorne]

Hi,

I want to write a script for our Release Engineers to run that does the following (which doesn't even work though this is the general idea ... and doesn't require my cutting and pasting so much). I think I need to perhaps create some arrays, and use a while statement? But, there may be better ways. I want the Release Engineers to just be able to run the script and forget about it, not need to install a function or stored procedure to help run it.

The general prinicipal, is that I want the data inserted, if the LookupName is not there, but updated if it is.

BEGIN TRANSACTION
BEGIN
Declare @LookupName char(50);
Declare @LookupValueFound char(4000);
Declare @LookupValue char(4000);


Set @LookupName = 'Shelternet_IsActive';
Set @LookupValue = 'true';
SELECT @LookUpValueFound = LookUpValue FROM LookUps WHERE LookupName = @LookUpName
IF @LookupValueFound IS NULL
Begin
INSERT INTO Lookups (LookUpName, LookUpValue) VALUES ( @LookupName, @LookupValue)
End
ELSE
Begin
UPDATE dbo.LookUps SET LookUpValue = @LookupValue WHERE LookupName = @LookupName
End



Set @LookupName = 'Shelternet_Message';
Set @LookupValue = 'March 8th marks International Women’s Day.[BREAK]Shelternet is an organization that empowers women affected by violence.[BREAK][CUSTOMERS_NAME]:Would you like to add a $1 donation to this order to help make a difference?';
SELECT @LookUpValueFound = LookUpValue FROM LookUps WHERE LookupName = @LookUpName
IF @LookupValueFound IS NULL
Begin
INSERT INTO Lookups (LookUpName, LookUpValue) VALUES ( @LookupName, @LookupValue)
End
ELSE
Begin
UPDATE dbo.LookUps SET LookUpValue = @LookupValue WHERE LookupName = @LookupName
End


Set @LookupName = 'Shelternet_ProductNum';
Set @LookupValue = '214';
SELECT @LookUpValueFound = LookUpValue FROM LookUps WHERE LookupName = @LookUpName
IF @LookupValueFound IS NULL
Begin
INSERT INTO Lookups (LookUpName, LookUpValue) VALUES ( @LookupName, @LookupValue)
End
ELSE
Begin
UPDATE dbo.LookUps SET LookUpValue = @LookupValue WHERE LookupName = @LookupName
End
END

GO



Any advice is appreciated.

Thanks!
Anne

 

[gmmastros]

Something like this should do the trick:

Declare @Lookup Table(LookupName VarChar(20), LookupValue VarChar(1000))

Insert Into @Lookup Values('Shelternet_IsActive'.'true')
Insert Into @Lookup Values('Shelternet_Message','March 8th marks International Women''s Day.[BREAK]Shelternet is an organization that empowers women affected by violence.[BREAK][CUSTOMERS_NAME]:Would you like to add a $1 donation to this order to help make a difference?')
Insert Into @Lookup Values('Shelternet_ProductNum','214')

Update	Lookups
Set     Lookups.LookupValue = L.LookupValue
From    Lookups
        Inner Join @Lookup As L
           On Lookups.LookupName = L.LookupName
           
Insert
Into    Lookups(LookupName, LookupValue)
Select  L.LookupName,
        L.LookupValue
From    @Lookups As L
        Left Join Lookups
           On L.LookupName = Lookups.LookupName
Where   Lookups.LookupName Is NULL

Note that you can easily add to the @Lookup table. If you want your data to contain a single-quote, you need to double it.

The update will only affect rows where the lookup names match.
The insert will only add rows where the lookup name in the @Lookup table is not found in the permanent Lookups table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[annethorne]

Thank you so very much!

This is a very useful technique.

I changed it only very slightly as follows:

BEGIN TRANSACTION


BEGIN
    Declare @Lookup Table(LookupName VarChar(50), LookupValue VarChar(5000))

Insert Into @Lookup Values('Shelternet_IsActive','true')
Insert Into @Lookup Values('Shelternet_Message','March 8th marks International Women''s Day.[BREAK]Shelternet is an organization that empowers women affected by violence.[BREAK][CUSTOMERS_NAME]:Would you like to add a $1 donation to this order to help make a difference?')
Insert Into @Lookup Values('Shelternet_ProductNum','214')

Update  Lookups
Set     Lookups.LookupValue = L.LookupValue
From    Lookups
        Inner Join @Lookup As L
           On Lookups.LookupName = L.LookupName
           
Insert
Into    Lookups(LookupName, LookupValue)
Select  L.LookupName,
        L.LookupValue
From    @Lookup As L
        Left Join Lookups
           On L.LookupName = Lookups.LookupName
Where   Lookups.LookupName Is NULL
END
GO

 

[sparkbyte]

Since your using Transactions don't you need to commit the Trans?

BEGIN TRANSACTION LookupOptions
	With Mark N'Adding Values to Temp Lookup Table';

BEGIN
    Declare @Lookup Table(LookupName VarChar(50), LookupValue VarChar(5000))

Insert Into @Lookup Values('Shelternet_IsActive','true')
Insert Into @Lookup Values('Shelternet_Message','March 8th marks International Women''s Day.
			[BREAK]Shelternet is an organization that empowers women affected by violence.
			[BREAK][CUSTOMERS_NAME]:Would you like to add a $1 donation to this order 
			to help make a difference?')
Insert Into @Lookup Values('Shelternet_ProductNum','214')

Update  Lookups
Set     Lookups.LookupValue = L.LookupValue
From    Lookups
        Inner Join @Lookup As L
           On Lookups.LookupName = L.LookupName
           
Insert
Into    Lookups(LookupName, LookupValue)
Select  L.LookupName,
        L.LookupValue
From    @Lookup As L
        Left Join Lookups
           On L.LookupName = Lookups.LookupName
Where   Lookups.LookupName Is NULL
END

COMMIT TRANSACTION LookupOptions;
GO

Thanks

John Fuhrman

 

[annethorne]

Yes... Thank you so much!!! Anne