Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

INSERT WITH relationships

Hi,

I've got two tables that i wish to insert data into simultaneously. The two tables have a relationship between the id on table1 and the field table1ID on table 2. the id field on table1 is an identity field and i wish to insert that into table2 [for several rows]

I can insert into table1 but not table2.

Does this make sense?

Kind Regards,
Rim:confused:Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW|||Originally posted by GWilliy
Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW

yeah that makes sense. i figured out the @.@.identity thing. It's good to get someone's opinion too rather that just the help files.

thank you very much.

Insert Trigger for Parent/Child

I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.

Thanks,
James

Where did you put the insert logic to the child table? I mean did you insert 1~N row(s) into child table in the insert trigger of parent table? Anyways I suppose you did like I say, as it helps to matainence the data consistency. Then you can add a column to parent table, which is used to record effected rows in child by the row. And in the insert trigger of parent table, let's declare a INT variable with initial value 0, every time an insert to child table will cause the variable increased by 1. After all required rows have been inserted into child, update the row in parent table with the INT variable. Something like this:

create trigger trg_ins_Parent on tbl_Parent for insert
as
begin
declare @.i int
set @.i=0
while(...)
begin
insert into tbl_Child select val1,val2,...
set @.i=@.i+1
end
update tbl_Parent setChildCnt=@.i
where rowid=inserted.rowid
end
go

INSERT TIMEOUT

