output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/d...
25v.asp
or
http://msdn.microsoft.com/library/d... />
ql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:

> How to get a field from a specific column as output parameter?
> Hrcko
>
>

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp
or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:
> How to get a field from a specific column as output parameter?
> Hrcko
>
>

Outer Join with 3 tables

Hope this is as easy as I think, but I am struggling to find answer in BOL,
etc.
I have 3 simple tables and want to link them on the same field, "ProductID".
The first table has all productid's on open SalesOrders and the qty sold.
The second table has productid's in Inventory for OnHand quanitity,
and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
I need to make sure all of the records in table one (SalesOrders) are
included regardless of the data in the other two tables. Additionally I wan
t
to return ONLY the ProductId's from the first table. LeftOuter Join isn't
working for me.
Obviously I am a newbie, and it's probably a dumb question, but you guys
always have the right answer really fast...Chuck,
I think that is equivalent to:
select ProductID from SalesOrders
AMB
"Chuck" wrote:

> Hope this is as easy as I think, but I am struggling to find answer in BOL
,
> etc.
> I have 3 simple tables and want to link them on the same field, "ProductID
".
> The first table has all productid's on open SalesOrders and the qty sold.
> The second table has productid's in Inventory for OnHand quanitity,
> and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
> I need to make sure all of the records in table one (SalesOrders) are
> included regardless of the data in the other two tables. Additionally I w
ant
> to return ONLY the ProductId's from the first table. LeftOuter Join isn't
> working for me.
> Obviously I am a newbie, and it's probably a dumb question, but you guys
> always have the right answer really fast...|||Sorry I was vague. I would like to return...
ProductID.SalesOrders
QtyOrdered.SalesOrders
QtyOnHand.Inventory
QtyOnPO.PurchaseOrders
returning all of the ProductID's and QtyOrdered in SalesOrders
and the quantities in Inventory and PurchaseOrders regardless of whether
that particular ProductID exists in that table (returning a null value if it
doesn't exist I presume).
In other words, two left outer joins to SalesOrders.
"Alejandro Mesa" wrote:
> Chuck,
> I think that is equivalent to:
> select ProductID from SalesOrders
>
> AMB
> "Chuck" wrote:
>|||Try,
select
so.ProductID,
so.QtyOrdered,
i.QtyOnHand,
po.QtyOnPO
from
SalesOrders as so
left join
Inventory as i
on so.ProductID = i.ProductID
left join
PurchaseOrders as po
on so.ProductID = po.ProductID
AMB
"Chuck" wrote:
> Sorry I was vague. I would like to return...
> ProductID.SalesOrders
> QtyOrdered.SalesOrders
> QtyOnHand.Inventory
> QtyOnPO.PurchaseOrders
> returning all of the ProductID's and QtyOrdered in SalesOrders
> and the quantities in Inventory and PurchaseOrders regardless of whether
> that particular ProductID exists in that table (returning a null value if
it
> doesn't exist I presume).
> In other words, two left outer joins to SalesOrders.
> "Alejandro Mesa" wrote:
>|||Thanks, That worked perfectly. For some reason I was getting an error when
attempting to create two outer joins in one query. Wonder why?
"Alejandro Mesa" wrote:
> Try,
> select
> so.ProductID,
> so.QtyOrdered,
> i.QtyOnHand,
> po.QtyOnPO
> from
> SalesOrders as so
> left join
> Inventory as i
> on so.ProductID = i.ProductID
> left join
> PurchaseOrders as po
> on so.ProductID = po.ProductID
>
> AMB
> "Chuck" wrote:
>sql

Out of Range Error When Updating A Decimal Value

I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny?

Parameter value '100.0' is out of range.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Parameter value '100.0' is out of range.

Source Error:

Line 1841: int returnValue;
Line 1842: try {
Line 1843: returnValue = command.ExecuteNonQuery();
Line 1844: }
Line 1845: finally {

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs Line: 1843

Stack Trace:

[ArgumentException: Parameter value '100.0' is out of range.]
 System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +4085
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +11

5
 StatisticsDALTableAdapters.StatisticsTableAdapter.UpdateStatistics(Nullable`1 ID, Nullable`1 PlayerID, Nullable`1 FG, Nullable`1 TTP, Nullable`1 FT, Nullable`1 RPG, Nullable`1 DEFR, Nullable`1 OFFR, Nullable`1 APG, Nullable`1 SPG, Nullable`1 BPG, Nullable`1 Turnovers, Nullable`1 PPG, String SYear, String EYear, String StatisticsType) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs:1843
 YourHoopSpace.BLL.StatisticsBLL.UpdateStatistic(Int32 ID, Int32 playerID, Nullable`1 fg, Nullable`1 ttp, Nullable`1 ft, Nullable`1 rpg, Nullable`1 defr, Nullable`1 offr, Nullable`1 apg, Nullable`1 spg, Nullable`1 bpg, Nullable`1 turnovers, Nullable`1 ppg, String sYear, String eYear, String statisticsType) in f:\MyProfile\App_Code\BLL\StatisticsBLL.cs:128

Is this the problem?

int returnValue;

Try decimal in your code. 

|||

HI~

How do you pass the parameters?

Here is sample code:

string str_test = TextBox1.Text; SqlConnection conn =new SqlConnection("Server=T-YOUNGF\\SQLEXPRESS;Database=pubs;uid=sa;pwd=pwd"); SqlCommand cmd =new SqlCommand("update decimal_test3 set column1=@.column1", conn); cmd.Parameters.AddWithValue("@.column1", 100.0); conn.Open();int i = cmd.ExecuteNonQuery(); Response.Write(i.ToString() +" row(s) has updated"); conn.Dispose();

And table:

create table decimal_test3 (column1decimal (4,1));

|||The problem was on my update I needed a decimal of (4,1) instead of (3,1) which I changed in the table not the stored procedure. Why does it need 4 instead of 3 to store 100?|||

decimal of (4,1) can hold upto 999.9

decimal of (3,1) can hold upto 99.9

So 100 is invalid.See decimal and numeric for details.

|||Thank you. I looked at that page, but really couldnt figure it out but now I know.sql

out of order identity field - sql2000

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don't understand why the values in tmp.C aren't in the order
suggested by the sort.

Any comments most appreciated
BevanTry ORDER BY C

<bevanward@.gmail.com> wrote in message
news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> Hi All
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.
> Any comments most appreciated
> Bevan|||Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:
> Try ORDER BY C
>
> <bevanward@.gmail.com> wrote in message
> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> > Hi All
> > I am finding unexpected results when inserted into a newly created
> > table that has a field of datatype int identity (1,1).
> > Basically the order I sort on when inserting into the table is not
> > reflected in the order of the values from the identity field.
> > Have I been wrong in assuming that it should reflect the order from the
> > sort?
> > The code is ...
> > create table tmp (A varchar(50), L float, C int identity(1,1))
> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> > and I don't understand why the values in tmp.C aren't in the order
> > suggested by the sort.
> > Any comments most appreciated
> > Bevan|||You can't rely on an IDENTITY column to be assigned in a particular order or
to not have gaps in the sequence, btw. Try assigning a rank value manually
instead:

CREATE TABLE #tmp (A VARCHAR(50),
L FLOAT,
C INT NOT NULL PRIMARY KEY)

CREATE TABLE #tmp1 (Aa VARCHAR(50),
Ll FLOAT(50),
PRIMARY KEY (Aa, Ll))

INSERT INTO #tmp1 (Aa, Ll)
SELECT 'ABC', 123.45
UNION SELECT 'DEF', 456.12
UNION SELECT 'XYZ', 999.99
UNION SELECT 'RST', 023.43
UNION SELECT 'GHI', 146.56

INSERT INTO #tmp (A, L, C)
SELECT t1.Aa, t1.Ll, COUNT(*) Rank
FROM #tmp1 t1
INNER JOIN #tmp1 t2
ON t1.Aa >= t2.Aa
AND t2.Ll >= t2.Ll
GROUP BY t1.Aa, t1.Ll
ORDER BY t1.Aa, t1.Ll

SELECT C, A, L
FROM #tmp
ORDER BY C

DROP TABLE #tmp1
DROP TABLE #tmp

<bevanward@.gmail.com> wrote in message
news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> Hi Mike
> Thanks for your comment - C is the field in the target table of the
> insert that I was hoping would increment in the same sequence as the
> sort of Aa, Ll
> Cheers
> Bevan
> Mike C# wrote:
>> Try ORDER BY C
>>
>>
>> <bevanward@.gmail.com> wrote in message
>> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
>> > Hi All
>>> > I am finding unexpected results when inserted into a newly created
>> > table that has a field of datatype int identity (1,1).
>>> > Basically the order I sort on when inserting into the table is not
>> > reflected in the order of the values from the identity field.
>>> > Have I been wrong in assuming that it should reflect the order from the
>> > sort?
>>> > The code is ...
>>> > create table tmp (A varchar(50), L float, C int identity(1,1))
>> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>>> > and I don't understand why the values in tmp.C aren't in the order
>> > suggested by the sort.
>>> > Any comments most appreciated
>> > Bevan
>|||Hi Mike

Thanks for your comprehensive response. I had always assumed that this
insert was dependable (sequential and contiguous) ... I guess I need to
go back and re-write anywhere I have existing code that made this
assumption.

Thanks again, most appreciated.

Cheers
Bevan

Mike C# wrote:
> You can't rely on an IDENTITY column to be assigned in a particular order or
> to not have gaps in the sequence, btw. Try assigning a rank value manually
> instead:
> CREATE TABLE #tmp (A VARCHAR(50),
> L FLOAT,
> C INT NOT NULL PRIMARY KEY)
> CREATE TABLE #tmp1 (Aa VARCHAR(50),
> Ll FLOAT(50),
> PRIMARY KEY (Aa, Ll))
> INSERT INTO #tmp1 (Aa, Ll)
> SELECT 'ABC', 123.45
> UNION SELECT 'DEF', 456.12
> UNION SELECT 'XYZ', 999.99
> UNION SELECT 'RST', 023.43
> UNION SELECT 'GHI', 146.56
> INSERT INTO #tmp (A, L, C)
> SELECT t1.Aa, t1.Ll, COUNT(*) Rank
> FROM #tmp1 t1
> INNER JOIN #tmp1 t2
> ON t1.Aa >= t2.Aa
> AND t2.Ll >= t2.Ll
> GROUP BY t1.Aa, t1.Ll
> ORDER BY t1.Aa, t1.Ll
> SELECT C, A, L
> FROM #tmp
> ORDER BY C
> DROP TABLE #tmp1
> DROP TABLE #tmp
>
> <bevanward@.gmail.com> wrote in message
> news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> > Hi Mike
> > Thanks for your comment - C is the field in the target table of the
> > insert that I was hoping would increment in the same sequence as the
> > sort of Aa, Ll
> > Cheers
> > Bevan
> > Mike C# wrote:
> >> Try ORDER BY C
> >>
> >>
> >> <bevanward@.gmail.com> wrote in message
> >> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> >> > Hi All
> >> >> > I am finding unexpected results when inserted into a newly created
> >> > table that has a field of datatype int identity (1,1).
> >> >> > Basically the order I sort on when inserting into the table is not
> >> > reflected in the order of the values from the identity field.
> >> >> > Have I been wrong in assuming that it should reflect the order from the
> >> > sort?
> >> >> > The code is ...
> >> >> > create table tmp (A varchar(50), L float, C int identity(1,1))
> >> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >> >> > and I don't understand why the values in tmp.C aren't in the order
> >> > suggested by the sort.
> >> >> > Any comments most appreciated
> >> > Bevan
> >|||<bevanward@.gmail.com> wrote in message
news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> Hi Mike
> Thanks for your comprehensive response. I had always assumed that this
> insert was dependable (sequential and contiguous) ... I guess I need to
> go back and re-write anywhere I have existing code that made this
> assumption.
> Thanks again, most appreciated.

