| Groups | Blog | Home |
sql server programming : store procedure or ...
If so, how do I create the procedure, which could update tableA.col1 and tableA.col2 from tableA based on the join key colkey in tableB with tableB.col1 and tableB.col2.
Hi Zach
So how do you exactly calling a S.P. from VB Here are currently how I running a couple of steps to get it done in VB, which I don't think it is an effective way - 1) select colkey from tableA where col1 is null and col2 is nul then get the result and put in loop, for each colkey put in a variable 2) select col1, col2 from tableB where colkey=variablecolkey then get the result an put into variables 3) update tableA set col1=variablecol1, col2=variablecol2 where colkey=variablecolkey 4) continue the loop Can you show me the sample how would you write the S.P. for the same result Thank you la ----- Zach Wells wrote: ---- [quoted text, click to view] shawn wrote > Can I create a store procedure that I could fire up from a Visual Basic program? > If so, how do I create the procedure, which could update tableA.col1 and tableA.col2 from tableA based on the join key colkey in tableB with tableB.col1 and tableB.col2. > Yes, you can You create the procedure using the "create procedure" command (see books online for exact syntax As far as how to update a table, quite frankly, given the minimal information you posted, I would do you a disservice by trying to post an update statement. Using update incorrectly can over-write every row of data you have in an instant and given your obvious newness, you wouldn't know any better until it was too late If you'd like to post the DDL (DDL is the data definition language, defines your tables) and some sample data, we might be able to help you a bit more Zac
I found the way calling a S.P. from VB, but not sure how do I write that S.P
Any input will be welcome ----- lanx wrote: ---- Hi Zach So how do you exactly calling a S.P. from VB Here are currently how I running a couple of steps to get it done in VB, which I don't think it is an effective way - 1) select colkey from tableA where col1 is null and col2 is nul then get the result and put in loop, for each colkey put in a variable 2) select col1, col2 from tableB where colkey=variablecolkey then get the result an put into variables 3) update tableA set col1=variablecol1, col2=variablecol2 where colkey=variablecolkey 4) continue the loop Can you show me the sample how would you write the S.P. for the same result Thank you la ----- Zach Wells wrote: ---- [quoted text, click to view] shawn wrote > Can I create a store procedure that I could fire up from a Visual Basic program? > If so, how do I create the procedure, which could update tableA.col1 and tableA.col2 from tableA based on the join key colkey in tableB with tableB.col1 and tableB.col2. > Yes, you can You create the procedure using the "create procedure" command (see books online for exact syntax As far as how to update a table, quite frankly, given the minimal information you posted, I would do you a disservice by trying to post an update statement. Using update incorrectly can over-write every row of data you have in an instant and given your obvious newness, you wouldn't know any better until it was too late If you'd like to post the DDL (DDL is the data definition language, defines your tables) and some sample data, we might be able to help you a bit more Zac
[quoted text, click to view] shawn wrote: > Can I create a store procedure that I could fire up from a Visual Basic program? > If so, how do I create the procedure, which could update tableA.col1 and tableA.col2 from tableA based on the join key colkey in tableB with tableB.col1 and tableB.col2. > Yes, you can. You create the procedure using the "create procedure" command (see books online for exact syntax) As far as how to update a table, quite frankly, given the minimal information you posted, I would do you a disservice by trying to post an update statement. Using update incorrectly can over-write every row of data you have in an instant and given your obvious newness, you wouldn't know any better until it was too late. If you'd like to post the DDL (DDL is the data definition language, defines your tables) and some sample data, we might be able to help you a bit more.
[quoted text, click to view]
> 1) select colkey from tableA where col1 is null and col2 is null > then get the result and put in loop, for each colkey put in a variable. > 2) select col1, col2 from tableB where colkey=variablecolkey. > then get the result an put into variables. > 3) update tableA set col1=variablecol1, col2=variablecol2 where colkey=variablecolkey. > 4) continue the loop. I'm glad you understand this is not a good method. It is usually best to avoid procedural processes like looping and cursors with a relational database. Whenever possible, let SQL Server perform the task rather than trying to do all the procedural code yourself. I believe you'll fund that the set-based approach below will perform *much* faster. You can execute the following statement directly from your application or encapsulate it in a stored procedure. UPDATE a SET col1 = b.col1, col2 = b.col2 FROM tableA a JOIN tableb b ON a.colkey = b.colkey WHERE a.col1 IS NULL AND a.col2 IS NULL -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "lanx" <anonymous@discussions.microsoft.com> wrote in message tableA.col1 and tableA.col2 from tableA based on the join key colkey innews:BBD660E6-2769-4748-B868-BAB2484DD201@microsoft.com... > Hi Zach, > So how do you exactly calling a S.P. from VB? > > Here are currently how I running a couple of steps to get it done in VB, which I don't think it is an effective way -- > 1) select colkey from tableA where col1 is null and col2 is null > then get the result and put in loop, for each colkey put in a variable. > 2) select col1, col2 from tableB where colkey=variablecolkey. > then get the result an put into variables. > 3) update tableA set col1=variablecol1, col2=variablecol2 where colkey=variablecolkey. > 4) continue the loop. > > Can you show me the sample how would you write the S.P. for the same result? > > > Thank you, > lan > > ----- Zach Wells wrote: ----- > > > > shawn wrote: > > Can I create a store procedure that I could fire up from a Visual Basic program? > > If so, how do I create the procedure, which could update tableB with tableB.col1 and tableB.col2. [quoted text, click to view] > > > > Yes, you can. > > You create the procedure using the "create procedure" command (see books > online for exact syntax) > > As far as how to update a table, quite frankly, given the minimal > information you posted, I would do you a disservice by trying to post an > update statement. Using update incorrectly can over-write every row of > data you have in an instant and given your obvious newness, you wouldn't > know any better until it was too late. > > If you'd like to post the DDL (DDL is the data definition language, > defines your tables) and some sample data, we might be able to help you > a bit more. > > Zach >
Here's some DDL, sample data and an example query using #temp tables. Run
the query in Query Analyzer and compare the before and after state of each table. See the topic CREATE PROCEDURE in Books Online for information on moving the UPDATE query to a stored procedure (basically wrap it with CREATE PROCEDURE and eliminate the # signs). set nocount on create table #tableA ( colkey INT primary key, col1 int null, col2 int null ) insert #tableA(colkey) select 1 insert #tableA(colkey) select 2 insert #tableA(colkey) select 3 insert #tableA select 4,0,0 -- a row not null insert #tableA(colkey) select 6 -- a row not in tableB create table #tableB ( colkey INT primary key, col1 int not null, col2 int not null ) insert #tableB select 1,2,3 insert #tableB select 2,3,4 insert #tableB select 3,4,5 insert #tableB select 4,1,1 insert #tableB select 5,10,10 -- a row not in tableA select * from #tableA select * from #tableB UPDATE #tableA SET col1 = tb.col1, col2 = tb.col2 FROM #tableA ta INNER JOIN #tableB tb ON ta.colkey = tb.colkey WHERE COALESCE(ta.col1, ta.col2) IS NULL select * from #tableA select * from #tableB drop table #tableA drop table #tableB -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ [quoted text, click to view] "lanx" <anonymous@discussions.microsoft.com> wrote in message news:BBD660E6-2769-4748-B868-BAB2484DD201@microsoft.com... > Hi Zach, > So how do you exactly calling a S.P. from VB? > > Here are currently how I running a couple of steps to get it done in > VB, which I don't think it is an effective way -- > 1) select colkey from tableA where col1 is null and col2 is null > then get the result and put in loop, for each colkey put in a variable. > 2) select col1, col2 from tableB where colkey=variablecolkey. > then get the result an put into variables. > 3) update tableA set col1=variablecol1, col2=variablecol2 where > colkey=variablecolkey. > 4) continue the loop. > > Can you show me the sample how would you write the S.P. for the same > result? > > > Thank you, > lan > > ----- Zach Wells wrote: ----- > > > > shawn wrote: > > Can I create a store procedure that I could fire up from a Visual > Basic program? > > If so, how do I create the procedure, which could update tableA.col1 > and tableA.col2 from tableA based on the join key colkey in tableB with > tableB.col1 and tableB.col2. > > > > Yes, you can. > > You create the procedure using the "create procedure" command (see > books > online for exact syntax) > > As far as how to update a table, quite frankly, given the minimal > information you posted, I would do you a disservice by trying to post > an > update statement. Using update incorrectly can over-write every row of > data you have in an instant and given your obvious newness, you > wouldn't > know any better until it was too late. > > If you'd like to post the DDL (DDL is the data definition language, > defines your tables) and some sample data, we might be able to help > you > a bit more. > > Zach >
Hi Aaron and Dan
Thank you so much for the great help. I am getting there. Have a great SQL time
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |