Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Is it possible to do an update on multiple records. I have fields CusOrderID and CusCreditAmt in table CusOrder; and fields CusOrderID and CreditAmt in table CusCredits. I would like to update the value in the CusCreditAmt field in CusOrder to equal the CreditAmt field in CusCredits where CusOrderID is the same in both tables.
I tried doing this in a stored procedure based on a View with an inner join between the 2 tables as follows:
Update View1 Set CusCreditAmt = CreditAmt
I received an error that the subquery returned more than one value. Is there anyway to do something like this and make it work?
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date] INTO temp1 FROM [G_L Entry] as A ,[G_L Account] as B where A.[G_L Account No_]= B.[No_] SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_] INTO temp2
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example. There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
Using SQL2000, is there another way of optimizing the original query below.TIA,BobUpdate Transactionset field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder wheretblOrder.id = Transaction.id),field2 = (select avg(price) from tblOrder where tblOrder.id =Transaction.id),field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)etc..(there are six additional updates)Is this a quicker way:Update TransactionSET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,field3 = min(cost) , etc.....FROM tblOrderWHERE tblOrder.id = Transaction.id
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE table1 SET col1 = 0 WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
What is the best possible way to make it work? Thank you.
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
-- File: sp_GetProgramsWatchedByDateRange.sql -- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse. -- Note that some of that data used by this procedure are obtained from the RMS_EPG database -- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file. -- Auth: H Hunsaker -- Date: 11/07/2006
-- Example invocation -- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description -- 3. StartDate datetime First date of reporting period -- 4. EndDate datetime Last date of reporting period -- TerseMode bit Return all columns? (1 = no, 0 = yes) -- 5. AsXML bit Resultset format (0 = standard, 1 = XML) -- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange @StartDate datetime = NULL, @EndDate datetime = NULL, @TerseMode bit = 0, @AsXML bit = 0, @Debug bit = 0 AS -- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse. -- So I am going to the RMS_EPG database to obtain that information. -- We will have to ensure that the 2 databases are generated at the same or a matching time -- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing -- DECLARE @StartDate datetime -- DECLARE @EndDate datetime -- DECLARE @TerseMode bit
-- Basic program information tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db. tprogramId uniqueidentifier NULL, -- programID generated by IPTV tprogramTitle varchar(150) NULL, tprogramEpisodeTitle varchar(100) NULL, tprogramDescription varchar(500) NULL,
-- Flags ClosedCaption bit NULL, InStereo bit NULL, Repeats bit NULL, New bit NULL, Live bit NULL, Taped bit NULL, Subtitled bit NULL, SAP bit NULL, ThreeD bit NULL, Letterbox bit NULL, HDTV bit NULL, Dolby bit NULL, DVS bit NULL,
-- I store the program watching data in a temp table because -- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid. -- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system AND originTime BETWEEN @StartDate AND @EndDate ELSE INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId UPDATE #programWatched SET taccountId = (SELECT accountId FROM DW_BRDB_bm_device d WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId) -- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs UPDATE #programWatched SET tchannelCallName = (SELECT TOP 1 channelCallName FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags) UPDATE #programWatched SET programMPAARating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched SET programMPAARatingVal = CASE programMPAARating WHEN 'G' THEN 10 WHEN 'PG' THEN 25 WHEN 'PG-13' THEN 30 WHEN 'R' THEN 35 WHEN 'NC-17' THEN 50 WHEN 'NRAO' THEN 60 WHEN 'NR' THEN 0 ELSE 0 END
UPDATE #programWatched SET programVChipRating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched SET programVChipRatingVal = CASE programVChipRating WHEN 'TV-Y' THEN 10 WHEN 'TV-Y7' THEN 20 WHEN 'TV-G' THEN 35 WHEN 'TV-PG' THEN 40 WHEN 'TV-14' THEN 45 WHEN 'TV-MA' THEN 60 ELSE 0 END
-- Genre UPDATE #programWatched SET programGenre = (SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId WHERE tprogram = pc.programID)
-- Categories UPDATE #programWatched SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID)
UPDATE #programWatched SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles UPDATE #programWatched SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programDirectorLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programWriterLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programProducerLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1) UPDATE #programWatched SET programActor1LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor2LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor3LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor4LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor5LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor6LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor7LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched SET programActor8LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
-- Channel (provider) Call Letters, Display Name and Type -- Is this correct? Should we get the channelId from the schedule table? -- Is this efficient? View execution plan
UPDATE #programWatched SET tchannelId = (SELECT TOP 1 c.channelId FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram)
UPDATE #programWatched SET callLetters = (SELECT TOP 1 c.channelCallLetters FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET displayName = (SELECT TOP 1 c.channelDisplayName FROM RMS_EPG..channel c JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET type = (SELECT TOP 1 c.channelType FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0 SELECT * FROM #programWatched ORDER BY toriginDateTime ELSE -- Get only Genre, title, show date/time, rating, call letters SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName FROM #programWatched ORDER BY toriginDateTime
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer, meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
SELECT PW.DeviceID, PW.originTime AS 'When Watched', PW.programID, PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW ORDER BY DeviceId, programId, originTime
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).
The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.
I have a number of questions:
How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?
Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.
What is being passed? Can you pass an array or something to a stored procedure?
As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.
UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer] WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));
okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?
here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)
update t1 set books_music='' from mailorder t1 where books_music is null update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null update t1 set Computer='' from mailorder t1 where Computer is null update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null
in FoxSlow (foxpro) i could just do something like this:
do while !eof() replace books_music with '' for books_music=null replace car_buff with '' for car_buff=null skip enddo
I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?
Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes': 1) "Update tblDept SET IsTop ='No' 2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 " Cheers!
Hi, I just started learning ASP.NET this week and have watched a mountain of videos from this website which has helped me alot However I have been stuck on a problem for 2 days now. I have created an SQL database with the Following 2 tables: USERS COMPUTERSUserid Computerid firstname Useridsecondname Manufacturer Model I have made a relationship between the 2 tables. I then created a dataset with the following query:SELECT COMPUTERS.Computer_ID, COMPUTERS.Manufacturer, COMPUTERS.Model, USERS.First_Name, USERS.Last_Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID I however do not get the option in my grid view when i output this data, to UPDATE. The best i have found from google is that i need to use subqueries and not innerjoins but i just cant seem to get my head around them, please help as i feel my head may just explode if i think about this or try any more ways to get this to work :D
I am using MSDE Release A was wondering what is the best way to find out when there are critical updates for MSDE. Is there a notification service for MSDE like there is for Windows?
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty WHERE item_no=@item_no
UPDATE Items SET Sales=@Total_Sales, Qty=@Total_Qty WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
Hi I am using SQL Server 6.5 as my backend for a VB6 Application. This is a OLTP kind of package that I have developed with about 600 Users. I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application. My database has grown considerably and all of a sudden, all the CommitTran failed. None of the records were saved.
When I stopped and restarted my SQL Server, the operation became normal all Update/Insert queries for the same Application succeeded. (Note that I have not stopped my Server for the last 45 days).
WHY DOES THIS HAPPEN? HOW DO I AVOID THIS IN FUTURE... PLEASE HELP Thanks Jivee
How would I check a db to see if a record exists and if it does then do an update but if it doesn't do an insert. so: Set oRS = objDB.Execute("select loginid from computers where loginid='" & WshNetwork.Computername & WshNetwork.UserName & "'")
If that returns something then do an update but if not then do the insert.
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions
well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(
Hello guys, I am askng for any help ...am trying to get this SQL language.
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. -------------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
i want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. Thank you in advance. Shcema---------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