No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
of the need for the self-join ranking method.|||Hi Mike

I have read fondly of row_number() in 2005 and can't wait. This has
existed in Oracle for years, from what I understand, and I'm not sure
how we have done without it for so long.

I have re-written the code for this and it doubles the execution time
unfortunately.

Thanks again for taking the time, most appreciated

Bevan

Mike C# wrote:
> <bevanward@.gmail.com> wrote in message
> news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> > Hi Mike
> > Thanks for your comprehensive response. I had always assumed that this
> > insert was dependable (sequential and contiguous) ... I guess I need to
> > go back and re-write anywhere I have existing code that made this
> > assumption.
> > Thanks again, most appreciated.
> No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
> of the need for the self-join ranking method.|||(bevanward@.gmail.com) writes:
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.

Interesting. I get it to work most of the time, and I've even been told
that this is guarranteed to work as expected. Definitely in SQL 2005,
but the source said it was OK for SQL 2000 as well.

However, if you are running on a multi-processor machine (including a
hyper-threaded CPU), try adding OPTION (MAXDOP 1) at the end of the
query.

Note that is you use SELECT INTO instead, there is no guarantee that
the order is the desired.

By the way, what does SELECT @.@.version say?

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E41419257FYazorman@.127.0.0.1...
> (bevanward@.gmail.com) writes:
> Interesting. I get it to work most of the time, and I've even been told
> that this is guarranteed to work as expected. Definitely in SQL 2005,
> but the source said it was OK for SQL 2000 as well.

I've found that it doesn't work all too often; particularly, as you pointed
out, if you are running hyperthreading, multiple processors, or have
multiple programs updating the table simultaneously. In that third
situation IDENTITY can leave extremely large gaps in a sequence. In my
experience, the only thing an IDENTITY column can guarantee is a different
number for each row.

To be honest, I don't think the INSERT statement guarantees the order in
which the rows will be inserted, which is a large part of the OP's problem
in this situation. Normally it doesn't matter what order rows get inserted
as long as they get in there. In this case the OP is dynamically assigning
numeric identifiers to each row as they're inserted which makes the order of
insertion important.

BTW - I didn't think about it last night, but with the SELECT INTO statement
(instead of INSERT) you might be able to use the IDENTITY() function to
assign values in the order you require. But SELECT INTO requires the target
table not exist before it's run. I haven't tried it, so can't guarantee it
would work, but hey...|||>> I am finding unexpected results when inserted into a newly created table that has a field [sic] of datatype int identity (1,1). <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

Next, by definition -- repeat BY DEFINITION !!! -- IDENTITY is not a
key.

>> Have I been wrong in assuming that it should reflect the order from the sort? <<

Your assumptions are MUCH worse than that! You have missed ALL of the
foundations of RDBMS. As they say in Zen, you must empty your cup to
drink new tea. Please get a good book on RDBMS, take some time and get
it right before you kill someone.|||This might help from the SQL Engine team blog...

http://blogs.msdn.com/sqltips/archi.../20/441053.aspx

Its point 4, the identities are calculated in the right order just not
inserted but the insert order shouldn't matter if the identities are
calculated in the correct order.

1.. If you have an ORDER BY in the top-most SELECT block in a query, the
presentation order of the results honor that ORDER BY request
2.. If you have a TOP in the same SELECT block as an ORDER BY, any TOP
computation is performed with respect to that ORDER BY. For example, if
there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows
within a given sort. Note that this does not guarantee that subsequent
operations will somehow retain the sort order of a previous operation. The
query optimizer re-orders operations to find more efficient query plans
3.. Cursors over queries containing ORDER BY in the top-most scope will
navigate in that order
4.. INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which the
rows are inserted
5.. SQL Server 2005 supports a number of new "sequence functions" like
RANK(), ROW_NUMBER() that can be performed in a given order using a OVER
clause with ORDER BY
6.. For backwards compatibility reasons, SQL Server provides support for
assignments of type SELECT @.p = @.p + 1 ... ORDER BY at the top-most scope.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Mike C#" <xxx@.yyy.com> wrote in message
news:Gylkg.2679$%12.1269@.fe09.lga...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97E41419257FYazorman@.127.0.0.1...
>> (bevanward@.gmail.com) writes:
>> Interesting. I get it to work most of the time, and I've even been told
>> that this is guarranteed to work as expected. Definitely in SQL 2005,
>> but the source said it was OK for SQL 2000 as well.
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a different
> number for each row.
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted, which is a large part of the OP's problem
> in this situation. Normally it doesn't matter what order rows get
> inserted as long as they get in there. In this case the OP is dynamically
> assigning numeric identifiers to each row as they're inserted which makes
> the order of insertion important.
> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require. But SELECT INTO
> requires the target table not exist before it's run. I haven't tried it,
> so can't guarantee it would work, but hey...|||Mike C# (xxx@.yyy.com) writes:
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a
> different number for each row.

Gaps due to simultaneous updates is another story. If you want contiguous
numbers, you should not use IDENTITY for your real tables. (You can
still generate ids with help of a temp table with an IDENTITY column.)

> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted,

Correct.

> which is a large part of the OP's problem in this situation.

I hope it isn't! What should matter is in which order the IDENTITY values
are generated. And that is what is guaranteed, at least in SQL 2005.

> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require.

No! I pointed this out in my post, but I say it again: SELECT INTO
with the IDENTITY() function gives no guarantee about order, and is
overall more prone to botch the order.

--
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|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e6timt$hna$1$8300dec7@.news.demon.co.uk...
> This might help from the SQL Engine team blog...
> http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
> Its point 4, the identities are calculated in the right order just not
> inserted but the insert order shouldn't matter if the identities are
> calculated in the correct order.

I noticed the blogger states "*most* of the rules are valid for SQL 2000
too", though he doesn't specify which ones.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E4EF3786F5CYazorman@.127.0.0.1...
> Mike C# (xxx@.yyy.com) writes:
>> I've found that it doesn't work all too often; particularly, as you
>> pointed out, if you are running hyperthreading, multiple processors, or
>> have multiple programs updating the table simultaneously. In that third
>> situation IDENTITY can leave extremely large gaps in a sequence. In my
>> experience, the only thing an IDENTITY column can guarantee is a
>> different number for each row.
> Gaps due to simultaneous updates is another story. If you want contiguous
> numbers, you should not use IDENTITY for your real tables. (You can
> still generate ids with help of a temp table with an IDENTITY column.)

So we agree on gaps.

>> To be honest, I don't think the INSERT statement guarantees the order in
>> which the rows will be inserted,
> Correct.

And insert statement order guarantees.

>> which is a large part of the OP's problem in this situation.
> I hope it isn't! What should matter is in which order the IDENTITY values
> are generated. And that is what is guaranteed, at least in SQL 2005.

But this is a SQL 2000 problem. If this is supposed to be guaranteed in SQL
2000 as well, then there's apparently a hot fix needed for the OP's problem.

>> BTW - I didn't think about it last night, but with the SELECT INTO
>> statement (instead of INSERT) you might be able to use the IDENTITY()
>> function to assign values in the order you require.
> No! I pointed this out in my post, but I say it again: SELECT INTO
> with the IDENTITY() function gives no guarantee about order, and is
> overall more prone to botch the order.

Hence my use of the word "might", as in "I didn't try this, so I don't know
if it will produce desired results or not."