May 8, 2012 at 11:46 pm
How can I update multiple values in 1 table based on the values from another table, do i need to use cursor ? or any loop ?
every time there may be different values in temptable or sometime there may be no values at all. in this particular example there are three columns for which I need to update the values in temptable2
from temptable1 i.e Name, Phone and Address
I know when updating we need to give field names and field values however in this case this will be coming from another table at runtime.
Can I use any loop to execute
Update dbo.temptable set ColumnValue = val1
where ColumnName = val2
For you to see what I have please use following code
drop table #temptable
Create Table #temptable
( id int not null primary key,
val1 varchar(50),
val2 varchar(50))
insert into #temptable(id,val1,val2)
values
(1,'Name','John'),
(2,'Phone','1111111'),
(3,'Address','Newton Australia')
select * from #temptable
drop table #temptable2
Create Table #temptable2
( columnname varchar(50),
columnvalue varchar(50))
insert into #temptable2(columnname)
values
('Address'),
('idnumber'),
('Phone'),
('OfficeNumber'),
('Surname')
select * from #temptable2
Umar Memon
May 9, 2012 at 12:28 am
Pls explain more clearly.
May 9, 2012 at 12:44 am
Let us know the expected o/p
May 9, 2012 at 1:19 am
Currently all fields in Temptable 2 are Null.
Output of Temptable 2 would be after an update statement would be as follows
Address = Newton Australia
Phone = 111111
idnumber = NULL
OfficeNumber = NULL
Surname = NULL
Currently all fields in Temptable 2 are Null.
these values for phone and address are going to be taken from Temp Table 1 as only these 2 column names are present in Table 1. rest of fields in temp table 2 will be null as it is.
I hope now you understand what I mean, let me know if any further explanation is required
May 9, 2012 at 1:56 am
I think this is ur expecting result
UPDATE #temptable2
SET columnvalue =CAse When columnname ='Address' THEN (SELECT VAl2 from #temptable WHERE val1 ='Address')
When columnname ='idnumber' THEN (SELECT VAl2 from #temptable WHERE val1 ='idnumber')
When columnname ='Phone' THEN (SELECT VAl2 from #temptable WHERE val1 ='Phone')
When columnname ='OfficeNumber' THEN (SELECT VAl2 from #temptable WHERE val1 ='OfficeNumber')
When columnname ='Surname' THEN (SELECT VAl2 from #temptable WHERE val1 ='Surname')
ELSE NULL END
Address Newton Australia
idnumber NULL
Phone 1111111
OfficeNumber NULL
Surname NULL
May 9, 2012 at 2:56 am
Are you implying that you need a Trigger?
here is something that I worked up. Hope it helps:
--Creating Tables
Create Table Ex
(id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work
val1 varchar(50),
val2 varchar(50) )
Create Table Ex2
(columnname varchar(50),
columnvalue varchar(50) )
--Inserting Sample Data into tables
Insert Into Ex
Select 'Name', 'John'
Union ALL
Select 'Phone', '1111111'
Union ALL
Select 'Address', 'Newton Australia'
Insert Into Ex2 (columnname)
values
('Address'),
('idnumber'),
('Phone'),
('OfficeNumber'),
('Surname')
--Create Trigger
Alter Trigger Tr
On Ex
For Insert
As
Begin
Declare @colname varchar(50), @value varchar(50), @sql varchar(max)
Select @colname = Val1, @value = val2 From Ex
Where Id IN (Select Id From Inserted)
If(@Colname IN ('Address','Phone'))
Begin
Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'
Execute (@sql)
End
Else
Begin
Return
End
End
--Test Execution Of trigger
Insert Into Ex
Select 'Phone', '2222222'
Select * From Ex
Select * From Ex2
May 9, 2012 at 8:06 am
vinu512 (5/9/2012)
Are you implying that you need a Trigger?here is something that I worked up. Hope it helps:
--Creating Tables
Create Table Ex
(id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work
val1 varchar(50),
val2 varchar(50) )
Create Table Ex2
(columnname varchar(50),
columnvalue varchar(50) )
--Inserting Sample Data into tables
Insert Into Ex
Select 'Name', 'John'
Union ALL
Select 'Phone', '1111111'
Union ALL
Select 'Address', 'Newton Australia'
Insert Into Ex2 (columnname)
values
('Address'),
('idnumber'),
('Phone'),
('OfficeNumber'),
('Surname')
--Create Trigger
Alter Trigger Tr
On Ex
For Insert
As
Begin
Declare @colname varchar(50), @value varchar(50), @sql varchar(max)
Select @colname = Val1, @value = val2 From Ex
Where Id IN (Select Id From Inserted)
If(@Colname IN ('Address','Phone'))
Begin
Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'
Execute (@sql)
End
Else
Begin
Return
End
End
--Test Execution Of trigger
Insert Into Ex
Select 'Phone', '2222222'
Select * From Ex
Select * From Ex2
That trigger will not work when there are multiple rows in a single insert. Consider what would happen if you ran the following:
Insert Into Ex
Select 'Phone', '2222222' union all
select 'Phone1', '321321'
And why the dynamic sql? I don't think that is needed in this case.
I did not rewrite this trigger due to a lack of information from the OP. If they come back and say that a trigger is what they need then I will happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 11:15 pm
Sean Lange (5/9/2012)
vinu512 (5/9/2012)
Are you implying that you need a Trigger?here is something that I worked up. Hope it helps:
--Creating Tables
Create Table Ex
(id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work
val1 varchar(50),
val2 varchar(50) )
Create Table Ex2
(columnname varchar(50),
columnvalue varchar(50) )
--Inserting Sample Data into tables
Insert Into Ex
Select 'Name', 'John'
Union ALL
Select 'Phone', '1111111'
Union ALL
Select 'Address', 'Newton Australia'
Insert Into Ex2 (columnname)
values
('Address'),
('idnumber'),
('Phone'),
('OfficeNumber'),
('Surname')
--Create Trigger
Alter Trigger Tr
On Ex
For Insert
As
Begin
Declare @colname varchar(50), @value varchar(50), @sql varchar(max)
Select @colname = Val1, @value = val2 From Ex
Where Id IN (Select Id From Inserted)
If(@Colname IN ('Address','Phone'))
Begin
Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'
Execute (@sql)
End
Else
Begin
Return
End
End
--Test Execution Of trigger
Insert Into Ex
Select 'Phone', '2222222'
Select * From Ex
Select * From Ex2
That trigger will not work when there are multiple rows in a single insert. Consider what would happen if you ran the following:
Insert Into Ex
Select 'Phone', '2222222' union all
select 'Phone1', '321321'
And why the dynamic sql? I don't think that is needed in this case.
I did not rewrite this trigger due to a lack of information from the OP. If they come back and say that a trigger is what they need then I will happy to help.
Nice catch Sean.
The above trigger is just a general idea which the OP can use to get to where he wants.
When I read the OP's post, the first thought that came to my mind is that the OP is trying to insert values from a front end application, maybe using Drop Down boxes. I could be wrong, its just a Hunch I had. I could be right as well as the OP wasn't very descriptive about his requirement.
I started working with the Hunch and came up with the Trigger. If the OP inserts from the application, the following might not happen:
Insert Into Ex
Select 'Phone', '2222222' union all
select 'Phone1', '321321'
Because, only one row will be inserted at a time.
And why the dynamic sql? I don't think that is needed in this case.
Anyways, its just a Hunch. I could be wrong.
Going by the OP's first post, he was unsure. This was just my try at getting him up and running with some possibilities.
May 10, 2012 at 7:47 am
vinu512 (5/9/2012)
Sean Lange (5/9/2012)
vinu512 (5/9/2012)
AreNice catch Sean.
The above trigger is just a general idea which the OP can use to get to where he wants.
I was just pointing out that the general idea needed to be tweaked to handle multiple rows.
When I read the OP's post, the first thought that came to my mind is that the OP is trying to insert values from a front end application, maybe using Drop Down boxes. I could be wrong, its just a Hunch I had. I could be right as well as the OP wasn't very descriptive about his requirement.
I started working with the Hunch and came up with the Trigger. If the OP inserts from the application, the following might not happen:
Insert Into Ex
Select 'Phone', '2222222' union all
select 'Phone1', '321321'
You have to assume that anything in a trigger will have multiple rows. Just because it is designed to be used with an application there will be a point where some sort of CRUD operation will involve multiple rows. Maybe some data was deleted accidentally or whatever. That situation ALWAYS happens at some point and any triggers MUST be able to handle it.
There was a story in another thread a few months ago by one of the regulars about a company that went belly up because their triggers couldn't handle multiple rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 10, 2012 at 6:45 pm
Sean Lange (5/10/2012)
There was a story in another thread a few months ago by one of the regulars about a company that went belly up because their triggers couldn't handle multiple rows.
Sean - I would really like to read that article if you could find it.
We have an application that we support here and it ran up against just this issue. I hit upon the fact that ALL triggers should be able to support multiple rows (I call them bulk triggers) and not just single rows as a common sense thing without even the need to seek advice. Ultimately I went back through that app and rewrote every single bloody trigger in the thing to handle bulk ops.
The article would be useful to me to impress this point upon others not as enlightened.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 12:08 am
Is this better Swan?
--Creating Tables
Create Table Ex
(id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work
val1 varchar(50),
val2 varchar(50) )
Create Table Ex2
(columnname varchar(50),
columnvalue varchar(50) )
--Inserting Sample Data into tables
Insert Into Ex
Select 'Name', 'John'
Union ALL
Select 'Phone', '1111111'
Union ALL
Select 'Address', 'Newton Australia'
Insert Into Ex2 (columnname)
values
('Address'),
('idnumber'),
('Phone'),
('OfficeNumber'),
('Surname')
--Create Trigger
Alter Trigger Tr
On Ex
For Insert
As
Begin
Declare @cnt int
Declare @colname varchar(50), @value varchar(50), @sql varchar(max), @sql1 varchar(max)
Declare @tbl Table(id int, val1 varchar(50), val2 varchar(50) )
Insert Into @tbl
Select id, val1, val2 From Inserted
Select @cnt = Count(*) From Ex as a JOIN Inserted as i ON a.id = i.id
Where i.val1 IN ('Address', 'Phone')
While(@cnt > 0)
Begin
Select @colName = i.val1, @value = i.val2 From Ex as a JOIN @tbl as i ON a.id = i.id
Where i.id IN (Select Top 1 id From @tbl) AND i.val1 IN ('Address', 'Phone')
Select @sql = 'Update Ex2 Set ColumnValue = '''+@value+''' Where ColumnName = '''+@colname+''';'
Execute (@sql)
Delete From @tbl Where Id IN (Select Top 1 id From @tbl)
Select @cnt = @cnt - 1
End
End
--Test Execution Of trigger
Insert Into Ex
Select 'Name', 'Allen'
Union ALL
Select 'Phone', '2222222'
Union ALL
Select 'Address', 'Australia'
Select * From Ex
Select * From Ex2
This works for multiple row inserts.
It could be better....but this is just my "raw" attempt at it.
May 11, 2012 at 12:37 am
Vinus - I think there's a simpler solution with no need for dynamic SQL in the trigger.
--drop table temptable
Create Table temptable (id int not null primary key, val1 varchar(50), val2 varchar(50))
GO
--drop table temptable2
Create Table temptable2 (columnname varchar(50), columnvalue varchar(50))
GO
CREATE TRIGGER OnInsertTempTable ON Temptable AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE t
SET columnvalue = val2
FROM temptable2 t
INNER JOIN INSERTED ON columnname = val1
END
GO
insert into temptable2(columnname)
SELECT 'Address'
UNION ALL SELECT 'idnumber'
UNION ALL SELECT 'Phone'
UNION ALL SELECT 'OfficeNumber'
UNION ALL SELECT 'Surname'
select * from temptable2
insert into temptable(id,val1,val2)
SELECT 1,'Name','John'
UNION ALL SELECT 2,'Phone','1111111'
UNION ALL SELECT 3,'Address','Newton Australia'
select * from temptable
SELECT * FROM temptable2
DROP TRIGGER OnInsertTempTable
drop table temptable
drop table temptable2
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 2:58 am
dwain.c (5/11/2012)
Vinus - I think there's a simpler solution with no need for dynamic SQL in the trigger.
--drop table temptable
Create Table temptable (id int not null primary key, val1 varchar(50), val2 varchar(50))
GO
--drop table temptable2
Create Table temptable2 (columnname varchar(50), columnvalue varchar(50))
GO
CREATE TRIGGER OnInsertTempTable ON Temptable AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE t
SET columnvalue = val2
FROM temptable2 t
INNER JOIN INSERTED ON columnname = val1
END
GO
insert into temptable2(columnname)
SELECT 'Address'
UNION ALL SELECT 'idnumber'
UNION ALL SELECT 'Phone'
UNION ALL SELECT 'OfficeNumber'
UNION ALL SELECT 'Surname'
select * from temptable2
insert into temptable(id,val1,val2)
SELECT 1,'Name','John'
UNION ALL SELECT 2,'Phone','1111111'
UNION ALL SELECT 3,'Address','Newton Australia'
select * from temptable
SELECT * FROM temptable2
DROP TRIGGER OnInsertTempTable
drop table temptable
drop table temptable2
You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.
Nice One!! 🙂
May 11, 2012 at 3:23 am
vinu512 (5/11/2012)
You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.
Nice One!! 🙂
Thanks Vinus! I'll take that as a compliment. 😀
Note that I have a sneaking suspicion that this can also be done with a MERGE statement and no trigger. Unfortunately I am not in a position to try that at the moment.
Maybe later when I'm in my SQL 2008 playground.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 7:42 am
dwain.c (5/11/2012)
vinu512 (5/11/2012)
You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.
Nice One!! 🙂
Thanks Vinus! I'll take that as a compliment. 😀
Note that I have a sneaking suspicion that this can also be done with a MERGE statement and no trigger. Unfortunately I am not in a position to try that at the moment.
Maybe later when I'm in my SQL 2008 playground.
I didn't really look back through very closely but I think you are right about using MERGE Dwain. Of course if you stick with the trigger methodology the one you wrote looks nice and clean.
I can help with the MERGE if the OP is interested. Otherwise something like what Dwain posted should work nicely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply