Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining archive tables into a single table

Hi,
I had a table in my database which will update every month ... so we used to
update the table every month and stored the archieve tables in a seperate
database.
--ID is the primary key for this table and all historical of the record will
have the same ID
Now I have to combine all those tables(Around 30 tables and each had around
3k columns) into one table based on the primary key of current version table
.
-Each Archieve table had one Unique Cycle_id
Note: The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
Now,the structure of my new table can be the same as "current version" table
(this month) with additional field cycle_id
Pls try to help me guys, which way is better to achieve this."Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Hi,
> I had a table in my database which will update every month ... so we used
> to
> update the table every month and stored the archieve tables in a seperate
> database.
> --ID is the primary key for this table and all historical of the record
> will
> have the same ID
> Now I have to combine all those tables(Around 30 tables and each had
> around
> 3k columns) into one table based on the primary key of current version
> table.
> -Each Archieve table had one Unique Cycle_id
> Note: The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the time
> Now,the structure of my new table can be the same as "current version"
> table
> (this month) with additional field cycle_id
> Pls try to help me guys, which way is better to achieve this.
Take a look at the Partitioned Views topic in Books Online.
David Portas
SQL Server MVP
--|||David,
Thats a good idea ...i just went through that ,but the problem is to make
partioned view on partioned tables we need to have all smilar structure
tables.I think then only it will be possible to combine(Union) all those and
show it as One Table.
But in my case,as I said
-- The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
--And i have to add a column to uniquely represent which version it is(Is
there any other solution to ditinguish the versions)
"David Portas" wrote:

> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Take a look at the Partitioned Views topic in Books Online.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:18358F29-A0FB-45F5-9586-E30A127703E9@.microsoft.com...
> David,
> Thats a good idea ...i just went through that ,but the problem is to make
> partioned view on partioned tables we need to have all smilar structure
> tables.I think then only it will be possible to combine(Union) all those
> and
> show it as One Table.
> But in my case,as I said
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
>
> "David Portas" wrote:
>
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
That's easily fixed then - add the columns to the older tables. Why would
that be a problem?

> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
Yes you do have to add such a column. Without that your design is weak,
whether or not you choose to use a partitioned view. It's not generally a
good idea to have multiple tables of the same structure with duplicate data.
Any reason you didn't or don't combine them as a single table? Re-reading
your post it seems that was your actual question. The answer is just to
insert all the data to a common table using INSERT statements. Maybe I'm not
quite understanding what your problem is. Perhaps it would help if you
posted some sample DDL.
David Portas
SQL Server MVP
--|||If archived tables have less columns than the current table, simply add null
values to the union selects where the actual values are missing.
Also add a column that will contain a distinct value for each of the
partitions.
If you post some DDL we can give you a better illustration.
ML
http://milambda.blogspot.com/|||Thanks ML,David
My table is like huge one with almost 30 columns ..any way iam displaying
some of those for demonstration
--Lets say,this is my current version table and the newly creating should be
in this format
CREATE TABLE [dbo].[COPY_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL--This is newly added column and which is
not there in previous versions
)
I had another table "Cycle",which maintains ids of the previous version
tables..like
Cycle_ cycle Publication data table name
name
1005 2005-11-04 22:59:18.653 dbo.GLOBAL_CC_MASTER_1005
0905 2005-10-07 13:35:15.330 dbo.GLOBAL_CC_MASTER_0905
0805 2005-09-08 02:26:43.873 dbo.GLOBAL_CC_MASTER_0805
0705 2005-08-08 22:13:04.013 dbo.GLOBAL_CC_MASTER_0705
0605 2005-07-07 19:03:43.020 dbo.GLOBAL_CC_MASTER_0605
0505 2005-06-06 17:34:03.517 dbo.GLOBAL_CC_MASTER_0505
0405 2005-05-10 12:15:12.027 dbo.GLOBAL_CC_MASTER_0405
0305 2005-04-11 23:38:59.073 dbo.GLOBAL_CC_MASTER_0305
Now I have to add all these tables into one table
"Archieve_GLOBAL_CC_MASTER" and has to include 'cycle_name' as primary key
along with 'id_pk', which should be look like:
CREATE TABLE [dbo].[Archieve_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[CYCLE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,--composite Primary key,New column which is not there in current table
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL
)
I think this should help you for better understanding|||It may be late and I may need glasses, but I think you've just come up with
a
solution. Create the new table and migrate all data from the old tables,
creating missing values at insert.
ML
http://milambda.blogspot.com/|||Kumar wrote:
> Thanks ML,David
> My table is like huge one with almost 30 columns ..any way iam displaying
> some of those for demonstration
> --Lets say,this is my current version table and the newly creating should
be
> in this format
>
Like this:
INSERT INTO [dbo].[archive_global_cc_master]
(id_pk, cycle_name, batch_id, source_id, ledger_id, payables_id,
claim_status)
SELECT id_pk, 1005, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_1005
UNION ALL
SELECT id_pk, 0905, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0905
UNION ALL
SELECT id_pk, 0805, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0805
UNION ALL ... etc
I'm not clear what you want to do with your keys. Are other tables to
reference Archive on a surrogate IDENTITY key? If so you'll want to
assign a new IDENTITY in which case id_pk won't be IDENTITY in your
archive table.
Are you sure all those other columns need to be nullable? Are you sure
you have an alternate key in each table? If not you may have
duplicates. I'm not convinced that you have a sound design here to
start with, but that could be a mistaken assumption given that this is
just a fragment.
David Portas
SQL Server MVP
--

combine update statements....help...

Hi guys! Is there a way to combine these update statements?

Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @.syain_No", cnn)

Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @.syain_No", cnn)

The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...

Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)

Please help me. Thanks.

Audrey

You can do it in one statement. Understand the consequences first. Lets say you already have some records (say 5) in table TE_shounin_zangyou, your first INSERT will add some more rows to it. Your second UPDATE will update the rows from the insert as well as the existing rows. However, if you combine both the INSERT and the UPDATE into one statement you will only modofy the rows being INSERTED with the SELECT statement. Any pre-existing rows will not be affected. If, in your case, there would be NO pre-existing rows with the condition [syain_No] = @.syain_No, then you can do it all in one statement as follows:

Try this:

INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou,phase,phase_states2,syounin2_sysd)

SELECT syain_No,date_kyou,time_kyou,2,'06',CONVERT(VARCHAR(10),GETDATE(),101) FROM TE_zangyou WHERE [syain_No] = @.syain_No

Combine Add/Edit SP's?

Rather than having 2 separate stored procedures to add and update something
like a customer record, are there any drawbacks to having one stored proc
that does both? If the custID is passed in, then it would do the update, and
if the custID param is NULL than it would do an insert. Would this approach
have any performance implications?Personally, I like that design (which some refer to as "upsert"). An
efficient pattern you can follow is:
UPDATE Tbl
SET ...
WHERE custID = @.custID
--This means no row exists already
IF @.@.ROWCOUNT = 0
BEGIN
INSERT Tbl (...)
VALUES (...)
END
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> Rather than having 2 separate stored procedures to add and update
something
> like a customer record, are there any drawbacks to having one stored proc
> that does both? If the custID is passed in, then it would do the update,
and
> if the custID param is NULL than it would do an insert. Would this
approach
> have any performance implications?|||I Use this ALL the time, but add to it using the following "design pattern"
If @.PK Is Null
Begin
Insert (ColA, ColB, ColC, ...)
Values(@.ParameterA, @.ParameterB, @.ParameterC, ...)
Set @.PK = ScopeIdentity()
End
Else If Exists (Select * From Table
Where PK = @.PK)
Begin
-- Using IsNull allows you to NOT pass in a parameter
-- and thereby effectively NOT update it (Set Null
Default values)
Update Table Set
ColA = IsNull (@.ParameterA, ColA),
ColB = IsNull (@.ParameterB, ColB),
ColC = IsNull (@.ParameterC, ColC),
..
Where PK = @.PK
End
Else
Begin
Set Identity_Insert TableName On -- When PK Is IDentity
Insert (PK, ColA, ColB, ColC, ...)
Values(@.PK, @.ParameterA, @.ParameterB, @.ParameterC, ...)
Set Identity_Insert TableName Off -- When PK Is IDentity
End
-- And then at the end, regardless of which path was taken,
Select @.PK As PK
"Adam Machanic" wrote:

