|
|
You're in the
inetserver asp db
group:i wanna help in deling sql server 2000
inetserver asp db:
Hello every one , I 'm doing this sql statment,it is an insert statment. i want to insert values into two tables at the same time . for one of thes tables have a forigen key of the other, it can't be null. so ti wan't to know if there is any possibility to combine the two statment in one statment only . Thank u all , u have been a great help to me before and u'm sure u will be for ever . Yours, Maii
[quoted text, click to view]
> I 'm doing this sql statment,it is an insert statment. i want > to insert values into two tables at the same time . for one of thes > tables have a forigen key of the other, it can't be null. so ti wan't to > know if there is any possibility to combine the two statment in one > statment only . No, it is not possible. An INSERT statement affects a single table. Typically, you would do this using a stored procedure, in which case if you need to grab, say, a system-generated IDENTITY value, you don't have to do it in multiple passes to the database layer. Can you give more information? What piece do you not know at the time you make your two INSERT statements?
[quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eJ6is%23iaGHA.3736@TK2MSFTNGP04.phx.gbl... > > I 'm doing this sql statment,it is an insert statment. i want > > to insert values into two tables at the same time . for one of thes > > tables have a forigen key of the other, it can't be null. so ti wan't to > > know if there is any possibility to combine the two statment in one > > statment only . > > No, it is not possible. An INSERT statement affects a single table. > > Typically, you would do this using a stored procedure, in which case if you > need to grab, say, a system-generated IDENTITY value, you don't have to do > it in multiple passes to the database layer. Can you give more information? > What piece do you not know at the time you make your two INSERT statements? > An SP isn't necessary it can be done in a single batch. DECLARE @Table1_ID int INSERT Table1 (FieldName1, FieldName2,...) VALUES (?, ?) SET @Table_ID = @@IDENTITY INSERT Table2 (Table1_ID, FieldName2, FieldName3) VALUES(@Table1_ID, ? ,?) Anthony.
[quoted text, click to view]
Anthony Jones wrote: Maybe not necessary, but why send all that stuff over the wire when you can > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:eJ6is%23iaGHA.3736@TK2MSFTNGP04.phx.gbl... >>> I 'm doing this sql statment,it is an insert statment. i >>> want to insert values into two tables at the same time . for one of >>> thes tables have a forigen key of the other, it can't be null. so >>> ti wan't to know if there is any possibility to combine the two >>> statment in one statment only . >> >> No, it is not possible. An INSERT statement affects a single table. >> >> Typically, you would do this using a stored procedure, in which case >> if you need to grab, say, a system-generated IDENTITY value, you >> don't have to do it in multiple passes to the database layer. Can >> you give more information? What piece do you not know at the time >> you make your two INSERT statements? >> > > An SP isn't necessary it can be done in a single batch. > > DECLARE @Table1_ID int > INSERT Table1 (FieldName1, FieldName2,...) VALUES (?, ?) > SET @Table_ID = @@IDENTITY > INSERT Table2 (Table1_ID, FieldName2, FieldName3) VALUES(@Table1_ID, > ? ,?) > > encapsulate it in a stored procedure? And why use @@IDENTITY when SCOPE_IDENTITY is available (I know, the OP did not specify the version of SQL Server he was using ... )? -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
[quoted text, click to view]
Anthony Jones wrote: :-)>> Maybe not necessary, but why send all that stuff over the wire when >> you can encapsulate it in a stored procedure? >> > > This is the Performance vs Maintainabilty argument we've already had. > The nice thing about having the SQL in the ASP page is that you don't > need to go hunting for it when trying to fix bugs or make a change. > The ASP can't be out of sync with sql performing the query although > the sql can still be out of sync with the schema. > > My criteria for creating an SP are: is it too complex to read well in > a ASP page and/or is it a reusable chunk of code. It drives me nuts > when I come across a SQL DB with hundreds of small little SPs that > are only called by a single ASP page which are in turn only ever hit > by a client once in a blue moon. I'm the opposite. I find stored procedures easier to maintain. Plus, I can debug them without having to use a client environment, i.e. a web server and browser. Sionce I'm using query analyzer, I can optimize the procedure before attempting to run it from ASP. And the biggest bonus for me: if the procedure fails when I'm debugging it in query analyzer, I know I don't have to go hunting for a vbscript problem. And vice versa, if there is a vailure when running from the client app, but not in QA, I know where to start looking for the problem. Plus, there's the theoretical gain from decoupling the application layer from the data layer, but I'm not a huge advocate of that POV, so I won't argue this point too strongly. I guess it comes down to where the developer is most comfortable working. Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
[quoted text, click to view]
>> Maybe not necessary, but why send all that stuff over the wire when you > can >> encapsulate it in a stored procedure? >> > > This is the Performance vs Maintainabilty argument we've already had. The > nice thing about having the SQL in the ASP page is that you don't need to > go > hunting for it when trying to fix bugs or make a change. I have a very different argument. Let's say you have 20 ASP pages that run the same query (or a very similar version, with one different parameter). NOW, when you have to change the SQL, instead of changing one stored procedure, you have to go find all the places where ASP might be referencing the underlying objects. T-SQL code does not belong in the presentation tier, it belongs in the database tier...
[quoted text, click to view]
Anthony Jones wrote: > > I'm not a big fan of the layered approached at the server level. The > concept is hampered by the need to reduce the amount of state held > between requests. Also too much of what some believe to be in the > domain of the 'business layer' impacts very significantly on the > behaviour of the UI. I am somewhat in agreement with this. Of course, I have mainly worked on small projects that never really lent themselves well to the strict n-tiered structure. Also, I am the lone developer in my company so there is even less of a reason to take a tiered approach. Given a different type of project work, I would probably switch my thinking to more align with Aaron's views. Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
[quoted text, click to view] "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%23onbC%23qaGHA.3376@TK2MSFTNGP05.phx.gbl... > Anthony Jones wrote: > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > > message news:eJ6is%23iaGHA.3736@TK2MSFTNGP04.phx.gbl... > >>> I 'm doing this sql statment,it is an insert statment. i > >>> want to insert values into two tables at the same time . for one of > >>> thes tables have a forigen key of the other, it can't be null. so > >>> ti wan't to know if there is any possibility to combine the two > >>> statment in one statment only . > >> > >> No, it is not possible. An INSERT statement affects a single table. > >> > >> Typically, you would do this using a stored procedure, in which case > >> if you need to grab, say, a system-generated IDENTITY value, you > >> don't have to do it in multiple passes to the database layer. Can > >> you give more information? What piece do you not know at the time > >> you make your two INSERT statements? > >> > > > > An SP isn't necessary it can be done in a single batch. > > > > DECLARE @Table1_ID int > > INSERT Table1 (FieldName1, FieldName2,...) VALUES (?, ?) > > SET @Table_ID = @@IDENTITY > > INSERT Table2 (Table1_ID, FieldName2, FieldName3) VALUES(@Table1_ID, > > ? ,?) > > > > > Maybe not necessary, but why send all that stuff over the wire when you can > encapsulate it in a stored procedure? > This is the Performance vs Maintainabilty argument we've already had. The nice thing about having the SQL in the ASP page is that you don't need to go hunting for it when trying to fix bugs or make a change. The ASP can't be out of sync with sql performing the query although the sql can still be out of sync with the schema. My criteria for creating an SP are: is it too complex to read well in a ASP page and/or is it a reusable chunk of code. It drives me nuts when I come across a SQL DB with hundreds of small little SPs that are only called by a single ASP page which are in turn only ever hit by a client once in a blue moon. [quoted text, click to view] > And why use @@IDENTITY when SCOPE_IDENTITY is available (I know, the OP did > not specify the version of SQL Server he was using ... )? > Good point. I stopped using identity field SQL 7.0 partly because what it is now supplied by SCOPE_IDENTITY wasn't available and partly because I'm a control freak and like to allocate my own IDs. [quoted text, click to view] > -- > Microsoft MVP - ASP/ASP.NET > Please reply to the newsgroup. This email account is my spam trap so I > don't check it very often. If you must reply off-line, then remove the > "NO SPAM" > >
[quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eqfz3$uaGHA.996@TK2MSFTNGP04.phx.gbl... > >> Maybe not necessary, but why send all that stuff over the wire when you > > can > >> encapsulate it in a stored procedure? > >> > > > > This is the Performance vs Maintainabilty argument we've already had. The > > nice thing about having the SQL in the ASP page is that you don't need to > > go > > hunting for it when trying to fix bugs or make a change. > > I have a very different argument. Let's say you have 20 ASP pages that run > the same query (or a very similar version, with one different parameter). > NOW, when you have to change the SQL, instead of changing one stored > procedure, you have to go find all the places where ASP might be referencing > the underlying objects. > I agree. Such a situation would match one of the reasons I would create an SP, to quote myself:- "My criteria for creating an SP are: is it too complex to read well in a ASP page and/or is it a _reusable_ chunk of code." [quoted text, click to view] > T-SQL code does not belong in the presentation tier, it belongs in the > database tier... > Not all ASP is presentation and not all SQL is database. I have as many ASP pages that accept XML for updating a DB as I do for creating the UI. I also have plenty of SQL (in page and in SP) whose only function is to provide a specific set of fields for a specific UI requirement. I'm not a big fan of the layered approached at the server level. The concept is hampered by the need to reduce the amount of state held between requests. Also too much of what some believe to be in the domain of the 'business layer' impacts very significantly on the behaviour of the UI.
[quoted text, click to view] "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:e7tFRmuaGHA.608@TK2MSFTNGP02.phx.gbl... > Anthony Jones wrote: > >> Maybe not necessary, but why send all that stuff over the wire when > >> you can encapsulate it in a stored procedure? > >> > > > > This is the Performance vs Maintainabilty argument we've already had. > > The nice thing about having the SQL in the ASP page is that you don't > > need to go hunting for it when trying to fix bugs or make a change. > > The ASP can't be out of sync with sql performing the query although > > the sql can still be out of sync with the schema. > > > > My criteria for creating an SP are: is it too complex to read well in > > a ASP page and/or is it a reusable chunk of code. It drives me nuts > > when I come across a SQL DB with hundreds of small little SPs that > > are only called by a single ASP page which are in turn only ever hit > > by a client once in a blue moon. > :-) > I'm the opposite. I find stored procedures easier to maintain. Plus, I can > debug them without having to use a client environment, i.e. a web server and > browser. Sionce I'm using query analyzer, I can optimize the procedure > before attempting to run it from ASP. I think we probably develop quite different apps which have different emphasis. I would make reasonable choices in schema (as regards index choices etc) and then not worry about it at all until I come across a reason to worry about it. In my experience less the 2% of an apps total code is responsible for nearly all the performance concerns. [quoted text, click to view] >And the biggest bonus for me: if the > procedure fails when I'm debugging it in query analyzer, I know I don't have > to go hunting for a vbscript problem. I think if a script is complex enough that it may require some debugging effort then it would met one of reason why I'd make it an SP. However, the vast majority of SQL out there just isn't that complex. [quoted text, click to view] > And vice versa, if there is a vailure > when running from the client app, but not in QA, I know where to start > looking for the problem. > > Plus, there's the theoretical gain from decoupling the application layer > from the data layer, but I'm not a huge advocate of that POV, so I won't > argue this point too strongly. I agree. It's always good to keep an eye out for discrete chunks of logic that can be encapsulated away in a set of SPs but I wouldn't go out my way to try to ram a square peg in a round hole. [quoted text, click to view] > I guess it comes down to where the developer is most comfortable working. > Yes this is a factor that at times can be under estimated. Very often the best tool for the job is the one you know best how to use. :) Again balance is needed it's good to get familiar with how to more effectively use other tools. [quoted text, click to view] > Bob Barrows > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > >
Don't see what you're looking for? Try a search.
|
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
| home · blog · groups | Questions? Comments? Contact the dn |