Insert a record in a particular place

[surya]

i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram
i want to insert a one record(102 , chandra) in HH table between
(101,baba) and( 103 ,ram).
how can i insert them please help ,me thanks

[Erland Sommarskog]

surya (su*******@gmail.com) writes:

i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram
i want to insert a one record(102 , chandra) in HH table between
(101,baba) and( 103 ,ram).
how can i insert them please help ,me thanks

Just as you would insert any other row:

INSERT HH(hhno, hhnmae)
VALUES (102, chandra)

Then again, one can say that you can't because "between" does not make
any sense in this context. Tables are unordered sets of rows.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Portas]

surya wrote:

i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram
i want to insert a one record(102 , chandra) in HH table between
(101,baba) and( 103 ,ram).
how can i insert them please help ,me thanks

Like this:

INSERT INTO hhtable (hhno, hhname)
(102 , 'chandra');

SELECT hhno, hhname
FROM hhtable
ORDER BY hhno ;

A table doesn't have any order. Only the results of a SELECT statement
can be sorted.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Ralph Ganszky]

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

surya wrote:

i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram
i want to insert a one record(102 , chandra) in HH table between
(101,baba) and( 103 ,ram).
how can i insert them please help ,me thanks

Like this:

INSERT INTO hhtable (hhno, hhname)
(102 , 'chandra');

SELECT hhno, hhname
FROM hhtable
ORDER BY hhno ;

A table doesn't have any order. Only the results of a SELECT statement
can be sorted.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

I think you can have an ordered table in SQL Server if you are using a
clustering index.

Regards
Ralph

[David Portas]

Ralph Ganszky wrote:

I think you can have an ordered table in SQL Server if you are using a
clustering index.

Regards
Ralph

You cannot. That is to say, a table with a clustered index is still not
logically ordered.

The OP asked how to insert a row between other rows. Although a
clustered index might achieve something like that at the page level a
clustered index doesn't really answer the original question - there is
no way to INSERT rows between each other. The only reliable way to get
ordered results from a table is to use ORDER BY when you query the
table.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Eric J. Holtman]

"Ralph Ganszky" <[e-mail]> wrote in news:e5*************@news.t-
online.com:

I think you can have an ordered table in SQL Server if you are using a
clustering index.

You think wrong. If you don't use an ORDER BY clause,
the returned order is undefined.

Sure, if you have a clustered index, AND you're only
selecting from one table, AND you're not using a where
clause that causes the optimizer to choose differently,
AND the phase of the moon is just right, THEN you **MIGHT**
get an ordered set.

In fact, that's pretty much guaranteed to work, right up
until you're demo'ing your project for the clients.

[Ralph Ganszky]

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

Ralph Ganszky wrote:

I think you can have an ordered table in SQL Server if you are using a
clustering index.

Regards
Ralph

You cannot. That is to say, a table with a clustered index is still not
logically ordered.

The OP asked how to insert a row between other rows. Although a
clustered index might achieve something like that at the page level a
clustered index doesn't really answer the original question - there is
no way to INSERT rows between each other. The only reliable way to get
ordered results from a table is to use ORDER BY when you query the
table.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

How do you know that the OP asked exactly what you expect he have asked? Are
you sure that he asked what you think/want he has asked? What I meant is
exactly what he asked from my point of view. He asked if it is possible to
insert in between two other records. A clustered index does exactly this on
row level. It has on the other side nothing to do with the order in the
result set as you mentioned. But the answer is true any way for the physical
representation on the disk. I aggree that in relational logic there is no
order in a set, but SQL implementation of the relational model are allmost
allways no strict implementations and have some features which are against
the relational model from Cod. Especially MS SQL Server have many features
which are not in the sense of Cod, e.g. the FROM clause in the UPDATE
statement.

I think the OP can now choose from the replies which answer answers his
question.

Regards Ralph

[Erland Sommarskog]

Ralph Ganszky ([e-mail]) writes:

How do you know that the OP asked exactly what you expect he have asked?
Are you sure that he asked what you think/want he has asked?

Taken to the letter, suryv'a question did not make sense.

My guess that is that what he was really asking about was the Open Table
feature, where he wanted to right-click and select Insert as in Excel,
and a row in the middle.

But that's the way the story goes. If you are short on details in your
questions, the answers may not apply to your real problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Portas]

Ralph Ganszky wrote:

How do you know that the OP asked exactly what you expect he have asked? Are
you sure that he asked what you think/want he has asked? What I meant is
exactly what he asked from my point of view. He asked if it is possible to
insert in between two other records. A clustered index does exactly this on
row level. It has on the other side nothing to do with the order in the
result set as you mentioned. But the answer is true any way for the physical
representation on the disk.