I got a completely independent log table, which I mean
there is no relationship with other tables. its columns
look like
nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
tinyint, varchar(3),smalldatetime.
it works fine before the amount of its records reaches 10
millions around. afterwards, the insertion of one record
in ASP may return an ODBC Timeout Error. when this
happens, the only way to insert a record which I can
imagine is to run the same stored procedure in Query
Analyzer, Guess how long it took? 70 seconds!
the strangest thing is, after the 70 seconds insertion,
the next insertion only takes 1 second or less, at that
time, ASP page works again.
My SQL server is SQL2000+sp3
I noticed the same problem was reported in this newsgroup
two years ago, somebody suggested using aba_lockinfo to
figure it out, I will try it when the problem happens
again. In the meantime, I wonder if anybody got a better
solution, or knew what was the main reason of this problem.
Thanks,
Mingfa MaSounds like you may have page splitting going on and a really slow disk
subsystem. What is the actual DDL including indexes for the table and a
sample of the inserted row? You could also have blocking issues which can
be found with sp_who2.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> I got a completely independent log table, which I mean
> there is no relationship with other tables. its columns
> look like
> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
> tinyint, varchar(3),smalldatetime.
> it works fine before the amount of its records reaches 10
> millions around. afterwards, the insertion of one record
> in ASP may return an ODBC Timeout Error. when this
> happens, the only way to insert a record which I can
> imagine is to run the same stored procedure in Query
> Analyzer, Guess how long it took? 70 seconds!
> the strangest thing is, after the 70 seconds insertion,
> the next insertion only takes 1 second or less, at that
> time, ASP page works again.
> My SQL server is SQL2000+sp3
> I noticed the same problem was reported in this newsgroup
> two years ago, somebody suggested using aba_lockinfo to
> figure it out, I will try it when the problem happens
> again. In the meantime, I wonder if anybody got a better
> solution, or knew what was the main reason of this problem.
> Thanks,
> Mingfa Ma
>|||Thanks, Andrew,
but what do you mean "the actual DDL"? how to get it?
here is the sample of the inserted row,
626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-15
02:23:00
Mingfa Ma
>--Original Message--
>Sounds like you may have page splitting going on and a
really slow disk
>subsystem. What is the actual DDL including indexes for
the table and a
>sample of the inserted row? You could also have
blocking issues which can
>be found with sp_who2.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> I got a completely independent log table, which I mean
>> there is no relationship with other tables. its columns
>> look like
>> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
>> tinyint, varchar(3),smalldatetime.
>> it works fine before the amount of its records reaches
10
>> millions around. afterwards, the insertion of one record
>> in ASP may return an ODBC Timeout Error. when this
>> happens, the only way to insert a record which I can
>> imagine is to run the same stored procedure in Query
>> Analyzer, Guess how long it took? 70 seconds!
>> the strangest thing is, after the 70 seconds insertion,
>> the next insertion only takes 1 second or less, at that
>> time, ASP page works again.
>> My SQL server is SQL2000+sp3
>> I noticed the same problem was reported in this
newsgroup
>> two years ago, somebody suggested using aba_lockinfo to
>> figure it out, I will try it when the problem happens
>> again. In the meantime, I wonder if anybody got a better
>> solution, or knew what was the main reason of this
problem.
>> Thanks,
>> Mingfa Ma
>>
>
>.
>|||That is the definition of the table, indexes, etc. You can get this by
right clicking on the table in EM and choosing "All Tasks - Generate Sql
Script" and make sure you include the indexes, triggers etc.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> Thanks, Andrew,
> but what do you mean "the actual DDL"? how to get it?
> here is the sample of the inserted row,
> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-15
> 02:23:00
>
> Mingfa Ma
>
> >--Original Message--
> >Sounds like you may have page splitting going on and a
> really slow disk
> >subsystem. What is the actual DDL including indexes for
> the table and a
> >sample of the inserted row? You could also have
> blocking issues which can
> >be found with sp_who2.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> I got a completely independent log table, which I mean
> >> there is no relationship with other tables. its columns
> >> look like
> >>
> >> nvarchar(16), nvarchar(16), nvarchar(16), int, tinyint,
> >> tinyint, varchar(3),smalldatetime.
> >>
> >> it works fine before the amount of its records reaches
> 10
> >> millions around. afterwards, the insertion of one record
> >> in ASP may return an ODBC Timeout Error. when this
> >> happens, the only way to insert a record which I can
> >> imagine is to run the same stored procedure in Query
> >> Analyzer, Guess how long it took? 70 seconds!
> >>
> >> the strangest thing is, after the 70 seconds insertion,
> >> the next insertion only takes 1 second or less, at that
> >> time, ASP page works again.
> >>
> >> My SQL server is SQL2000+sp3
> >>
> >> I noticed the same problem was reported in this
> newsgroup
> >> two years ago, somebody suggested using aba_lockinfo to
> >> figure it out, I will try it when the problem happens
> >> again. In the meantime, I wonder if anybody got a better
> >> solution, or knew what was the main reason of this
> problem.
> >>
> >> Thanks,
> >>
> >> Mingfa Ma
> >>
> >>
> >
> >
> >.
> >|||Sorry, I mistook DDL as DLL.
the table is very simple, no index, no trigger.
Here is the definition of the table
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[n2_tblLogUserQuery]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[n2_tblLogUserQuery]
GO
CREATE TABLE [dbo].[n2_tblLogUserQuery] (
[UID] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SID] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Query] [nvarchar] (128) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonNameID] [int] NULL ,
[Type] [tinyint] NULL ,
[App] [tinyint] NULL ,
[CountryCode] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
>--Original Message--
>That is the definition of the table, indexes, etc. You
can get this by
>right clicking on the table in EM and choosing "All
Tasks - Generate Sql
>Script" and make sure you include the indexes, triggers
etc.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> Thanks, Andrew,
>> but what do you mean "the actual DDL"? how to get it?
>> here is the sample of the inserted row,
>> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-
15
>> 02:23:00
>>
>> Mingfa Ma
>>
>> >--Original Message--
>> >Sounds like you may have page splitting going on and a
>> really slow disk
>> >subsystem. What is the actual DDL including indexes
for
>> the table and a
>> >sample of the inserted row? You could also have
>> blocking issues which can
>> >be found with sp_who2.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> I got a completely independent log table, which I
mean
>> >> there is no relationship with other tables. its
columns
>> >> look like
>> >>
>> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
tinyint,
>> >> tinyint, varchar(3),smalldatetime.
>> >>
>> >> it works fine before the amount of its records
reaches
>> 10
>> >> millions around. afterwards, the insertion of one
record
>> >> in ASP may return an ODBC Timeout Error. when this
>> >> happens, the only way to insert a record which I can
>> >> imagine is to run the same stored procedure in Query
>> >> Analyzer, Guess how long it took? 70 seconds!
>> >>
>> >> the strangest thing is, after the 70 seconds
insertion,
>> >> the next insertion only takes 1 second or less, at
that
>> >> time, ASP page works again.
>> >>
>> >> My SQL server is SQL2000+sp3
>> >>
>> >> I noticed the same problem was reported in this
>> newsgroup
>> >> two years ago, somebody suggested using aba_lockinfo
to
>> >> figure it out, I will try it when the problem happens
>> >> again. In the meantime, I wonder if anybody got a
better
>> >> solution, or knew what was the main reason of this
>> problem.
>> >>
>> >> Thanks,
>> >>
>> >> Mingfa Ma
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You have no indexes at all? Is there anyone running selects on this table?
If so they will most likely take out a table lock and block the insert until
done. Just curious if the UID and SID are just numbers why ar eyou using a
NVARCHAR vs a regular VARCHAR or even an INT? You might consider placing a
clustered index on the table but you need to decide how you access it to
determine the proper column to use.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> Sorry, I mistook DDL as DLL.
> the table is very simple, no index, no trigger.
> Here is the definition of the table
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[n2_tblLogUserQuery]') and OBJECTPROPERTY
> (id, N'IsUserTable') = 1)
> drop table [dbo].[n2_tblLogUserQuery]
> GO
> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> [UID] [nvarchar] (16) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [SID] [nvarchar] (16) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Query] [nvarchar] (128) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [CommonNameID] [int] NULL ,
> [Type] [tinyint] NULL ,
> [App] [tinyint] NULL ,
> [CountryCode] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogDate] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
>
>
> >--Original Message--
> >That is the definition of the table, indexes, etc. You
> can get this by
> >right clicking on the table in EM and choosing "All
> Tasks - Generate Sql
> >Script" and make sure you include the indexes, triggers
> etc.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> Thanks, Andrew,
> >>
> >> but what do you mean "the actual DDL"? how to get it?
> >>
> >> here is the sample of the inserted row,
> >>
> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-07-
> 15
> >> 02:23:00
> >>
> >>
> >> Mingfa Ma
> >>
> >>
> >> >--Original Message--
> >> >Sounds like you may have page splitting going on and a
> >> really slow disk
> >> >subsystem. What is the actual DDL including indexes
> for
> >> the table and a
> >> >sample of the inserted row? You could also have
> >> blocking issues which can
> >> >be found with sp_who2.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> I got a completely independent log table, which I
> mean
> >> >> there is no relationship with other tables. its
> columns
> >> >> look like
> >> >>
> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> tinyint,
> >> >> tinyint, varchar(3),smalldatetime.
> >> >>
> >> >> it works fine before the amount of its records
> reaches
> >> 10
> >> >> millions around. afterwards, the insertion of one
> record
> >> >> in ASP may return an ODBC Timeout Error. when this
> >> >> happens, the only way to insert a record which I can
> >> >> imagine is to run the same stored procedure in Query
> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >>
> >> >> the strangest thing is, after the 70 seconds
> insertion,
> >> >> the next insertion only takes 1 second or less, at
> that
> >> >> time, ASP page works again.
> >> >>
> >> >> My SQL server is SQL2000+sp3
> >> >>
> >> >> I noticed the same problem was reported in this
> >> newsgroup
> >> >> two years ago, somebody suggested using aba_lockinfo
> to
> >> >> figure it out, I will try it when the problem happens
> >> >> again. In the meantime, I wonder if anybody got a
> better
> >> >> solution, or knew what was the main reason of this
> >> problem.
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Mingfa Ma
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Correct, there is no indexes on the table as there is
nobody running selects on this table.
UID and SID are not numbers, but they can be changed to
varchar if that would make any difference.
It is whorthwhile pointing out that this table is not
being accessed frequently. 99.99% of time it is only used
to log incoming data and there are about 15 to 30 incoming
data to be recorded within a single second. I am pretty
sure nobody is runnng 'select' when the problem happens.
If creating an index on this table, would it not further
slow down this table? Also, this table can grow like 1
million records in a day, the index of the table would
also be very huge?
Mingfa Ma
>--Original Message--
>You have no indexes at all? Is there anyone running
selects on this table?
>If so they will most likely take out a table lock and
block the insert until
>done. Just curious if the UID and SID are just numbers
why ar eyou using a
>NVARCHAR vs a regular VARCHAR or even an INT? You might
consider placing a
>clustered index on the table but you need to decide how
you access it to
>determine the proper column to use.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
>> Sorry, I mistook DDL as DLL.
>> the table is very simple, no index, no trigger.
>> Here is the definition of the table
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[n2_tblLogUserQuery]') and
OBJECTPROPERTY
>> (id, N'IsUserTable') = 1)
>> drop table [dbo].[n2_tblLogUserQuery]
>> GO
>> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
>> [UID] [nvarchar] (16) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [SID] [nvarchar] (16) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Query] [nvarchar] (128) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [CommonNameID] [int] NULL ,
>> [Type] [tinyint] NULL ,
>> [App] [tinyint] NULL ,
>> [CountryCode] [varchar] (3) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [LogDate] [smalldatetime] NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> >--Original Message--
>> >That is the definition of the table, indexes, etc. You
>> can get this by
>> >right clicking on the table in EM and choosing "All
>> Tasks - Generate Sql
>> >Script" and make sure you include the indexes, triggers
>> etc.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> >> Thanks, Andrew,
>> >>
>> >> but what do you mean "the actual DDL"? how to get it?
>> >>
>> >> here is the sample of the inserted row,
>> >>
>> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-
07-
>> 15
>> >> 02:23:00
>> >>
>> >>
>> >> Mingfa Ma
>> >>
>> >>
>> >> >--Original Message--
>> >> >Sounds like you may have page splitting going on
and a
>> >> really slow disk
>> >> >subsystem. What is the actual DDL including indexes
>> for
>> >> the table and a
>> >> >sample of the inserted row? You could also have
>> >> blocking issues which can
>> >> >be found with sp_who2.
>> >> >
>> >> >--
>> >> >
>> >> >Andrew J. Kelly
>> >> >SQL Server MVP
>> >> >
>> >> >
>> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> >> I got a completely independent log table, which I
>> mean
>> >> >> there is no relationship with other tables. its
>> columns
>> >> >> look like
>> >> >>
>> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
>> tinyint,
>> >> >> tinyint, varchar(3),smalldatetime.
>> >> >>
>> >> >> it works fine before the amount of its records
>> reaches
>> >> 10
>> >> >> millions around. afterwards, the insertion of one
>> record
>> >> >> in ASP may return an ODBC Timeout Error. when this
>> >> >> happens, the only way to insert a record which I
can
>> >> >> imagine is to run the same stored procedure in
Query
>> >> >> Analyzer, Guess how long it took? 70 seconds!
>> >> >>
>> >> >> the strangest thing is, after the 70 seconds
>> insertion,
>> >> >> the next insertion only takes 1 second or less, at
>> that
>> >> >> time, ASP page works again.
>> >> >>
>> >> >> My SQL server is SQL2000+sp3
>> >> >>
>> >> >> I noticed the same problem was reported in this
>> >> newsgroup
>> >> >> two years ago, somebody suggested using
aba_lockinfo
>> to
>> >> >> figure it out, I will try it when the problem
happens
>> >> >> again. In the meantime, I wonder if anybody got a
>> better
>> >> >> solution, or knew what was the main reason of this
>> >> problem.
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Mingfa Ma
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||The main reason I mentioned changing the datatype was to reduce space and
keep the index smaller if you had one on those columns. If this does not
get queried then I suppose you don't need an index for that purpose but
sometimes a clustered index can help with insertions. In a heap sql server
needs to lookup a fair amount of information to determine where to place the
next row and with a large number of rows this may take a while, although
usually not that long<g>. With a clustered index there is no choice but to
place it where it belongs according to the index expression. If you use a
column such a an Identity or a datetime that is always in chronological
order for your index expression the new rows are always just added to the
end. It's pretty clean and efficient. First I would run profiler and
perfmon to see what is happening in the system at the time of the slowdown.
Make sure there are no high disk or cpu queues etc. Run sp_who2 to ensure
that nothing is blocking the inserts. And I would try the clustered index
on the LogDate column and see how that works out.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:091f01c34c62$86066d60$a501280a@.phx.gbl...
> Correct, there is no indexes on the table as there is
> nobody running selects on this table.
> UID and SID are not numbers, but they can be changed to
> varchar if that would make any difference.
> It is whorthwhile pointing out that this table is not
> being accessed frequently. 99.99% of time it is only used
> to log incoming data and there are about 15 to 30 incoming
> data to be recorded within a single second. I am pretty
> sure nobody is runnng 'select' when the problem happens.
> If creating an index on this table, would it not further
> slow down this table? Also, this table can grow like 1
> million records in a day, the index of the table would
> also be very huge?
> Mingfa Ma
>
> >--Original Message--
> >You have no indexes at all? Is there anyone running
> selects on this table?
> >If so they will most likely take out a table lock and
> block the insert until
> >done. Just curious if the UID and SID are just numbers
> why ar eyou using a
> >NVARCHAR vs a regular VARCHAR or even an INT? You might
> consider placing a
> >clustered index on the table but you need to decide how
> you access it to
> >determine the proper column to use.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> >> Sorry, I mistook DDL as DLL.
> >>
> >> the table is very simple, no index, no trigger.
> >>
> >> Here is the definition of the table
> >>
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
> OBJECTPROPERTY
> >> (id, N'IsUserTable') = 1)
> >> drop table [dbo].[n2_tblLogUserQuery]
> >> GO
> >>
> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> >> [UID] [nvarchar] (16) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [SID] [nvarchar] (16) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [Query] [nvarchar] (128) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [CommonNameID] [int] NULL ,
> >> [Type] [tinyint] NULL ,
> >> [App] [tinyint] NULL ,
> >> [CountryCode] [varchar] (3) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [LogDate] [smalldatetime] NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >That is the definition of the table, indexes, etc. You
> >> can get this by
> >> >right clicking on the table in EM and choosing "All
> >> Tasks - Generate Sql
> >> >Script" and make sure you include the indexes, triggers
> >> etc.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> >> Thanks, Andrew,
> >> >>
> >> >> but what do you mean "the actual DDL"? how to get it?
> >> >>
> >> >> here is the sample of the inserted row,
> >> >>
> >> >> 626995265,1052399332,Dering Harbor,0,20,100,US,2003-
> 07-
> >> 15
> >> >> 02:23:00
> >> >>
> >> >>
> >> >> Mingfa Ma
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >Sounds like you may have page splitting going on
> and a
> >> >> really slow disk
> >> >> >subsystem. What is the actual DDL including indexes
> >> for
> >> >> the table and a
> >> >> >sample of the inserted row? You could also have
> >> >> blocking issues which can
> >> >> >be found with sp_who2.
> >> >> >
> >> >> >--
> >> >> >
> >> >> >Andrew J. Kelly
> >> >> >SQL Server MVP
> >> >> >
> >> >> >
> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> >> I got a completely independent log table, which I
> >> mean
> >> >> >> there is no relationship with other tables. its
> >> columns
> >> >> >> look like
> >> >> >>
> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> >> tinyint,
> >> >> >> tinyint, varchar(3),smalldatetime.
> >> >> >>
> >> >> >> it works fine before the amount of its records
> >> reaches
> >> >> 10
> >> >> >> millions around. afterwards, the insertion of one
> >> record
> >> >> >> in ASP may return an ODBC Timeout Error. when this
> >> >> >> happens, the only way to insert a record which I
> can
> >> >> >> imagine is to run the same stored procedure in
> Query
> >> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >> >>
> >> >> >> the strangest thing is, after the 70 seconds
> >> insertion,
> >> >> >> the next insertion only takes 1 second or less, at
> >> that
> >> >> >> time, ASP page works again.
> >> >> >>
> >> >> >> My SQL server is SQL2000+sp3
> >> >> >>
> >> >> >> I noticed the same problem was reported in this
> >> >> newsgroup
> >> >> >> two years ago, somebody suggested using
> aba_lockinfo
> >> to
> >> >> >> figure it out, I will try it when the problem
> happens
> >> >> >> again. In the meantime, I wonder if anybody got a
> >> better
> >> >> >> solution, or knew what was the main reason of this
> >> >> problem.
> >> >> >>
> >> >> >> Thanks,
> >> >> >>
> >> >> >> Mingfa Ma
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks very much indeed.
I will try your suggestions when the problem happens again.
and I'll inform you the result. Is your email
sqlmvpnooospam@.shadhawk.com reachable? just in case this
topic would be too old in the group at that time, and you
would not notice it.
Kind regards,
Mingfa Ma
>--Original Message--
>The main reason I mentioned changing the datatype was to
reduce space and
>keep the index smaller if you had one on those columns.
If this does not
>get queried then I suppose you don't need an index for
that purpose but
>sometimes a clustered index can help with insertions. In
a heap sql server
>needs to lookup a fair amount of information to determine
where to place the
>next row and with a large number of rows this may take a
while, although
>usually not that long<g>. With a clustered index there
is no choice but to
>place it where it belongs according to the index
expression. If you use a
>column such a an Identity or a datetime that is always in
chronological
>order for your index expression the new rows are always
just added to the
>end. It's pretty clean and efficient. First I would
run profiler and
>perfmon to see what is happening in the system at the
time of the slowdown.
>Make sure there are no high disk or cpu queues etc. Run
sp_who2 to ensure
>that nothing is blocking the inserts. And I would try
the clustered index
>on the LogDate column and see how that works out.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>news:091f01c34c62$86066d60$a501280a@.phx.gbl...
>> Correct, there is no indexes on the table as there is
>> nobody running selects on this table.
>> UID and SID are not numbers, but they can be changed to
>> varchar if that would make any difference.
>> It is whorthwhile pointing out that this table is not
>> being accessed frequently. 99.99% of time it is only
used
>> to log incoming data and there are about 15 to 30
incoming
>> data to be recorded within a single second. I am pretty
>> sure nobody is runnng 'select' when the problem happens.
>> If creating an index on this table, would it not further
>> slow down this table? Also, this table can grow like 1
>> million records in a day, the index of the table would
>> also be very huge?
>> Mingfa Ma
>>
>> >--Original Message--
>> >You have no indexes at all? Is there anyone running
>> selects on this table?
>> >If so they will most likely take out a table lock and
>> block the insert until
>> >done. Just curious if the UID and SID are just numbers
>> why ar eyou using a
>> >NVARCHAR vs a regular VARCHAR or even an INT? You
might
>> consider placing a
>> >clustered index on the table but you need to decide how
>> you access it to
>> >determine the proper column to use.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
>> >> Sorry, I mistook DDL as DLL.
>> >>
>> >> the table is very simple, no index, no trigger.
>> >>
>> >> Here is the definition of the table
>> >>
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
>> OBJECTPROPERTY
>> >> (id, N'IsUserTable') = 1)
>> >> drop table [dbo].[n2_tblLogUserQuery]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
>> >> [UID] [nvarchar] (16) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [SID] [nvarchar] (16) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [Query] [nvarchar] (128) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [CommonNameID] [int] NULL ,
>> >> [Type] [tinyint] NULL ,
>> >> [App] [tinyint] NULL ,
>> >> [CountryCode] [varchar] (3) COLLATE
>> >> SQL_Latin1_General_CP1_CI_AS NULL ,
>> >> [LogDate] [smalldatetime] NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >That is the definition of the table, indexes, etc.
You
>> >> can get this by
>> >> >right clicking on the table in EM and choosing "All
>> >> Tasks - Generate Sql
>> >> >Script" and make sure you include the indexes,
triggers
>> >> etc.
>> >> >
>> >> >--
>> >> >
>> >> >Andrew J. Kelly
>> >> >SQL Server MVP
>> >> >
>> >> >
>> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
>> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
>> >> >> Thanks, Andrew,
>> >> >>
>> >> >> but what do you mean "the actual DDL"? how to get
it?
>> >> >>
>> >> >> here is the sample of the inserted row,
>> >> >>
>> >> >> 626995265,1052399332,Dering
Harbor,0,20,100,US,2003-
>> 07-
>> >> 15
>> >> >> 02:23:00
>> >> >>
>> >> >>
>> >> >> Mingfa Ma
>> >> >>
>> >> >>
>> >> >> >--Original Message--
>> >> >> >Sounds like you may have page splitting going on
>> and a
>> >> >> really slow disk
>> >> >> >subsystem. What is the actual DDL including
indexes
>> >> for
>> >> >> the table and a
>> >> >> >sample of the inserted row? You could also have
>> >> >> blocking issues which can
>> >> >> >be found with sp_who2.
>> >> >> >
>> >> >> >--
>> >> >> >
>> >> >> >Andrew J. Kelly
>> >> >> >SQL Server MVP
>> >> >> >
>> >> >> >
>> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in
message
>> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
>> >> >> >> I got a completely independent log table,
which I
>> >> mean
>> >> >> >> there is no relationship with other tables. its
>> >> columns
>> >> >> >> look like
>> >> >> >>
>> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
>> >> tinyint,
>> >> >> >> tinyint, varchar(3),smalldatetime.
>> >> >> >>
>> >> >> >> it works fine before the amount of its records
>> >> reaches
>> >> >> 10
>> >> >> >> millions around. afterwards, the insertion of
one
>> >> record
>> >> >> >> in ASP may return an ODBC Timeout Error. when
this
>> >> >> >> happens, the only way to insert a record which
I
>> can
>> >> >> >> imagine is to run the same stored procedure in
>> Query
>> >> >> >> Analyzer, Guess how long it took? 70 seconds!
>> >> >> >>
>> >> >> >> the strangest thing is, after the 70 seconds
>> >> insertion,
>> >> >> >> the next insertion only takes 1 second or
less, at
>> >> that
>> >> >> >> time, ASP page works again.
>> >> >> >>
>> >> >> >> My SQL server is SQL2000+sp3
>> >> >> >>
>> >> >> >> I noticed the same problem was reported in this
>> >> >> newsgroup
>> >> >> >> two years ago, somebody suggested using
>> aba_lockinfo
>> >> to
>> >> >> >> figure it out, I will try it when the problem
>> happens
>> >> >> >> again. In the meantime, I wonder if anybody
got a
>> >> better
>> >> >> >> solution, or knew what was the main reason of
this
>> >> >> problem.
>> >> >> >>
>> >> >> >> Thanks,
>> >> >> >>
>> >> >> >> Mingfa Ma
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||You have to remove the nooospam first. GoodLuck.
--
Andrew J. Kelly
SQL Server MVP
"Mingfa Ma" <mingfa@.commonname.com> wrote in message
news:0c3001c34c75$48451f40$a401280a@.phx.gbl...
> Thanks very much indeed.
> I will try your suggestions when the problem happens again.
> and I'll inform you the result. Is your email
> sqlmvpnooospam@.shadhawk.com reachable? just in case this
> topic would be too old in the group at that time, and you
> would not notice it.
> Kind regards,
> Mingfa Ma
>
> >--Original Message--
> >The main reason I mentioned changing the datatype was to
> reduce space and
> >keep the index smaller if you had one on those columns.
> If this does not
> >get queried then I suppose you don't need an index for
> that purpose but
> >sometimes a clustered index can help with insertions. In
> a heap sql server
> >needs to lookup a fair amount of information to determine
> where to place the
> >next row and with a large number of rows this may take a
> while, although
> >usually not that long<g>. With a clustered index there
> is no choice but to
> >place it where it belongs according to the index
> expression. If you use a
> >column such a an Identity or a datetime that is always in
> chronological
> >order for your index expression the new rows are always
> just added to the
> >end. It's pretty clean and efficient. First I would
> run profiler and
> >perfmon to see what is happening in the system at the
> time of the slowdown.
> >Make sure there are no high disk or cpu queues etc. Run
> sp_who2 to ensure
> >that nothing is blocking the inserts. And I would try
> the clustered index
> >on the LogDate column and see how that works out.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >news:091f01c34c62$86066d60$a501280a@.phx.gbl...
> >> Correct, there is no indexes on the table as there is
> >> nobody running selects on this table.
> >>
> >> UID and SID are not numbers, but they can be changed to
> >> varchar if that would make any difference.
> >>
> >> It is whorthwhile pointing out that this table is not
> >> being accessed frequently. 99.99% of time it is only
> used
> >> to log incoming data and there are about 15 to 30
> incoming
> >> data to be recorded within a single second. I am pretty
> >> sure nobody is runnng 'select' when the problem happens.
> >>
> >> If creating an index on this table, would it not further
> >> slow down this table? Also, this table can grow like 1
> >> million records in a day, the index of the table would
> >> also be very huge?
> >>
> >> Mingfa Ma
> >>
> >>
> >> >--Original Message--
> >> >You have no indexes at all? Is there anyone running
> >> selects on this table?
> >> >If so they will most likely take out a table lock and
> >> block the insert until
> >> >done. Just curious if the UID and SID are just numbers
> >> why ar eyou using a
> >> >NVARCHAR vs a regular VARCHAR or even an INT? You
> might
> >> consider placing a
> >> >clustered index on the table but you need to decide how
> >> you access it to
> >> >determine the proper column to use.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >news:0c0401c34bb0$61ec76c0$a501280a@.phx.gbl...
> >> >> Sorry, I mistook DDL as DLL.
> >> >>
> >> >> the table is very simple, no index, no trigger.
> >> >>
> >> >> Here is the definition of the table
> >> >>
> >> >> if exists (select * from dbo.sysobjects where id => >> >> object_id(N'[dbo].[n2_tblLogUserQuery]') and
> >> OBJECTPROPERTY
> >> >> (id, N'IsUserTable') = 1)
> >> >> drop table [dbo].[n2_tblLogUserQuery]
> >> >> GO
> >> >>
> >> >> CREATE TABLE [dbo].[n2_tblLogUserQuery] (
> >> >> [UID] [nvarchar] (16) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [SID] [nvarchar] (16) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [Query] [nvarchar] (128) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [CommonNameID] [int] NULL ,
> >> >> [Type] [tinyint] NULL ,
> >> >> [App] [tinyint] NULL ,
> >> >> [CountryCode] [varchar] (3) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [LogDate] [smalldatetime] NULL
> >> >> ) ON [PRIMARY]
> >> >> GO
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >That is the definition of the table, indexes, etc.
> You
> >> >> can get this by
> >> >> >right clicking on the table in EM and choosing "All
> >> >> Tasks - Generate Sql
> >> >> >Script" and make sure you include the indexes,
> triggers
> >> >> etc.
> >> >> >
> >> >> >--
> >> >> >
> >> >> >Andrew J. Kelly
> >> >> >SQL Server MVP
> >> >> >
> >> >> >
> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in message
> >> >> >news:04ff01c34b9e$9ea475c0$a001280a@.phx.gbl...
> >> >> >> Thanks, Andrew,
> >> >> >>
> >> >> >> but what do you mean "the actual DDL"? how to get
> it?
> >> >> >>
> >> >> >> here is the sample of the inserted row,
> >> >> >>
> >> >> >> 626995265,1052399332,Dering
> Harbor,0,20,100,US,2003-
> >> 07-
> >> >> 15
> >> >> >> 02:23:00
> >> >> >>
> >> >> >>
> >> >> >> Mingfa Ma
> >> >> >>
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >Sounds like you may have page splitting going on
> >> and a
> >> >> >> really slow disk
> >> >> >> >subsystem. What is the actual DDL including
> indexes
> >> >> for
> >> >> >> the table and a
> >> >> >> >sample of the inserted row? You could also have
> >> >> >> blocking issues which can
> >> >> >> >be found with sp_who2.
> >> >> >> >
> >> >> >> >--
> >> >> >> >
> >> >> >> >Andrew J. Kelly
> >> >> >> >SQL Server MVP
> >> >> >> >
> >> >> >> >
> >> >> >> >"Mingfa Ma" <mingfa@.commonname.com> wrote in
> message
> >> >> >> >news:03e701c34b8d$d8c13b00$a001280a@.phx.gbl...
> >> >> >> >> I got a completely independent log table,
> which I
> >> >> mean
> >> >> >> >> there is no relationship with other tables. its
> >> >> columns
> >> >> >> >> look like
> >> >> >> >>
> >> >> >> >> nvarchar(16), nvarchar(16), nvarchar(16), int,
> >> >> tinyint,
> >> >> >> >> tinyint, varchar(3),smalldatetime.
> >> >> >> >>
> >> >> >> >> it works fine before the amount of its records
> >> >> reaches
> >> >> >> 10
> >> >> >> >> millions around. afterwards, the insertion of
> one
> >> >> record
> >> >> >> >> in ASP may return an ODBC Timeout Error. when
> this
> >> >> >> >> happens, the only way to insert a record which
> I
> >> can
> >> >> >> >> imagine is to run the same stored procedure in
> >> Query
> >> >> >> >> Analyzer, Guess how long it took? 70 seconds!
> >> >> >> >>
> >> >> >> >> the strangest thing is, after the 70 seconds
> >> >> insertion,
> >> >> >> >> the next insertion only takes 1 second or
> less, at
> >> >> that
> >> >> >> >> time, ASP page works again.
> >> >> >> >>
> >> >> >> >> My SQL server is SQL2000+sp3
> >> >> >> >>
> >> >> >> >> I noticed the same problem was reported in this
> >> >> >> newsgroup
> >> >> >> >> two years ago, somebody suggested using
> >> aba_lockinfo
> >> >> to
> >> >> >> >> figure it out, I will try it when the problem
> >> happens
> >> >> >> >> again. In the meantime, I wonder if anybody
> got a
> >> >> better
> >> >> >> >> solution, or knew what was the main reason of
> this
> >> >> >> problem.
> >> >> >> >>
> >> >> >> >> Thanks,
> >> >> >> >>
> >> >> >> >> Mingfa Ma
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Insert SQL statament ?

I have a two table and they have relationship :

tblOrder :
+ OrderId : Type is AutoNumber(Identity) (PK)
+ CustomerID : Number(int)
+ OrderPrice : Number(int)
+ OrderDate : DateTime

and tblOrderDetail :
+ OrderID : Type is Number(FK)
+ ItemID : Number(int)
+ PriceEachItem : Number(int)

My problem : I want to insert data into two above table .
To tblOrder I use : insert into tblOrder(CustomerId,OrderPrice,OrderDate) values(.....) <--(This statement is allright )

So how i insert into tblOrderDetail when i can't not identify OrderID.

Please help me ! Thank u very much !

Insert into the order table, then use the SCOPE_IDENTITY() function to get back the key for that row...
DECLARE @.OrderID INT
INSERT tblOrder VALUES (...)
SET @.OrderID = SCOPE_IDENTITY()
INSERT tblOrderDetail VALUES (@.OrderId, ...)
|||

Thank adammachnic very much ! i am a beginner ! because i can only know "simple sql statement " i don't know clearly your sql statement ! How can i use them in Asp.net . Example in the my situation :

Sub Order_Click(a as Object ,e as EventArg) handles button1.Click
dim myconn as new sqlconnection("...")
dim sql as string ="Insert into tblOrder(...) values(...)"
dim mycmd as new sqlcommand(sql,myconn)
mycmd.executenonquery !
End Sub

I have never forgotten your advice ! Thank !

|||Actually, it would be better to put these statements into storedprocedures instead of calling them directly from your app. Youshould probably invest in a good book on SQL Server and ADO.NET. There are a few books available that cover both topics, that shouldhelp you out quite a bit!

|||

Thank adam ! I am going to study like you guide !