Run same script for multiple clients

Question

Post reply

Run same script for multiple clients

Christian.stringer3

SSC Journeyman

Points: 91
More actions
July 30, 2014 at 10:26 am

#289389

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 @user-id varchar (MAX)
SET @ClientID = 111 -- Replace with Client ID
SET @user-id = 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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Or, assuming you can get it in a delimited fashion, using Jeff's Tally Table methods [/url]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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323504 More actions · Answer 3

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 SSC Journeyman Points: 91 More actions · Answer 4

SQLRUNNR its from a database, what do you mean by doing select, where would I do that?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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[/url]
Learn Extended Events

Christian.stringer3 SSC Journeyman Points: 91 More actions · Answer 6

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 SSC Guru Points: 281334 More actions · Answer 7

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[/url]
Learn Extended Events

Christian.stringer3 SSC Journeyman Points: 91 More actions · Answer 8

one last question, this didn't create a temp table did it?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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[/url]
Learn Extended Events

Christian.stringer3 SSC Journeyman Points: 91 More actions · Answer 10

Ya, I just didn't want to make a temp table, so this this method works perfectly. BUT how would I INSERT into multiple documents at the same time, for example

With MyClients(ClientID, UserID)

AS

(

SELECT 3479, 8792633 UNION ALL

SELECT 110605, 8792633

)

--HEALTH(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) 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

--MEDICAL(99)

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 'Medical informational video','',ClientID,'Medical Rovion Video',16,1,UserID,GETDATE(),3,'<a href="#this" onclick="try{playThisVideoNow(''20110824181720e66FsHYTkWv'');}catch(e){}">View Video on Medical</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,99,NULL,NULL,NULL,NULL)

--DENTAL(4)

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 'Dental informational video','',ClientID,'Dental Rovion Video',16,1,UserID,GETDATE(),3,'<a href="#this" onclick="try{playThisVideoNow(''20110824181602SvvmvCqTsWn'');}catch(e){}">View Video on Dental</a>','1/1/1900','12/31/9999',NULL,NULL,NULL,NULL,NULL,'<script type="text/javascript" src="https://engaged-by.rovion.com/play/20110824181602SvvmvCqTsWn"></script>',NULL,NULL,NULL,4,NULL,NULL,NULL,NULL)

--HSA(55)

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 'HSA informational video','',ClientID,'HSA Rovion Video',16,1,UserID,GETDATE(),3,'<a href="#this" onclick="try{playThisVideoNow(''20110824181742ItFenZQCyZy'');}catch(e){}">View Video on HSA</a>','1/1/1900','12/31/9999',NULL,NULL,NULL,NULL,NULL,'<script type="text/javascript" src="https://engaged-by.rovion.com/play/20110824181742ItFenZQCyZy"></script>',NULL,NULL,NULL,55,NULL,NULL,NULL,NULL)