> Personally, I like that design (which some refer to as "upsert"). An
> efficient pattern you can follow is:
>
> UPDATE Tbl
> SET ...
> WHERE custID = @.custID
> --This means no row exists already
> IF @.@.ROWCOUNT = 0
> BEGIN
> INSERT Tbl (...)
> VALUES (...)
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> something
> and
> approach
>
>

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

column-defulat-value as auto-increment?

Thank you very much for the reply!
well, there is no way to update an identity column,
but is there a way to define a default value to a column
that makes auto-increment?
i tried in the SQL-Server-Enterprize-Manager\Design-Table
to set the default value of a column to MAX(recid_)+1...
any more ideas?
Thanks again,
edo.> well, there is no way to update an identity column,
> but is there a way to define a default value to a column
> that makes auto-increment?
> i tried in the SQL-Server-Enterprize-Manager\Design-Table
> to set the default value of a column to MAX(recid_)+1...
> any more ideas?
You could do this in a trigger, but maybe it might make more sense to
explain why you need this behavior.|||i made this trigger:
CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
FOR INSERT
AS
declare @.i1 int
declare c1 cursor for select Max(recid) from jobs
open c1
fetch next from c1 into @.i1
close c1
deallocate c1
update T1 set recid_=@.i1+1 where recid is NULL
do you think it's the most effecient way?
i doubt because i wonder first, wether i must open a
cursor, and second wether there is no direct way to
update the current inserted record, ruther than
search "where recid is NULL"
thank,
edo.
>--Original Message--
>> well, there is no way to update an identity column,
>> but is there a way to define a default value to a
column
>> that makes auto-increment?
>> i tried in the SQL-Server-Enterprize-Manager\Design-
Table
>> to set the default value of a column to MAX(recid_)
+1...
>> any more ideas?
>You could do this in a trigger, but maybe it might make
more sense to
>explain why you need this behavior.
>
>.
>|||You don't need a cursor:
SET @.i = (SELECT MAX(recid) FROM jobs)
Also, you use the INSERTED table to get the modified row(s).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"edo" <ewilde@.nana.co.il> wrote in message news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
> i made this trigger:
> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
> FOR INSERT
> AS
> declare @.i1 int
> declare c1 cursor for select Max(recid) from jobs
> open c1
> fetch next from c1 into @.i1
> close c1
> deallocate c1
> update T1 set recid_=@.i1+1 where recid is NULL
>
> do you think it's the most effecient way?
> i doubt because i wonder first, wether i must open a
> cursor, and second wether there is no direct way to
> update the current inserted record, ruther than
> search "where recid is NULL"
> thank,
> edo.
> >--Original Message--
> >> well, there is no way to update an identity column,
> >> but is there a way to define a default value to a
> column
> >> that makes auto-increment?
> >>
> >> i tried in the SQL-Server-Enterprize-Manager\Design-
> Table
> >> to set the default value of a column to MAX(recid_)
> +1...
> >> any more ideas?
> >
> >You could do this in a trigger, but maybe it might make
> more sense to
> >explain why you need this behavior.
> >
> >
> >.
> >|||Thanks for your helped,
i implemented your suggestion about the
SET @.i = (SELECT MAX(recid) FROM T1)
but i tried somthing like:
update inserted set recid=1
and got an error:
"the logical tables INSERTED and DELETED can not be
updated."
?
thanks agian,
edo.
>--Original Message--
>You don't need a cursor:
>SET @.i = (SELECT MAX(recid) FROM jobs)
>Also, you use the INSERTED table to get the modified row
(s).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"edo" <ewilde@.nana.co.il> wrote in message
news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
>> i made this trigger:
>> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
>> FOR INSERT
>> AS
>> declare @.i1 int
>> declare c1 cursor for select Max(recid) from jobs
>> open c1
>> fetch next from c1 into @.i1
>> close c1
>> deallocate c1
>> update T1 set recid_=@.i1+1 where recid is NULL
>>
>> do you think it's the most effecient way?
>> i doubt because i wonder first, wether i must open a
>> cursor, and second wether there is no direct way to
>> update the current inserted record, ruther than
>> search "where recid is NULL"
>> thank,
>> edo.
>> >--Original Message--
>> >> well, there is no way to update an identity column,
>> >> but is there a way to define a default value to a
>> column
>> >> that makes auto-increment?
>> >>
>> >> i tried in the SQL-Server-Enterprize-Manager\Design-
>> Table
>> >> to set the default value of a column to MAX(recid_)
>> +1...
>> >> any more ideas?
>> >
>> >You could do this in a trigger, but maybe it might
make
>> more sense to
>> >explain why you need this behavior.
>> >
>> >
>> >.
>> >
>
>.
>|||> update inserted set recid=1
You can't update the inserted / deleted tables.
Maybe you could show your table structure, sample data, and the results you
are trying to achieve, rather than have us reverse engineer your existing
trigger code. It might be that a trigger isn't even necessary for this, or
it might be that you could approach the trigger in a completely different
way. Your narrative a few posts back is difficult to follow, but might be
easier to understand if you show us your actual schema design. There might
be a much more efficient approach to whatever it is you mean by "cloning"...

