Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Run same script for multiple clients Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 11:45 AM
Points: 5, Visits: 17
Hi, kind of new to sql so help would be much appreciated. I have a script that needs to be run for 50 different @ClientID. I dont want to run this script individually for each clientid. Would 'SET @clientID in (111, 222, 333) work? I've been told that it wouldn't. Short version of the script is.....

DECLARE @ClientID varchar (MAX)
DECLARE @UserID varchar (MAX)

SET @ClientID = 111 -- Replace with Client ID
SET @UserID = 5656 -- Replace with your ID

--VIDEO(1)
INSERT INTO document (documentTitle,actualFileName,clientID,documentDescription,documentCategoryLV,updateKey,savedUserID,savedDateTime,contentType,url,effectiveBeginDate,effectiveEndDate,featureYN,language,imageID,sequence,effectiveShowAsNewDate,javascriptURL,homePageYN,enrollmentYN,bestFitVideoYN,benefitPlanTypeID,eeMarqueeUntilDate,imageName,thumbnailImageName,thumbnailImageID) VALUES ('Health informational video','',@ClientID,'Health Rovion Video',16,1,@UserID,GETDATE(),3,'<a href="#this" onclick="try{playThisVideoNow(''20110824181720e66FsHYTkWv'');}catch(e){}">View Video on Health</a>','1/1/1900','12/31/9999',NULL,NULL,NULL,NULL,NULL,'<script type="text/javascript" src="https://engaged-by.rovion.com/play/20110824181720e66FsHYTkWv"></script>',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL)





Thanks!
Post #1597850
Posted Wednesday, July 30, 2014 10:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
Trying to set like that will not work.

e.g. Set @ClientID in (...)


Where is the source of this data?

If it is coming from a database, you can use a select instead of the values method.

If this is coming from an app (via delimited fashion maybe), then a loop would work with the values method you have there.
It is also possible to dump the values for clientid into a temp table and then use the select statement for the insert instead.

Make sense?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1597862
Posted Wednesday, July 30, 2014 11:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:46 AM
Points: 13,752, Visits: 28,148
Or, assuming you can get it in a delimited fashion, using Jeff's Tally Table methods to turn that into a derived table that you can then JOIN on.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1597878
Posted Wednesday, July 30, 2014 2:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
maybe something more along the lines of this?
With MyClients(ClientID,UserID)
AS
(
SELECT 111,5656 UNION ALL
SELECT 222,5656 UNION ALL
SELECT 333,5656
)
--INSERT INTO document (documentTitle,actualFileName,clientID,documentDescription,documentCategoryLV,updateKey,savedUserID,savedDateTime,contentType,url,effectiveBeginDate,effectiveEndDate,featureYN,language,imageID,sequence,effectiveShowAsNewDate,javascriptURL,homePageYN,enrollmentYN,bestFitVideoYN,benefitPlanTypeID,eeMarqueeUntilDate,imageName,thumbnailImageName,thumbnailImageID)
SELECT 'Health informational video','',ClientID,'Health Rovion Video',16,1,UserID,GETDATE(),3,'<a href="#this" onclick="try{playThisVideoNow(''20110824181720e66FsHYTkWv'');}catch(e){}">View Video on Health</a>','1/1/1900','12/31/9999',NULL,NULL,NULL,NULL,NULL,'<script type="text/javascript" src="https://engaged-by.rovion.com/play/20110824181720e66FsHYTkWv"></script>',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL
FROM MyClients



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1597972
Posted Thursday, July 31, 2014 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 11:45 AM
Points: 5, Visits: 17
SQLRUNNR its from a database, what do you mean by doing select, where would I do that?
Post #1598481
Posted Thursday, July 31, 2014 2:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
Christian.stringer3 (7/31/2014)
SQLRUNNR its from a database, what do you mean by doing select, where would I do that?


You used the INSERT ... VALUES method in your example.

If you look at the example pasted by Lowell, you will see that he showed an example using the INSERT ... Select method.


In fact, Lowell pretty much showed an example of what I was describing.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1598482
Posted Thursday, July 31, 2014 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 11:45 AM
Points: 5, Visits: 17
I tried his method and it didn't work, I realized I had the insert commented out, dumb mistake on my part. IT WORKS. Thanks to both of you.
Post #1598490
Posted Thursday, July 31, 2014 2:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
You are welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1598491
Posted Thursday, July 31, 2014 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 11:45 AM
Points: 5, Visits: 17
one last question, this didn't create a temp table did it?
Post #1598496
Posted Thursday, July 31, 2014 2:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
If you used the query provided by Lowell, it does not create a temp table. He used a cte.

But a temp table would be efficient in handling this type of thing too.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1598497
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse