Triggers Prevent Updates To Multiple Records

Updates To Multiple Records
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?

Many thanks for any advice.

View Replies !

---

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..
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...
 
Regards
Karen

View Replies !

---

Batch Updates And Triggers
Hello All

I am running a statment that updates 248 records in a table. That table has a trigger on update.

The First thing that the trigger does is to declare a number of variables and then set them using the values from the inserted and deleted temp tables.

This works fine when you are updating one row at a time but when you run a batch update it errors.

I believe this is because i am assuming that in a batch update a single row will be updated and then the trigger fired, then the next row and so on.

I now know that this is not the case. Is there a way of linking the 3 variables (see Below) so they all return information form a single row.

Declare@ApplicantId Int,
@NewStatus Int,
@OldStatus Int

Set @ApplicantId = (Select ApplicantID from Inserted)
Set @NewStatus = (Select StatusID from Inserted)
Set @OldStatus = (Select StatusID from Deleted)

Hope that makes sense

Thanks for any help.

Steve

View Replies !

---

Cascading Updates/Deletes With Triggers
Microsoft article Q142480 states "Triggers cannot be used to perform cascading updates and deletes if ForeignKey-to-PrimaryKey relationships have been extablished using SQL Server's DRI."

Does this mean that I cannot declare FK's in my scripts if I want to have triggers in the table? Do I just add a column in my table that will have a foreign key in it, but just not reference it in my script? Can someone clarify for me?

Thanks,
Nathan

View Replies !

---

Prevent Duplicate Records
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.

View Replies !

---

How To Prevent Ordering Of Records....
hello,

i use VC# and sql server 2005.

i create a table using the following command :

create table history(name varchar(20) primary key);

I insert records in a particular order.After all the records are inserted and when I try to retrieve the records, all of them are sorted in a ascending order.Is there any way to prevent them from being sorted.

pls reply asap.....

- Sweety

View Replies !

---

Multiple Triggers On A Table Or Encapsulated Triggers
This isn€™t an problem as such, it€™s more of a debate. 
 
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger.  Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed.   I was wondering in terms of maintenance and best practice etc.  My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
 
www.handleysonline.com

View Replies !

---

How To Prevent Table-locking With Multiple Queue Readers
 

In a situation where messages are coming in faster than they can be processed, at what point will service broker start up another queue_reader? Also, how do you prevent table locking if part of the processing of that message involves inserting or updating data in a table? We are experiencing this problem because of the high number of messages coming through, and I'm not sure what the best solution is - does service broker have some built-in support for preventing contention on a table when multiple readers are running? Or maybe a pattern that can be used to get around it?

View Replies !

---

DataAdapter.Update Updates Zero Records
I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?

View Replies !

---

Tracking User Updates On Records In SQL 7.0
Does anyone know of a good strategy for tracking which user was the last to update a record. I have attempted putting a trigger on the table so that when certain fields are updated the field called 'userid' will be set something like the following

UPDATE userid
SET userid = user

Obviously this changes the 'userid' field for every record to the value of the current user. I guess I am having some trouble with the WHERE clause of this statement. I just want the current record, the one whose updates are firing the trigger, to be modified. Is there a better way to do this? I guess to position the update I have to set a cursor on this record but I have so far had no luck. ANy tips?

Anthony

View Replies !

---

Multiple Row Updates
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?

Thanks you
Jeff

View Replies !

---

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?
 

View Replies !

---

Trace Auditing For ( New Records And Updates For A Particular Date) - SQL Server 2000 Sp4
Hi,


I have few tables. I want to identify the RECORDS for a table which has been created/modified for a particular date and time. I don't want to write a trigger to capture the event for add/update.

 

Is there any system table which track for date and time using stored procedure each individual records which has been last updated or newly created records??

 

Note : The application already created without lastModified date and each table... so, we don't want to modify the application or db. 

Database : SQL Server 2000 sp4

 

thanks in advance.

View Replies !

---

How To Replace The Existing Records In The Table From Updates In The Transactional File
 

Hi  i  am getting a weekly transaction file  which has two columns    trans code and trans date  to indicate   whether the record is changed, added  or modified .  and the monthly master file contains  blank in these two fields.
 
