|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
update all columns if exists, or do the insert based on pkI have base table and temp table with new data. I need to update
changed columns of the base table , if pk exists or do the insert. Any examples for doing this? The table is having more than 50 columns, I need to update changed data. I would appreciate any thoughts. Trame Something like this:
If EXISTS ( SELECT PKCol FROM MyBaseTable WHERE PKCol = @PKParam ) UPDATE MyBaseTable SET Col1 = t.Col1 , Col2 = t.Col2 , etc = t.etc FROM MyBaseTable b JOIN MyTempTable t WHERE b.PKCol = t.PKCol ELSE INSERT INTO MyBaseTable ( Collist ) SELECT (ColList FROM MyTempTable t WHERE t.PKCol = @PKParam Perhaps other interesting ideas will come out too... -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." <trame2***@hotmail.com> wrote in message news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... >I have base table and temp table with new data. I need to update > changed columns of the base table , if pk exists or do the insert. Any > examples for doing this? The table is having more than 50 columns, I > need to update changed data. I would appreciate any thoughts. > > Trame > Thanks. What I am looking for is dynamic sql for statement for SET
statement. As I've mentioned the table has more than 50 columns. Arnie Rowland wrote: Show quote > Something like this: > > If EXISTS > ( SELECT PKCol > FROM MyBaseTable > WHERE PKCol = @PKParam > ) > UPDATE MyBaseTable > SET Col1 = t.Col1 > , Col2 = t.Col2 > , etc = t.etc > FROM MyBaseTable b > JOIN MyTempTable t > WHERE b.PKCol = t.PKCol > ELSE > INSERT INTO MyBaseTable > ( Collist ) > SELECT (ColList > FROM MyTempTable t > WHERE t.PKCol = @PKParam > > Perhaps other interesting ideas will come out too... > > -- > Arnie Rowland > "To be successful, your heart must accompany your knowledge." > > > > <trame2***@hotmail.com> wrote in message news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... > >I have base table and temp table with new data. I need to update > > changed columns of the base table , if pk exists or do the insert. Any > > examples for doing this? The table is having more than 50 columns, I > > need to update changed data. I would appreciate any thoughts. > > > > Trame > > When you are writing the query, open object browser (query analyzer) or
object explorer (management studio). Find your table on the left hand side, expand it until you see "Columns", and drag that folder-looking node into the query window. Wow, magic, huh? And no lazy^H^H^H^Hdynamic SQL required. A <trame2***@hotmail.com> wrote in message Show quote news:1152905054.022213.201600@m79g2000cwm.googlegroups.com... > Thanks. What I am looking for is dynamic sql for statement for SET > statement. As I've mentioned the table has more than 50 columns. > > > Arnie Rowland wrote: >> Something like this: >> >> If EXISTS >> ( SELECT PKCol >> FROM MyBaseTable >> WHERE PKCol = @PKParam >> ) >> UPDATE MyBaseTable >> SET Col1 = t.Col1 >> , Col2 = t.Col2 >> , etc = t.etc >> FROM MyBaseTable b >> JOIN MyTempTable t >> WHERE b.PKCol = t.PKCol >> ELSE >> INSERT INTO MyBaseTable >> ( Collist ) >> SELECT (ColList >> FROM MyTempTable t >> WHERE t.PKCol = @PKParam >> >> Perhaps other interesting ideas will come out too... >> >> -- >> Arnie Rowland >> "To be successful, your heart must accompany your knowledge." >> >> >> >> <trame2***@hotmail.com> wrote in message >> news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... >> >I have base table and temp table with new data. I need to update >> > changed columns of the base table , if pk exists or do the insert. Any >> > examples for doing this? The table is having more than 50 columns, I >> > need to update changed data. I would appreciate any thoughts. >> > >> > Trame >> > > Good idea.
Aaron Bertrand [SQL Server MVP] wrote: Show quote > When you are writing the query, open object browser (query analyzer) or > object explorer (management studio). Find your table on the left hand side, > expand it until you see "Columns", and drag that folder-looking node into > the query window. Wow, magic, huh? And no lazy^H^H^H^Hdynamic SQL > required. > > A > > > <trame2***@hotmail.com> wrote in message > news:1152905054.022213.201600@m79g2000cwm.googlegroups.com... > > Thanks. What I am looking for is dynamic sql for statement for SET > > statement. As I've mentioned the table has more than 50 columns. > > > > > > Arnie Rowland wrote: > >> Something like this: > >> > >> If EXISTS > >> ( SELECT PKCol > >> FROM MyBaseTable > >> WHERE PKCol = @PKParam > >> ) > >> UPDATE MyBaseTable > >> SET Col1 = t.Col1 > >> , Col2 = t.Col2 > >> , etc = t.etc > >> FROM MyBaseTable b > >> JOIN MyTempTable t > >> WHERE b.PKCol = t.PKCol > >> ELSE > >> INSERT INTO MyBaseTable > >> ( Collist ) > >> SELECT (ColList > >> FROM MyTempTable t > >> WHERE t.PKCol = @PKParam > >> > >> Perhaps other interesting ideas will come out too... > >> > >> -- > >> Arnie Rowland > >> "To be successful, your heart must accompany your knowledge." > >> > >> > >> > >> <trame2***@hotmail.com> wrote in message > >> news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... > >> >I have base table and temp table with new data. I need to update > >> > changed columns of the base table , if pk exists or do the insert. Any > >> > examples for doing this? The table is having more than 50 columns, I > >> > need to update changed data. I would appreciate any thoughts. > >> > > >> > Trame > >> > > > Good idea.
Aaron Bertrand [SQL Server MVP] wrote: Show quote > When you are writing the query, open object browser (query analyzer) or > object explorer (management studio). Find your table on the left hand side, > expand it until you see "Columns", and drag that folder-looking node into > the query window. Wow, magic, huh? And no lazy^H^H^H^Hdynamic SQL > required. > > A > > > <trame2***@hotmail.com> wrote in message > news:1152905054.022213.201600@m79g2000cwm.googlegroups.com... > > Thanks. What I am looking for is dynamic sql for statement for SET > > statement. As I've mentioned the table has more than 50 columns. > > > > > > Arnie Rowland wrote: > >> Something like this: > >> > >> If EXISTS > >> ( SELECT PKCol > >> FROM MyBaseTable > >> WHERE PKCol = @PKParam > >> ) > >> UPDATE MyBaseTable > >> SET Col1 = t.Col1 > >> , Col2 = t.Col2 > >> , etc = t.etc > >> FROM MyBaseTable b > >> JOIN MyTempTable t > >> WHERE b.PKCol = t.PKCol > >> ELSE > >> INSERT INTO MyBaseTable > >> ( Collist ) > >> SELECT (ColList > >> FROM MyTempTable t > >> WHERE t.PKCol = @PKParam > >> > >> Perhaps other interesting ideas will come out too... > >> > >> -- > >> Arnie Rowland > >> "To be successful, your heart must accompany your knowledge." > >> > >> > >> > >> <trame2***@hotmail.com> wrote in message > >> news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... > >> >I have base table and temp table with new data. I need to update > >> > changed columns of the base table , if pk exists or do the insert. Any > >> > examples for doing this? The table is having more than 50 columns, I > >> > need to update changed data. I would appreciate any thoughts. > >> > > >> > Trame > >> > > > There are several approaches to what is typically referred to . If the pk
is clustered, this should be as fast as any: UPDATE Table SET foo = @foo, bar = @bar, ... WHERE pk = @pk; IF @@ROWCOUNT = 0 INSERT table(pk, foo, bar, ...) SELECT @pk, @foo, @bar, ... <trame2***@hotmail.com> wrote in message Show quote news:1152903746.298356.265150@i42g2000cwa.googlegroups.com... >I have base table and temp table with new data. I need to update > changed columns of the base table , if pk exists or do the insert. Any > examples for doing this? The table is having more than 50 columns, I > need to update changed data. I would appreciate any thoughts. > > Trame > > There are several approaches to what is typically referred to . as UPSERT or MERGE.A Aaron Bertrand [SQL Server MVP] wrote:
Show quote > > There are several approaches to what is typically referred to . > > as UPSERT or MERGE. > > Upsert is not avaialble in sql server UPSERT in sql server 2000? I
Aaron Bertrand [SQL Server MVP] wrote: Show quote > > There are several approaches to what is typically referred to . > > as UPSERT or MERGE. > > A There is no UPSERT command, correct. What I was attempting to say, to spell
it out, is... here is an approach to achieving something similar to UPSERT. Show quote > UPSERT in sql server 2000? I > Aaron Bertrand [SQL Server MVP] wrote: >> > There are several approaches to what is typically referred to . >> >> as UPSERT or MERGE. >> >> A >  |
|||||||||||||||||||||||
Other interesting topics