INSRET/UPDATE trigger

Hi,

I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this rollback record in the table? It seems it does and I am wondering if there is any workaround.

Thanks,

It depends on the severity of the error. You should do RAISERROR with a lesser severity and then do ROLLBACK explicitly. See Books Online topic on RAISERROR and how to use it in triggers in SQL Server 2005. There are several samples that shows how to use RAISERROR.

Inside Common Table Expressions (CTE)

Hello all.
I would like to know exacly what improvements does CTE offers to compare its
performance to Table Variables for the perpouse of 'Paging' through data.
I curently build a table variable (i.e: @.return(rowid int identity, matchid
int)), populate it with matching ids and select from @.return where rowid
between N and N.
What advantages, if any does CTE have over the above solution?
What advantages, if any does CTE have over Cursor 'Paging'?
What details can you provide about the CTEs inner workings?
Thank you very much, in advance, for all your help!
- Eyal ZinderSome of what you are asking with regard to paging
may be answered here
http://www.aspfaq.com/show.asp?id=2120
There isn't a CTE sample but my own using
ROW_NUMBER()...OVER didn't perform significantly
better than the "RowCount" version.

inset table using sp

Hi,
I have a select statement from tb1 and tb2:
select field1 from tb1 where field1 not in (select field1 from tb2)
order by field1
I want to have the result inserted into tb3 using a SP.
tb3 has only one filed, same type. How to do this?
ThanksCheck out INSERT INTO in BOL.
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
You can put it in a stored procedure like this:
CREATE PROCEDURE dbo.usp_DoInsert
AS
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
GO
BTW you might want to reconsider your naming conventions - this particular
setup is going to be very hard to understand and maintain.
"whph" <nospam@.nospam.com> wrote in message
news:mqo2719mcnvjerec932v8i49mof003ikk4@.
4ax.com...
> Hi,
> I have a select statement from tb1 and tb2:
> select field1 from tb1 where field1 not in (select field1 from tb2)
> order by field1
> I want to have the result inserted into tb3 using a SP.
> tb3 has only one filed, same type. How to do this?
> Thanks

Inserts....

Hi..
I have a problem...
I have a temporary table and I'm traying to do this...
create table #tmp
( valor varchar(100),
valor1 varchar(100),
valor2 varchar(100)
)

declare @.cmd varchar(500)

select @.cmd = 'Select valor, valor2 from mytable)

insert into #tmp(valor, valor2)
exec(@.cmd)

select valor, valor2 from #tmp

but I have an error... 'Invalid object name #tmp'

why??other than a few typos everything looked good try this:

if object_id('mytable') is not null drop table mytable
if object_id('tempdb..#tmp') is not null drop table mytable

create table mytable(
valor varchar(100),
valor1 varchar(100),
valor2 varchar(100))

insert into mytable values('A','B','C')
insert into mytable values('D','E','F')
insert into mytable values('G','H','I')
insert into mytable values('J','K','L')

create table #tmp(
valor varchar(100),
valor1 varchar(100),
valor2 varchar(100))

declare @.cmd varchar(500)

select @.cmd = 'Select valor, valor2 from mytable'

insert into #tmp(valor, valor2)
exec(@.cmd)

select valor, valor2 from #tmp|||If you have this code inside of a stored proc it should work, otherwise it won't because your temporary table is local not global. (local temporary tables are visible only in the current sesion).
Try using:
CREATE TABLE ##tmp

or

CREATE TABLE tempdb..tmp

see "tempoary tables" in BOL for more details

Inserts with and without a clustered index...

Hi,
I created 2 identical tables with just 12 records each. Then I created a clustered index in one table, but not on the other. I then tried inserting 2 identical records in both the tables. I guessed that in the table without the index the insertion would be faster, because there is no overhead of re-ordering the table based on the clustered index. I found that the insertion is actually faster on the table with the clustered index. How is this possible? Isn't inserts supposed to faster on heap files?A while ago I looked into the same matter but found that inserting rows in a table with/out indices took an equal amount of time, that is, the overhead of updating indices didn't change anything. I had quite a bit more rows than 12 though.

Having that said; I'm not exactly sure how to interpret these kind of results anyway. There could be a lot of influences that affect the sql-processes running in the background that again influence the select or insert results. The only thing I concluded was that, in _my_ case, it didn't matter that much.|||Well, if your table structure is like this create table dbo.t1 (
f1 int identity(1,1) not null primary key clustered,
f2 char(1) null)
then there is no difference. But if it's like this create table dbo.t2 (
f1 uniqueidentifier not null primary key clustered,
f2 char(1) null) then your INSERTS are going to be slower than if you remove CLUSTERED from your PK.

In addition, it's not the presence of the clustered index that imposes overhead, it's non-clustered indexes that require any action query to be accompanied by an implicit update of index pages.|||Thanks guys for the replies. But, doesn't having a clustered index mean that the records in the table are to be physically sorted? Therefore even if there is enough space on the page which should contain the record being inserted, the DBMS would still have to search for that page and insert the record, as opposed to simply appending it at the end, in the case of a heap file (unindexed file).
Maybe I see this in my example because the table is too small and everything fits into one disk page. How do you think my observations would have been if the table had a million records?|||It still goes to the last extent allocated to the table but does a simple check - is the new value greater than the last value? If the answer is YES - that's the end of the story. Else, - then we are gonna be talking about the overhead of the clustered index.

Inserts to tables as one or two users.

SQL 7.0 SP4
Background: C++ program accesses SQL via ODBC doing
massive inserts to two different tables. The data goes
either to one table or the other, but not both.
Problem: Is it more efficient to have the program access
the database as one or two SQL users?
1. user DOG inserts to table XXX "and" table YYY.
or
2. user DOG inserts to table XXX,
user CAT inserts to table YYY?
Thanks for your help,
DonUnless you need to load them in parallel there isn't any reason to use two
users. Adding connections has a slight overhead that you probably don't
need for this type operation.
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:0bf401c38126$d4174120$a001280a@.phx.gbl...
> SQL 7.0 SP4
> Background: C++ program accesses SQL via ODBC doing
> massive inserts to two different tables. The data goes
> either to one table or the other, but not both.
> Problem: Is it more efficient to have the program access
> the database as one or two SQL users?
> 1. user DOG inserts to table XXX "and" table YYY.
> or
> 2. user DOG inserts to table XXX,
> user CAT inserts to table YYY?
> Thanks for your help,
> Don|||no, it wouldn't make any difference in performance, it
will only increase work for you to manage permissions to
two users.
Also, consider using stored procedures rather than direct
insert statements. this will give slight performance gain,
and better management of sql code.
>--Original Message--
>SQL 7.0 SP4
>Background: C++ program accesses SQL via ODBC doing
>massive inserts to two different tables. The data goes
>either to one table or the other, but not both.
>Problem: Is it more efficient to have the program access
>the database as one or two SQL users?
>1. user DOG inserts to table XXX "and" table YYY.
>or
>2. user DOG inserts to table XXX,
> user CAT inserts to table YYY?
>Thanks for your help,
>Don
>.
>

Inserts taking longer time

Hello All,

I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.

Declare @.i int
Set @.i=0
While (@.i<1500)
Begin
Insert into test2 values (@.i)
Set @.i=@.i+1
End

Here goes the table definition,

CREATE TABLE [dbo].[test2](
[int] NULL
) ON [PRIMARY]

Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds.
Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful

Thanks in advance,
Mitesh

Although this can′t be the possible cause, which recovery models are you using on the databases ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hey Jens,
Thanks for the pointer but we are using the Full Recovery model on both of my database. I think there is some problem with the server configuration as I have tried this Insert on multiple databases of my server (where the time taken is more) and have got the same output
|||

You machine is probably caching disk writes so the commits are not actually hardening.Servers are usually configured not to do that so your data is not at risk.

You should wrap your loop with a BEGIN TRAN and COMMIT TRAN.

Declare @.i int
Set @.i=0

Begin Tran
While (@.i<1500)
Begin
Insert into test2 values (@.i)
Set @.i=@.i+1
End
Commit Tran

|||

Hi Mitesh,

I am having a very similar problem:

* I have a crappy testing machine (P3 at 0.5 GHz on Windows NT Server) with SQL 7,

* I have a testing environment running on a Virtual Machine on a pretty descent laptop (Laptop = Celeron @. 1.59 GHz on Windows XP - sorry, low budget -). The Virtual Machine runs Windows 2003 with SQL 2005 Express, with 400 MBytes assigned in RAM.

* The database I work with is created running an SQL script. It has about 15 tables, and as a whole is a quite normalized database.

* I have this application that I did, that updates the database through ODBC.

Now here is the interesting data:

* On my SQL 7, it takes between 2 to 3 seconds to update the database (we are talking about 50 to 100 insert statements) the first time I click on the "save" button. After that is just a matter of a little bit less than a second to update the database every time I click on the "save" button.

* However, on my SQL 2005 environment, it takes about 1 minute. The amount of time does not change much the next time I click "save".

Notes aside:

* Both are empty databases. They only have the table structure.

* The tables are quite normalized. It is ok, it is not a database that it is intended to grow beyond 100 Mbytes.

* "De-normalization" is not an option.

* I changed the size of the database log file from 1 Mbyte to 20 to see if my problem were excesive "check points"... but the behaviour was just the same.

I learned to live with it, after all, Windows XP does not support VM's. In fact, I was rather impressed by the Celeron chip.

But the fact that caught my attention is that, after time went by and I continued using my Virtual Machine, the time got reduced as my database grew up in size. After about 100 "save" operations, It now takes around 10 seconds to save. This suggests me that there is something going on with the indexes...

It is ok, after all, it is just a testing environment.

Hope it helps!!!

Tark Ik

PS: This posting has been posted "AS IS"

|||Hey Peter,
Thanks a Ton for your reply. There would have been no way in the world through which i could have figured this out . The other thing I want to ask is that how can we disable the caching on our server. I mean could not exactly figure out how did the begin tran and commit solved the purpose. Will be really helpful if you can tell how to disable the caching and hardening and is this safe.

Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?
You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgsDon't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:
>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:
> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
> >Hi ,
> >
> > At SQL server's end , i have put my windows id as a readonly for that
> >database.
> >
> > however from MS Access when i specify the ODBC i used the sa id & pwd
> >
> >from Ms Access , i linked to the SQL server db using ODBC connection ,when i
> >tried to insert some data into SQL server i was not allowed
> >
> > why is that so as i was using sa pwd from the odbc connection
> >
> > Could anyone kindly advise ?
> >
> >tks & rdgs
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgsDon't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:

>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when
i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:

> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
>
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgs
Don't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:

>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs
|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:

> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
>
>

Insertion data via Stored Procedure [URGENT]!

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??

Example:
----
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.

FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)

Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.

INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)
INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)

|||Ok, what about if I want to do these process in two SPs??

I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).

Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.

I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?

Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET

Are are ok, if you would like to demonstrate your replay. ( I want the answer!).

Thanks again.|||YES!!

The problem was from my ADO.NET part.

Thanks for your help gays.

Insertion and Updates on 20.000.000 tuples table.

Hi,
I have a table with 20.000.000 of tuples.
I have been monitoring the performance of the insertion and updates,
but not convince me at all.
The table have 30 columns, what and 12 of it, are calcultated column.

The test that i do was this:

1 Insertion with all the columns and calculing the calcultated columns
in the insertion sentence.

1 insertion and all the columns calculated in @.vars..

1 insertion with the basic fields, and 10 updates.

And the result was that the last test was the most performant.

What is your opinion?Andrix (elkpichico@.gmail.com) writes:
> I have a table with 20.000.000 of tuples.
> I have been monitoring the performance of the insertion and updates,
> but not convince me at all.
> The table have 30 columns, what and 12 of it, are calcultated column.
> The test that i do was this:
> 1 Insertion with all the columns and calculing the calcultated columns
> in the insertion sentence.
> 1 insertion and all the columns calculated in @.vars..
> 1 insertion with the basic fields, and 10 updates.
> And the result was that the last test was the most performant.
> What is your opinion?

That your posting is not very clear. I would take "calculated columns"
to mean "computed columns", but since you can't insert explicit values
in computed columns that does not really fit.

Why not post the code you used, so it's easier to understand what you
are talking about.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi.

The test that i do, were this:
select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.imp * @.ohter, ...)

I mean that in the same Insert sentence, i do all the calcs to insert
in the table.

the other,
was
select @.cal1 = @.imp * @.other
select @.cal2 = @.imp * @.other - @.umbral

select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.calc1,@.calc2, ...)

and the last one was:
select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... )

select @.cal1 = @.imp * @.other
select @.cal2 = @.imp * @.other - @.umbral

update shared_calc_imp
set calc1 = @.calc1
where pk = @.PK

update shared_calc_imp
set calc2 = @.calc2
where pk = @.PK

thanks!

Andrix.

Erland Sommarskog wrote:
> Andrix (elkpichico@.gmail.com) writes:
> > I have a table with 20.000.000 of tuples.
> > I have been monitoring the performance of the insertion and updates,
> > but not convince me at all.
> > The table have 30 columns, what and 12 of it, are calcultated column.
> > The test that i do was this:
> > 1 Insertion with all the columns and calculing the calcultated columns
> > in the insertion sentence.
> > 1 insertion and all the columns calculated in @.vars..
> > 1 insertion with the basic fields, and 10 updates.
> > And the result was that the last test was the most performant.
> > What is your opinion?
> That your posting is not very clear. I would take "calculated columns"
> to mean "computed columns", but since you can't insert explicit values
> in computed columns that does not really fit.
> Why not post the code you used, so it's easier to understand what you
> are talking about.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do not store calculations in a table. You can do the calculations in a
VIEW, in the application or in computed columns (a proprietary
shorthand) for a VIEW. This will save you disk space of course. It
will also run faster, since reading from a disk is very slow compared
to math done in main storage. But the real benefit is data integrity,
which your approach will destroy.|||Andrix (elkpichico@.gmail.com) writes:
> The test that i do, were this:
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.imp * @.ohter, ...)
> I mean that in the same Insert sentence, i do all the calcs to insert
> in the table.
> the other,
> was
> select @.cal1 = @.imp * @.other
> select @.cal2 = @.imp * @.other - @.umbral
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.calc1,@.calc2, ...)
> and the last one was:
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... )
> select @.cal1 = @.imp * @.other
> select @.cal2 = @.imp * @.other - @.umbral
> update shared_calc_imp
> set calc1 = @.calc1
> where pk = @.PK
> update shared_calc_imp
> set calc2 = @.calc2
> where pk = @.PK

And you are saying that the last test had the best performance?
Surprising.

But how many times did you run each test? Did you ensure that there
was no other load on the server? Did you work with the same table
that you just added to each time? Or did you recreate the table for
each test? And what were the results in numbers? Were the huge
differences?

Running performance tests requires care to avoid sources of error. One
very important is that any measurement below 50 ms contains to much
white noise to be reliable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insertion / updation problem in SSIS

“I have a scenario where i am trying to insert 200,000 lac records & update 200,000 lac record in destination table using SISS Package (SQL SERVER 2005) but I am not able to neither update nor insert the records . while executing the package its not showing any error also . what could be the problem ? “

We have business logic in Package creation 1) Insert New records and 2) Update Existing Records using the follow Data flow diagram

For update we are using OLEDB command, for insert we are using OLEDB Destination.

We are using merge join for spliting record into insert and update.

Perhaps there is blocking on the destination table. This can often happen if you're attempting 2 operations simultaneously.

Execute sp_who2 to see if there's any blocking going on.

-Jamie

|||

is there any other solution for this problem, is this not possible to run for achive both insertion and updation in the same package? if i try to run the package for less records, it is succeded, if i try to run the package for more records, then same problem coming again and again

Thanks & Regards

S.Nagarajan

|||

In that case I am even more sure that blocking is a problem. Did you bother to execute sp_who2 like I advised?

There is an easy fix to this problem. Continue to do the insert but push teh adta to be updated into a raw file. You can then use the contents of the raw file in another data-flow in order to do the update,.

-Jamie

|||I ran sp_who2 and found the blocking, how can i remove the blocking, is there any query to remove the blocking. I couldn't get your solution clearly, please brief me your alternate solution.|||

Do you know what raw files are? If not, go away and study them. When you have finished read #1 here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

It describes a different scenario for using raw files but the usage is the same.

-Jamie

|||

We have completed upto move to flat destination files using raw files, could you please help to move this flat files data (for update) to database. what logic we need to use? is it required to add dataflow diagram after raw file destination component

|||

Please stop writing the same question in multiple threads simultaneously. People are here to help and don't want to waste their time clicking through and reading the same thing more than once.

-Jamie

inserting\update line numbering

I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.|||If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.

This is a daily build operation

Using Identity though creates the numbering for all records in the table (sees it as one order (500 records, records numbered from 1 to 500?)

I am looking at the line numbering to reset back to 1 everytime there is a change in the order number field (inv_ref field)

this can't be done through identity?|||My guess is, that it would be possible using an identity column, but I wouldn't go for that if it needs to be reset on a daily basis.

It's probably me, but I'm still not quite clear on what you want, on the other hand, maybe I do but miss the point as to why you need a linenumber associated with the table contents.

One of these might work for you though:
- create a view that has a computed column (if the linenumber can be determined on other information from the table);
- create a trigger that does an update (guess this can be quite a burden);
- create an sp; do an update based on identity from a temp-table.|||line number id forms part of the primary key make up.

I have Invoice number, sales order number, and line id

I can't include product id instead of line id as in an invoice there may be a reference to the same product id i.e. at line 1 and 10.

I guess a messy way of going about it is to just use identity and leave the count go on the entire table just to satisfy the primary key requirements.

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind Regards
For double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>