Column update structure change

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

or any good recomendations of a action to accomplish this in the best and
fastest way.

thanks davepDave,

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you keep
under that limit when you add the #bytes from all of the other columns.

Access to the table, and possibly other objects that are dependent on that
table will be blocked until the schema change is complete. Also, other
non-dependent processes will also be slowed down significantly. A million
row table on recent sever technology should not take an excessive amount of
time to alter.

-- Bill

"DaveP" <dvs_bis@.sbcglobal.netwrote in message
news:S3Jyh.59511$wc5.47614@.newssvr25.news.prodigy. net...

Quote:

Originally Posted by

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server
>
or any good recomendations of a action to accomplish this in the best and
fastest way.
>
>
thanks davep
>
>

|||DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

This is a metadata change, so it will be about instant.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||AlterEgo (alterego55@.dslextreme.com) writes:

Quote:

Originally Posted by

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you
keep under that limit when you add the #bytes from all of the other
columns.

This applies to SQL 2000 only. On SQL 2005 rows can span pages. Not so
that you can have three char(8000) on the same page, but three varchar(8000)
is OK.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks for the responses...
dave

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98D24AF9ACFYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I want to increase a varchar(5000)
>to varchar(8000) on a table that
>has approximately million rows....
>What is the impact on the server

>
This is a metadata change, so it will be about instant.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

column update function

This is probably a common problem with a standard design pattern, but
I'm having trouble finding the solution.

I have a table with a lot of columns, for this example I'll just use
three but in reality its more like 20.

Create Table myTable (int col_one primary key, int col_two,
varchar(20) col_three) etc...

I want to write a sproc that allows updating of this column. Say I
have a sproc

create sproc myUpdate int @.col_one, int @.col_two, varchar(20)
col_three

as

update myTable col_two = @.col_two, col_three = @.col_three
where col_one = @.col_one

then if I only want to update col_two I have to pass in the current
value of col_three so that it remains the same, which seems pretty
inefficient. so I could change it to:

as

update myTable col_two = coalasce(@.col_two, col_two)
, col_three = coalasce(@.col_three, col_three)
where col_one = @.col_one

and then if I wanted to leave col_three the way it is then I could
just do

exec myUpdate 1, 2, NULL

the only problem here is that what if the value of col_three is
currently 3, and I want to set it to NULL? Under the current method,
setting someting to NULL is impossible

finally, I'd like to use parameter naming in my exec calls. that way
I can just say someting like

exec myUpdate 1, col_three=3

this would update col_three to 3 and leave the rest of the fields
untouched. you can see how handy this would be if you just want to
change a few of the fields in a table with a large number of columns.

I'm sure this has been done before, can somebody point me in the right
direction?

Thanks,

Benben (santoshamb@.yahoo.com) writes:
> update myTable col_two = coalasce(@.col_two, col_two)
> , col_three = coalasce(@.col_three, col_three)
> where col_one = @.col_one
> and then if I wanted to leave col_three the way it is then I could
> just do
> exec myUpdate 1, 2, NULL
> the only problem here is that what if the value of col_three is
> currently 3, and I want to set it to NULL? Under the current method,
> setting someting to NULL is impossible
> finally, I'd like to use parameter naming in my exec calls. that way
> I can just say someting like
> exec myUpdate 1, col_three=3
> this would update col_three to 3 and leave the rest of the fields
> untouched. you can see how handy this would be if you just want to
> change a few of the fields in a table with a large number of columns.

