Insert/Update

Hi,
Is it possible to Read data From a Table while Insertion/Update is happening on the table??
In case if this is not possible,Is there anyway to do that??

Thanks,
KarthikIf you need accurate, dependable data, then there isn't a way to get that without waiting for the INSERT/UPDATE/DELETE transactions to finish.

If all you need to do is browse the data, and it doesn't matter if that data is not 100% complete or accurate, then you can use either the NOLOCK index hint, or the SET TRANSACTION_ISOLATION_LEVEL command.

-PatP|||Hi ,
Thanks for ur suggestion.

More inputs on this is welcome.

Thanks,
Karthik

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
1;Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blog...e
s.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.&
#91;Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blog...
es.html
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewritin
g
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CAS
E
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.comsql

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
End
Could you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.com

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via http://www.sqlmonster.com

INSERT UPDATE Trigger Question

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test

John

<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
only once?
Thanks,
Only once, and ALL of the rows are in the inserted table.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>
|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
> only once?
> Thanks,
>
|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:

> In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
> only once?
> Thanks,
>

Insert trigger for bulk insert

In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
only once?
Thanks,Only once, and ALL of the rows are in the inserted table.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
> only once?
> Thanks,
>|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:
> In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
> only once?
> Thanks,
>

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or only once?

Thanks,

Once for each batch. Here's the specific text from Books Online

"Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch."

Note that, by default, the triggers do not fire for bulk operations, you must specify the FIRE_TRIGGERS option in order to get triggers to fire for bulk operations.

|||

This option may have issues with performance if you enable during the bulk load operaton during the otherside of triggers, refer to the books online about Controlling trigger execution when bulk importing data.

HTH

Insert trigger for bulk insert

In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or only once?

Thanks,

(FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.

For more information, see Controlling Trigger Execution When Bulk Importing Data. )

What you are looking for is in the BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

sql

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod
or
only once?
Thanks,Only once, and ALL of the rows are in the inserted table.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the “FOR INSERT” trigger fires for each reco
d or
> only once?
> Thanks,
>|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:

> In case of a bulk insert, the “FOR INSERT” trigger fires for each reco
d or
> only once?
> Thanks,
>

insert record from another database

hi. we have 1 production database and a 2nd backup database. one of our users accidentally deleted a case and we would like to get it back. our production database is updated nightly so none of the case's information would've changed.

how can i insert the data from the backup database to the production database? i need to copy one row. thanks for your help!just one row?

how about a simple insert statement?

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.sqlmonster.com
Mike via SQLMonster.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com
|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.sqlmonster.com

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.droptable.comMike via droptable.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.droptable.com

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
--
Message posted via http://www.sqlmonster.comMike via SQLMonster.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
--
David Gugick
Imceda Software
www.imceda.com|||It seems like DBCC DBREINDEX solved the problem.
--
Message posted via http://www.sqlmonster.com