How do i update the row  coming from the transaction file  in the  tables   which contain the rows from the master file .
 
to better explain  the  example is
Master File
 
ID Name  AGE   Salary  Transcode   TransDate
 2    dev      27      2777     
 
Transaction File  indicating change
 
ID   Name  AGE   Salary  Transcode   TransDate
 2       dev      27      24444      C              08072007
 
 
the ouput should be
 
  2       dev      27      24444      C              08072007  
 
replacing the existing row in the table(updating the whole row)

 i have 50 columns in my table  and based on the  two fields i  should replace the rows exisiting in table and if ID doesnot match exist  just add them as a new row.
 
 what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and  if  there doesnt exist matching id  just add them as new row.
 
Thanks...

View Replies !

---

Multiple Updates In Store Procedure?
I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?

View Replies !

---

Advise On Multiple Column Updates
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

View Replies !

---

UPDATEs With Multiple Aggregate Functions
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!

View Replies !

---

Multiple Text Updates To A Single Row
I have a table A that has related records in table B.  I need to run an update to concatonate certian values in table B into a single value in table A. 
 
Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

View Replies !

---

Multiple Updates And Identity Fields
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.

 

TIA,

 

Barkingdog

 

View Replies !

---

Multiple Record Updates In One Statement
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.

Any help would be appreciated. Many thanks.

View Replies !

---

Problem: Trigger And Multiple Updates To The Table
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.

View Replies !

---

How To Catch Multiple Updates Done To A Table With A Trigger?
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?
 
 
Following is the table structure:
 
Customer_Master(custmastercode, customer_company_name,updated_by)
 
Following is the trigger:
 

ALTER TRIGGER [TR_UPDATE_CUST]

ON [dbo].[CUSTOMER_MASTER]

AFTER UPDATE

AS

BEGIN



SET NOCOUNT ON;



 
IF EXISTS (SELECT * FROM inserted)

BEGIN

 
declare @custcode int

Declare @message varchar(5000)

Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int 

Declare @CUSTOMER_COMPANY_NAME varchar(50)
 
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '

 

select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted

 
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '

 
IF(update([CUSTOMER_COMPANY_NAME]))

Begin

select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted

select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted

Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'

Set @message = @message + @custommessage

End
 

Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'

 
INSERT INTO [CHANGE_HISTORY]

([CUSTMASTERCODE]

,[CHANGE_DETAILS])

VALUES (@custcode, @message)

END

END

View Replies !

---

Triggers Not Recognized When Running SP To Insert Set Of Records
I wrote a trigger that works fine when I insert record by record in the DB.
However, when I run a Stored Proc to insert a bunch of records at the same time, the trigger only works for the last record.

Anyone has a clue or a possible solution.
The trigger is well tested and works fine.

Best Regards,

Gabriel Cohen
gabrielc@yahoo.com

View Replies !

---

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View Replies !

---

Multiple Triggers
When a trigger on one table updates another table, does it activatethe update trigger on the updated table?For ex:1. Table A has a trigger for update2. Table B has a trigger for update that updates table AWill the trigger no table A run when table B trigger updates table A?Thanks

View Replies !

---

Triggers - Run For Multiple Updated
I am new to trigger and I have following question.

I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.

Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?

I hope I make it clear and please help me on this! :o

View Replies !

---

Multiple Update Triggers
What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire

View Replies !

---

One Trigger Vs Multiple Triggers ?
 

I have a question regarding Triggers.
 
Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.
 
Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.
 
Or I can create three different triggers each for Insrt, update and delete.
 
Which option is better for performance?
 
Thanks
 
 
 
 

View Replies !

---

Selecting Multiple Records By Multiple Filters...
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew

View Replies !

---

Delete Multiple Records From Multiple Tables
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')


delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

View Replies !

---

Selecting Multiple Records From Multiple Tables
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.

ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.

View Replies !

---

Can SSRS 2005 Handle Stored Procedures Or SQL Subqueries That Rreturn Rowsets Based On Multiple SQL Updates?
Hello,

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?