T-SQL is not a language that lends itself to this sort of thing. There
is no way to tell whether a parameter was passed explicitly or not. You
can of course test for NULL, but it may have been an explicit NULL.

One alternative would be to have extra marker variables to tell whether
a parameter applies or not. It quickly gets bulky. It can be reduced to
a single parameter which is a bitmask, but that is cryptic and error-prone.

What we do in our update procedures is to pass all column values. But
then we typically have read all to the GUI and now we are sending them
back. If some operation updates only affects a few columns, that is
typically an individual UPDATE statement in a different procedure.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

colors

Does anyone know if you can update background colors in RS 2005 using hex
values? It looks like you can only assign named colors. When I go to
custom colors and use the palette and assign a custom color, RS puts a hex
value in the expressio box, but it returns an error that it is an
un-recognized color.
Thanks
BillBill,
In theory you can use an expression for the background colour.
I have tried expressions like =rgb(255,0,255) which the Expression
Editor accepts as a syntactically correct expression. Stylistically
it's a bit overpowering! :)
However, although the expression shows in the textbox for the
BackgroundColor property in the Properties pane it is ignored when
previewing.
I assume it is a bug in September CTP.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
wrote:
>Does anyone know if you can update background colors in RS 2005 using hex
>values? It looks like you can only assign named colors. When I go to
>custom colors and use the palette and assign a custom color, RS puts a hex
>value in the expressio box, but it returns an error that it is an
>un-recognized color.
>Thanks
>Bill|||A color value in RDL is a either a color name or a hex HTML color string of
the form #HHHHHH. See also:
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
If you want to use the RGB function, you would need to convert the resulting
integer into a string. I.e.
=String.Format("#{0:x6}",RGB(255, 0, 255))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
> Bill,
> In theory you can use an expression for the background colour.
> I have tried expressions like =rgb(255,0,255) which the Expression
> Editor accepts as a syntactically correct expression. Stylistically
> it's a bit overpowering! :)
> However, although the expression shows in the textbox for the
> BackgroundColor property in the Properties pane it is ignored when
> previewing.
> I assume it is a bug in September CTP.
> Andrew Watt
> MVP - InfoPath
>
> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||Thanks, Robert.
Unfortunately the Expression Editor doesn't like your code (or my
attempts to correct it). Versions which the Expression Editor likes
seem to be ignored in display.
Nor have I found a way to express a custom colour using the Hex
notation that actually displays.
Any further suggestions?
Thanks
Andrew Watt
MVP - InfoPath
On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>A color value in RDL is a either a color name or a hex HTML color string of
>the form #HHHHHH. See also:
>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>If you want to use the RGB function, you would need to convert the resulting
>integer into a string. I.e.
>=String.Format("#{0:x6}",RGB(255, 0, 255))
>
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||The expression is valid - just try to run the report. The expression editor
marking the expression as invalid at design time is a bug.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
> Thanks, Robert.
> Unfortunately the Expression Editor doesn't like your code (or my
> attempts to correct it). Versions which the Expression Editor likes
> seem to be ignored in display.
> Nor have I found a way to express a custom colour using the Hex
> notation that actually displays.
> Any further suggestions?
> Thanks
> Andrew Watt
> MVP - InfoPath
> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>|||Thanks, Robert.
There also seem to be bugs in the Layout tab and in the Preview tab
relating to this.
I will wait and see what the situation is in RTM then, if necessary,
post some bugs to Product Feedback Center then.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 09:29:49 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>The expression is valid - just try to run the report. The expression editor
>marking the expression as invalid at design time is a bug.
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
>> Thanks, Robert.
>> Unfortunately the Expression Editor doesn't like your code (or my
>> attempts to correct it). Versions which the Expression Editor likes
>> seem to be ignored in display.
>> Nor have I found a way to express a custom colour using the Hex
>> notation that actually displays.
>> Any further suggestions?
>> Thanks
>> Andrew Watt
>> MVP - InfoPath
>> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
>> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>