i wanna help in deling sql server 2000

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

*** Sent via Developersdex http://www.developersdex.com ***

Re: i wanna help in deling sql server 2000

> 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?

Re: i wanna help in deling sql server 2000

"Aaron Bertrand [SQL Server MVP]" 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.

Re: i wanna help in deling sql server 2000

Anthony Jones wrote:
> "Aaron Bertrand [SQL Server MVP]" 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?

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"

Re: i wanna help in deling sql server 2000

"Bob Barrows [MVP]" wrote in message
news:%23onbC%23qaGHA.3376@TK2MSFTNGP05.phx.gbl...
> Anthony Jones wrote:
> > "Aaron Bertrand [SQL Server MVP]" 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.

> 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.

> --
> 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"
>
>

Re: i wanna help in deling sql server 2000

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.

Re: i wanna help in deling sql server 2000

>> 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...

Re: i wanna help in deling sql server 2000

"Aaron Bertrand [SQL Server MVP]" 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."

> 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.

Re: i wanna help in deling sql server 2000

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.

Re: i wanna help in deling sql server 2000

"Bob Barrows [MVP]" 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.

>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.

> 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.

> 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.

> 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.
>
>