Only partly right. A clustered index will give rows a positional order
that can be reconstructed internally in the clustered index. However,
that order is normally inaccessible to the user. Your statement that
this is a physical order on disk is misleading. There need not be any
such physical ordering and even if it exists initially there is no
guarantee it will be maintained by a clustered index.

To refer to this in the way you did (an "ordered table") without any
qualification or explanation is to invite confusion. A table is a
logical model concept and definitively does not have a logical ordering
- not even given the faults and features of SQL and T-SQL. It's
difficult to see how a clustered index answers the original question
(if the OP wasn't aware of clustered indexes then how could he be
asking a question about their internal ordering?) but even if it did, I
wanted to clear up any confusion about the phrase "ordered table".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Hugo Kornelis]

On 28 May 2006 03:12:03 -0700, David Portas wrote:

Ralph Ganszky wrote:

How do you know that the OP asked exactly what you expect he have asked? Are
you sure that he asked what you think/want he has asked? What I meant is
exactly what he asked from my point of view. He asked if it is possible to
insert in between two other records. A clustered index does exactly this on
row level. It has on the other side nothing to do with the order in the
result set as you mentioned. But the answer is true any way for the physical
representation on the disk.

Only partly right. A clustered index will give rows a positional order
that can be reconstructed internally in the clustered index. However,
that order is normally inaccessible to the user. Your statement that
this is a physical order on disk is misleading. There need not be any
such physical ordering and even if it exists initially there is no
guarantee it will be maintained by a clustered index.

Hi David,

Exactly. I checked this using the following script:

USE TestDB90
-- Create table
CREATE TABLE HH
(hhno int NOT NULL PRIMARY KEY CLUSTERED,
hhname varchar(40) NOT NULL)
go
-- Insert initial population
INSERT INTO HH (hhno, hhname)
SELECT 100, 'suresh'
UNION ALL
SELECT 101, 'baba'
UNION ALL
SELECT 103, 'ram'
go
-- Attempt to add row "in between" other rows
INSERT INTO HH (hhno, hhname)
SELECT 102, 'chandra'
go
-- Get DBCC PAGE output to query window instead of system log
DBCC TRACEON(3604)
go
-- Find page in DB file where table data is stored
SELECT first FROM sysindexes WHERE id = OBJECT_ID('HH')
go
-- Check page (calculate page number from previous query output
DBCC PAGE(TestDB90, 1, 208, 2)
go

And here are some snippets from the output:

609EC060: 30000800 64000000 0200fc01 00150073 †0...d..........s
609EC070: 75726573 68300008 00650000 000200fc †uresh0...e......
609EC080: 01001300 62616261 30000800 67000000 †....baba0...g...
609EC090: 0200fc01 00120072 616d3000 08006600 †.......ram0...f.
609EC0A0: 00000200 fc010016 00636861 6e647261 †.........chandra
609EC0B0: 00000000 00000000 00000000 00000000 †................
(...)
609EDFF0: 01000000 381d0000 88009a00 75006000 †....8.......u.`.

OFFSET TABLE:

Row - Offset
3 (0x3) - 136 (0x88)
2 (0x2) - 154 (0x9a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

As you can see, the physical order matches (this case, coincidentally)
the order of insertion. The "logical" ordering according to the
clustered index is enforced in the offset table (which is encoded in the
last few bytes of the pages - you'll recognise the binary values of the
offsets in the last line of the dump).

--
Hugo Kornelis, SQL Server MVP

[Peter]

SQL Server does not have a functionality to insert a record in a particular
place in a table.

If you are talking about a clustered index then
1. You are not talking about the functionality 'table' but about an
implementation detail.
2. The function of a clustered index is to speed up queries, not to insert
in a specific position.

It makes no sense to insert a record in a particular place. It makes sense
to:
1. get records in a order by using order by
2. speed up querys by using a clustered index.

It is wrong to expect sorted records in the result set of a query without
using a order by, even if there is a clustered index. Functional spoken, the
concept table (also in SQL Server) has no order.

Peter

"Ralph Ganszky" <[e-mail]> wrote in message
news:e5*************@news.t-online.com...

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

surya wrote:

i have a table name is HH table
it has two columns 'hhno' and hhname'
HH tabele
hhno hhname
100 suresh
101 baba
103 ram
i want to insert a one record(102 , chandra) in HH table between
(101,baba) and( 103 ,ram).
how can i insert them please help ,me thanks

Like this:

INSERT INTO hhtable (hhno, hhname)
(102 , 'chandra');

SELECT hhno, hhname
FROM hhtable
ORDER BY hhno ;

A table doesn't have any order. Only the results of a SELECT statement
can be sorted.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

I think you can have an ordered table in SQL Server if you are using a
clustering index.

Regards
Ralph