Here is the stored procedure:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 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
 
 --SET @StartDate = NULL
 --SET @EndDate = NULL
 --SET @TerseMode = 1
 
 SET NOCOUNT ON 

 CREATE TABLE #programWatched
 (
  --IPTV device ID
  tdeviceId     uniqueidentifier NULL,
  taccountId     uniqueidentifier NULL,
  
  -- 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,
  
  toriginDateTime   datetime  NULL,
  tduration    bigint   NULL,
  tprogramType   nvarchar(100) NULL,
  tchannelCallName  nvarchar(20) NULL,
  
  --Rating
  programMPAARating  varchar(50) NULL,
  programVCHIPRating  varchar(50) NULL,
  programMPAARatingVal smallint  NULL,
  programVChipRatingVal smallint  NULL,
  
  -- Categories
  programGenre   varchar(50) NULL,
  programCategory1  varchar(50) NULL,
  programCategory2  varchar(50) NULL,
  programCategory3  varchar(50) NULL,
  programCategory4  varchar(50) NULL,
  
  -- Roles  
  programActor1FirstName varchar(50) NULL,
  programActor1LastName varchar(50) NULL,
  programActor1   varchar(100) NULL,
  
  programActor2FirstName varchar(50) NULL,
  programActor2LastName varchar(50) NULL,
  programActor2   varchar(100) NULL,
  
  programActor3FirstName varchar(50) NULL,
  programActor3LastName varchar(50) NULL,
  programActor3   varchar(100) NULL,

  programActor4FirstName varchar(50) NULL,
  programActor4LastName varchar(50) NULL,
  programActor4   varchar(100) NULL,
  
  programActor5FirstName varchar(50) NULL,
  programActor5LastName varchar(50) NULL,
  programActor5   varchar(100) NULL,
  
  programActor6FirstName varchar(50) NULL,
  programActor6LastName varchar(50) NULL,
  programActor6   varchar(100) NULL,
  
  programActor7FirstName varchar(50) NULL,
  programActor7LastName varchar(50) NULL,
  programActor7   varchar(100) NULL,
  
  programActor8FirstName varchar(50) NULL,
  programActor8LastName varchar(50) NULL,
  programActor8   varchar(100) NULL,
  
  programDirectorFirstName varchar(50) NULL,
  programDirectorLastName  varchar(50) NULL,
  programDirector   varchar(100) NULL,
  
  programWriterFirstName varchar(50) NULL,
  programWriterLastName varchar(50) NULL,
  programWriter   varchar(100) NULL,
  
  programProducerFirstName varchar(50) NULL,
  programProducerLastName varchar(50) NULL,
  programProducer   varchar(100) 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,
  
  FlagOrdinalValue  smallint NULL,
  
  -- Channel
  tchannelId    int NULL,
  
  callLetters    varchar(20) NULL,
  displayName    varchar(50) NULL,
  type     varchar(50) NULL,
  networkAffiliation  varchar(50) 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,

       ClosedCaption,
       InStereo,
       Repeats,
       New,
       Live,
       Taped,
       Subtitled,
       SAP,
       ThreeD,
       Letterbox,
       HDTV,
       Dolby,
       DVS
       )
  SELECT  pw.DeviceID,
    pw.programID,
    --epg.program,
    --epg.programTitle,
    --epg.programEpisodeTitle,
    pw.originTime AS 'When Watched',
    pw.Duration AS 'Duration Seconds',
    --epg.programType,
    --epg.channelCallName,

    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,

       ClosedCaption,
       InStereo,
       Repeats,
       New,
       Live,
       Taped,
       Subtitled,
       SAP,
       ThreeD,
       Letterbox,
       HDTV,
       Dolby,
       DVS
       )
  SELECT  pw.DeviceID,
    pw.programID,
    --epg.program,
    --epg.programTitle,
    --epg.programEpisodeTitle,
    pw.originTime AS 'When Watched',
    pw.Duration AS 'Duration Seconds',
    --epg.programType,
    --epg.channelCallName,

    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 programDirector = programDirectorLastName + ' , ' + programDirectorFirstName
       
 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 programWriter = programWriterLastName + ' , ' + programWriterFirstName
       
 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 programProducer = programProducerLastName + ' , ' + programProducerFirstName
       
 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 programActor1 = programActor1LastName + ' , ' + programActor1FirstName
         
 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 programActor2 = programActor2LastName + ' , ' + programActor2FirstName
         
 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 programActor3 = programActor3LastName + ' , ' + programActor3FirstName
         
 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 programActor4 = programActor4LastName + ' , ' + programActor4FirstName
         
 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 programActor5 = programActor5LastName + ' , ' + programActor5FirstName
         
 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 programActor6 = programActor6LastName + ' , ' + programActor6FirstName
         
 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 programActor7 = programActor7LastName + ' , ' + programActor7FirstName
      
 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))

 UPDATE #programWatched
 SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName

 -- 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
  
 DROP TABLE #programWatched

 SET NOCOUNT OFF

GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help is appreciated

 

View Replies !

---

Creating Multiple Triggers Is Same Sql Script
I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

View Replies !

---

Triggers Affecting Multiple Tables
I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".

My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.

Gazzou

View Replies !

---

Multiple Update Triggers Or One Large Trigger With If&#39;s
I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated.
Thanks

View Replies !

---

Creating Triggers Among Multiple Database Servers
Hello,

I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?

Any help is appreciated!!
Thanks,
Lisa

View Replies !

---

Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?
 

Hey!
 
DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?

View Replies !

---

Transactional Replication - Propagating Updates As Updates
 

I am using SQL2005 transactional replication and need singleton updates to be propagate as updates and not as delete/insert  pairs as I am currently seeing.
 
In SQL 2000 - Trace flag T8207 can be used as described below

http://support.microsoft.com/kb/302341

The relevant extact from the article is as follows

"Organizations may find that sending updates to subscribers as DELETE and INSERT operations does not meet their business needs. For example, business rules might require update triggers to fire at the Subscriber. To accommodate these situations a new trace flag, 8207, is introduced in SQL Server 2000 Service Pack 1, which enables singleton updates for Transactional Replication. An update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair. "


How can I get the SQL 2000 behaviour on SQL 2005?  I have been advised that T8207 is not supported on SQL 2005.  I did try using T8207, but found that SQL then bugchecked whenever data was moved between partitions
 

Any suggestions would be much appreciated
 

aero1

 

View Replies !

---

Multiple Records And Sub Records
I'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(

I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.

I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.

Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?

Thank you very much for any help,
Mischa

View Replies !

---

Multiple Records In One Row
Hello,I have a table that contains multiple prices for multiplelocation (yes I know is should have been done with an Xref table orsomething, but I didn't create it and it's too late to do right now).The records are similar to this...priceID, productID (non-unique), productName, locationID, price1,100, prod1, 1, $3.002,101, prod2, 1, $4.003,102, prod3, 1, $2.004,101, prod1, 2, $9.005,102, prod2, 2, $5.006,103, prod3, 2, $8.00What I would like is for the output islocation1Name, Product1Price, Product2Price, Product3Pricelocation2Name, Product1Price, Product2Price, Product3Pricelocation3Name, Product1Price, Product2Price, Product3PriceI have already written the proc so that if prices aren't found for aproduct at a particular location it will return null records. The partI can't figure out is how to retrieve the records in the format that Ihave shown above. I've got about 6+ hours in on this and haven't foundanything helpful enough on google or Books online yet so if someonecan help I will be very thankful.

View Replies !

---

