July 30, 2014 at 10:26 am
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!
July 30, 2014 at 10:51 am
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
July 30, 2014 at 11:40 am
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
July 30, 2014 at 2:29 pm
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
July 31, 2014 at 2:21 pm
SQLRUNNR its from a database, what do you mean by doing select, where would I do that?
July 31, 2014 at 2:23 pm
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
July 31, 2014 at 2:37 pm
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.
July 31, 2014 at 2:38 pm
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
July 31, 2014 at 2:54 pm
one last question, this didn't create a temp table did it?
July 31, 2014 at 2:56 pm
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
July 31, 2014 at 3:49 pm
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)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply