SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run same script for multiple clients


Run same script for multiple clients

Author
Message
Christian.stringer3
Christian.stringer3
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145729 Visits: 18652
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224161 Visits: 33573
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Lowell
Lowell
SSC Guru
SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)SSC Guru (185K reputation)

Group: General Forum Members
Points: 185033 Visits: 41569
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Christian.stringer3
Christian.stringer3
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 17
SQLRUNNR its from a database, what do you mean by doing select, where would I do that?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145729 Visits: 18652
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Christian.stringer3
Christian.stringer3
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145729 Visits: 18652
You are welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Christian.stringer3
Christian.stringer3
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 17
one last question, this didn't create a temp table did it?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145729 Visits: 18652
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search