Multiple Records, Only Want One
I have a query that returns multiple identical records, however it shouldonly return one. Indeed there is only one record for the OrderActionTypecodeof 'P' yet there are two orderactions so it returnd both.Oddly the second query returns only the desired single record but wihtoutall the additional fields I need. Does the Inner join somehow mess with thequery?ThanksQuery 1SELECT Orders.OrderID, Orders.TicketID, A.AttemptID, E.EventID,E.AssetCode AS Asset, Orders.FK_Prod_Alias AS Alias, Orders.ContractCode,L.OrderLegCode AS OrderLeg, M.MarketActionName ASAction, Orders.OrderVolume AS Volume, Orders.OrderPrice AS Price,T.OrderTypeName AS OrderType, S.StatusName AS Status,Orders.FilledVolume AS Filled, Orders.OriginalOrderDateTime,Orders.PlaceOrderDateTime,Orders.MonitorFlagFROM Orders INNER JOINOrderAction OA ON Orders.OrderID = OA.OrderID INNERJOINAttempt A ON Orders.AttemptID = A.AttemptID INNER JOINEvent E ON A.EventID = E.EventID INNER JOINMarketAction M ON Orders.MarketActionCode =M.MarketActionCode INNER JOINOrderLegs L ON Orders.OrderLegCode = L.OrderLegCodeINNER JOINOrderType T ON Orders.OrderTypeCode = T.OrderTypeCodeINNER JOINOrderState S ON Orders.Status = S.Status ANDOrders.OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P'))Query 2SELECT OrderID, TicketIDFROM OrdersWHERE (OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P')))

View Replies !

---

Multiple Records
Hi there...

I am new at sql programming and I need all the help i can possibly get..Im using SQL Server 2000 and I have been given an assignment which follows:

- i have to add 100 new records at once to an existing table.
I have to do it through SQL Server only( query analizer,i suppose) and I am not allowed to use any other languages to achieve this. Now, my question is how can i do it through sql only? What is the code i have to write to make this thing work? I simply dont know where and how to start...

thanx in advance and sorry for my bad english

View Replies !

---

Multiple Records
I am trying to join seperate records into one return set.
IE. select accountNum, flag1, flag2, flag3 from MyDB where flag1='A' or flag2='B' or Flag3='C'

The problem is that there could be 2 records with the same account number but flag1 ='A'
Flag2='B' ect.

This would return to lines. Is there a way that I can have all return sets show up on one line only.
Eliminating duplicate return sets???

View Replies !

---

Multiple Records
Dear ALL,
I have two tables with named Reports and Backup_Reports with same fields. Id(numeric), MobileNumber(varchar), SmsBody(varchar),date(datetime),Status(char).
Here what I want is, if I am searching some records by entering the mobile number then the sql query first will search on the first table Reports then it will show the related records and again the query will search on the second table Backup_Reports then will show related records from this table also.
What I mean is, if I am searching some records by mobilenumber first will search on the Reports and then will go to second table Backup_Reports and will show all the related records together.
How can I handle this?

regards
Shaji

View Replies !

---

Help With Inserting Multiple Records Using A CSV Value.
I have the Temporary table:ItemDetailID (int)FieldID  (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice? 

View Replies !

---

Inserting Multiple Records Using For Each
Hi, I am a rookie at sql and asp.net.  I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section.
I am collecting multiple rows from a gridview.  I validated/confirmed I am capturing the correct values.  How ever, I am having major problems passing these to an sql database.  The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement.  I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem.  Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck.
Regards!
 Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then     chkBox = True     gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString      Response.Write(ddIDs + " " + gvIDs + " ")     If addChkBx1Item.Checked Then          chkBox1 = True          Response.Write("Defaut Item")    End If          Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry     Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)"     Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn)     cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString)     cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString)     cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox))     cn.Open()
      For Each .....          Problems here also
      Next
     cmd.ExecuteNonQuery()Catch err As SqlException     Response.Write(err.Message.ToString)Finally     cn.Close()End TryEnd If
 
 

View Replies !

---

Question About Get Multiple Records !!!
I have table with format :
ID          Name          ParentID1            England            02            Canada            03            Manchester      14            Chelsi               15            Arsenal             16            Canada_1         27            Canada_2         28            MU_1              39            MU_2              310          MU_1_1          8....
Now , I write a function which to get information ,such as : England ---- Manchester ----MU_1----MU_1_1 (*)My Function :    Function GetInformation(ID as int16) as string           ' my code here     End function
My problem : (With ID=10)When i pass this ID , i have to get string which format : (*)So , what is " my code here " ?Thank any suggestions ,any advices ....And thank you very much.
 
 
 
 
 
 
 
 

View Replies !

---

Inserting Multiple Records At Once
Hi,

I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.

Then insert 15 records in the child table.

Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?

Thanks!

View Replies !

---