sorting table while inserting

Hi Friends,

How can we insert records in sorted order

like consider a table

No Name Phone
1 test1 12345
1 test1 23455
2 test2 68638
3 test3 67684
4 test4 54808
4 test4 74594


if i add a new record like this

2 test2 34454

it should go in specific order. so that the final table should look
something like this

No Name Phone
1 test1 12345
1 test1 23455
2 test2 34454
2 test2 68638
3 test3 67684
4 test4 54808
4 test4 74594

Re: sorting table while inserting

Tables don't store data in a logical order. They store data in a
physical order. If you want to see data in an ordered fashion then
create a view to do that. Also, Name is a reserved word, don't use
it...

SELECT
No,
NameField,
Phone
FROM
yourTable
ORDER BY
No, NameField, Phone

Cheers,
Jason Lepack
On May 29, 12:04 pm, Oonz wrote:
> Hi Friends,
>
> How can we insert records in sorted order
>
> like consider a table
>
> No Name Phone
> 1 test1 12345
> 1 test1 23455
> 2 test2 68638
> 3 test3 67684
> 4 test4 54808
> 4 test4 74594
>
> if i add a new record like this
>
> 2 test2 34454
>
> it should go in specific order. so that the final table should look
> something like this
>
> No Name Phone
> 1 test1 12345
> 1 test1 23455
> 2 test2 34454
> 2 test2 68638
> 3 test3 67684
> 4 test4 54808
> 4 test4 74594

Re: sorting table while inserting

On May 29, 9:19 pm, Jason Lepack wrote:
> Tables don't store data in a logical order. They store data in a
> physical order. If you want to see data in an ordered fashion then
> create a view to do that. Also, Name is a reserved word, don't use
> it...
>
> SELECT
> No,
> NameField,
> Phone
> FROM
> yourTable
> ORDER BY
> No, NameField, Phone
>
> Cheers,
> Jason Lepack
> On May 29, 12:04 pm, Oonz wrote:
>
>
>
> > Hi Friends,
>
> > How can we insert records in sorted order
>
> > like consider a table
>
> > No Name Phone
> > 1 test1 12345
> > 1 test1 23455
> > 2 test2 68638
> > 3 test3 67684
> > 4 test4 54808
> > 4 test4 74594
>
> > if i add a new record like this
>
> > 2 test2 34454
>
> > it should go in specific order. so that the final table should look
> > something like this
>
> > No Name Phone
> > 1 test1 12345
> > 1 test1 23455
> > 2 test2 34454
> > 2 test2 68638
> > 3 test3 67684
> > 4 test4 54808
> > 4 test4 74594- Hide quoted text -
>
> - Show quoted text -

But actually i want to do further processing with that sorted table.
Thats why i am in need of such structure

Thanks
Arunkumar.D

Re: sorting table while inserting

What exactly do you want to do? Maybe there is a better way of doing
it that you haven't come across.

Give a little more detail and more help can be given.

Cheers,
Jason Lepack

On May 29, 12:24 pm, Oonz wrote:
> On May 29, 9:19 pm, Jason Lepack wrote:
>
>
>
>
>
> > Tables don't store data in a logical order. They store data in a
> > physical order. If you want to see data in an ordered fashion then
> > create a view to do that. Also, Name is a reserved word, don't use
> > it...
>
> > SELECT
> > No,
> > NameField,
> > Phone
> > FROM
> > yourTable
> > ORDER BY
> > No, NameField, Phone
>
> > Cheers,
> > Jason Lepack
> > On May 29, 12:04 pm, Oonz wrote:
>
> > > Hi Friends,
>
> > > How can we insert records in sorted order
>
> > > like consider a table
>
> > > No Name Phone
> > > 1 test1 12345
> > > 1 test1 23455
> > > 2 test2 68638
> > > 3 test3 67684
> > > 4 test4 54808
> > > 4 test4 74594
>
> > > if i add a new record like this
>
> > > 2 test2 34454
>
> > > it should go in specific order. so that the final table should look
> > > something like this
>
> > > No Name Phone
> > > 1 test1 12345
> > > 1 test1 23455
> > > 2 test2 34454
> > > 2 test2 68638
> > > 3 test3 67684
> > > 4 test4 54808
> > > 4 test4 74594- Hide quoted text -
>
> > - Show quoted text -
>
> But actually i want to do further processing with that sorted table.
> Thats why i am in need of such structure
>
> Thanks
> Arunkumar.D- Hide quoted text -
>
> - Show quoted text -

Re: sorting table while inserting

Jason Lepack wrote:

> What exactly do you want to do? Maybe there is a better way of doing
> it that you haven't come across.

More specifically, it sounds like he (like many others) is stuck on
procedural rather than functional thinking. Beware the frumious CURSOR!

> Give a little more detail and more help can be given.

Agreed.

Re: sorting table while inserting

On May 29, 9:27 pm, Jason Lepack wrote:
> What exactly do you want to do? Maybe there is a better way of doing
> it that you haven't come across.
>
> Give a little more detail and more help can be given.
>
> Cheers,
> Jason Lepack
>
> On May 29, 12:24 pm, Oonz wrote:
>
>
>
> > On May 29, 9:19 pm, Jason Lepack wrote:
>
> > > Tables don't store data in a logical order. They store data in a
> > > physical order. If you want to see data in an ordered fashion then
> > > create a view to do that. Also, Name is a reserved word, don't use
> > > it...
>
> > > SELECT
> > > No,
> > > NameField,
> > > Phone
> > > FROM
> > > yourTable
> > > ORDER BY
> > > No, NameField, Phone
>
> > > Cheers,
> > > Jason Lepack
> > > On May 29, 12:04 pm, Oonz wrote:
>
> > > > Hi Friends,
>
> > > > How can we insert records in sorted order
>
> > > > like consider a table
>
> > > > No Name Phone
> > > > 1 test1 12345
> > > > 1 test1 23455
> > > > 2 test2 68638
> > > > 3 test3 67684
> > > > 4 test4 54808
> > > > 4 test4 74594
>
> > > > if i add a new record like this
>
> > > > 2 test2 34454
>
> > > > it should go in specific order. so that the final table should look
> > > > something like this
>
> > > > No Name Phone
> > > > 1 test1 12345
> > > > 1 test1 23455
> > > > 2 test2 34454
> > > > 2 test2 68638
> > > > 3 test3 67684
> > > > 4 test4 54808
> > > > 4 test4 74594- Hide quoted text -
>
> > > - Show quoted text -
>
> > But actually i want to do further processing with that sorted table.
> > Thats why i am in need of such structure
>
> > Thanks
> > Arunkumar.D- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


Actually i will combine the multiple numbers into one single record

the temporary table structure would be like this

No Name Phone FinalPhone
1 test1 12345 12345
1 test1 23455 12345
23455
2 test2 68638 68638
2 test2 34454 68638
34454

2 test2 45445 68638
34454
45445
3 test3 67684 67684
4 test4 54808 54808
4 test4 74594 54808
74594


and by using GROUP BY clause i would select single record having all
the
information


No Name FinalPhone
1 test1 12345
23455
2 test2 68638
34454
45445
3 test3 67684
4 test4 54808
74594

this view would be sent to UI for display...

Thanks
Arunkumar.D

Re: sorting table while inserting

Oonz wrote:

> Actually i will combine the multiple numbers into one single record
>
> the temporary table structure would be like this
>
> No Name Phone FinalPhone
> 1 test1 12345 12345
> 1 test1 23455 12345
23455
> 2 test2 68638 68638
> 2 test2 34454 68638
34454

> 2 test2 45445 68638
34454
45445
> 3 test3 67684 67684
> 4 test4 54808 54808
> 4 test4 74594 54808
74594

Add a DateInserted column, and use it in sorting.

If you delete the row with Phone = 34454, do you want to have to also
update the row with 45445 so that FinalPhone = 68638
45445? Of
course not - that would be a maintenance nightmare. Move FinalPhone
from the database to the UI layer, or at least from the table to a view.

Re: sorting table while inserting

I created two tables and a trigger and I have all the functionality
that you wanted, without having to create a cursor. The key is to let
the database do the work and this way, any record that validly gets
inserted into the phone_numbers table automatically gets put into the
phone_output list.

If you expect to be deleting or modifying records from the
phone_numbers table then you will need to add update and delete
triggers. (Note: for delete, use the replace function)

If you need more help, just post back.

Cheers,
Jason Lepack

Try this:

-- Begin SQL
CREATE TABLE [dbo].[phone_numbers](
[num] [int] NOT NULL,
[phone_name] [varchar](50) NOT NULL,
[phone] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [nat_key] ON [dbo].[phone_numbers]
(
[num] ASC,
[phone] ASC
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[phone_output](
[num] [int] NOT NULL,
[phone_name] [nvarchar](50) NOT NULL,
[final_phone] [varchar](max) NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[update_final_phone]
ON dbo.phone_numbers
AFTER INSERT
AS
BEGIN
insert into
phone_output(num, phone_name)
select distinct
i.num,
i.phone_name
from
inserted i
left outer join phone_output p
on i.num = p.num
where
p.num is null

update
phone_output
set
final_phone = coalesce(final_phone + '
','') + phone
from
inserted i
where
phone_output.num = i.num
END
GO

insert into phone_numbers (num,phone_name,phone) values
(1,'test1','12345')
insert into phone_numbers (num,phone_name,phone) values
(1,'test1','23455')
insert into phone_numbers (num,phone_name,phone) values
(2,'test2','68638')
insert into phone_numbers (num,phone_name,phone) values
(3,'test3','67684')
insert into phone_numbers (num,phone_name,phone) values
(4,'test4','54808')
insert into phone_numbers (num,phone_name,phone) values
(4,'test4','74594')
go

select * from phone_numbers
select * from phone_output
-- End SQL

On May 29, 12:42 pm, Oonz wrote:
> On May 29, 9:27 pm, Jason Lepack wrote:
>
>
>
>
>
> > What exactly do you want to do? Maybe there is a better way of doing
> > it that you haven't come across.
>
> > Give a little more detail and more help can be given.
>
> > Cheers,
> > Jason Lepack
>
> > On May 29, 12:24 pm, Oonz wrote:
>
> > > On May 29, 9:19 pm, Jason Lepack wrote:
>
> > > > Tables don't store data in a logical order. They store data in a
> > > > physical order. If you want to see data in an ordered fashion then
> > > > create a view to do that. Also, Name is a reserved word, don't use
> > > > it...
>
> > > > SELECT
> > > > No,
> > > > NameField,
> > > > Phone
> > > > FROM
> > > > yourTable
> > > > ORDER BY
> > > > No, NameField, Phone
>
> > > > Cheers,
> > > > Jason Lepack
> > > > On May 29, 12:04 pm, Oonz wrote:
>
> > > > > Hi Friends,
>
> > > > > How can we insert records in sorted order
>
> > > > > like consider a table
>
> > > > > No Name Phone
> > > > > 1 test1 12345
> > > > > 1 test1 23455
> > > > > 2 test2 68638
> > > > > 3 test3 67684
> > > > > 4 test4 54808
> > > > > 4 test4 74594
>
> > > > > if i add a new record like this
>
> > > > > 2 test2 34454
>
> > > > > it should go in specific order. so that the final table should look
> > > > > something like this
>
> > > > > No Name Phone
> > > > > 1 test1 12345
> > > > > 1 test1 23455
> > > > > 2 test2 34454
> > > > > 2 test2 68638
> > > > > 3 test3 67684
> > > > > 4 test4 54808
> > > > > 4 test4 74594- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > But actually i want to do further processing with that sorted table.
> > > Thats why i am in need of such structure
>
> > > Thanks
> > > Arunkumar.D- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> Actually i will combine the multiple numbers into one single record
>
> the temporary table structure would be like this
>
> No Name Phone FinalPhone
> 1 test1 12345 12345
> 1 test1 23455 12345
23455
> 2 test2 68638 68638
> 2 test2 34454 68638
34454

> 2 test2 45445 68638
34454
45445
> 3 test3 67684 67684
> 4 test4 54808 54808
> 4 test4 74594 54808
74594
>
> and by using GROUP BY clause i would select single record having all
> the
> information
>
> No Name FinalPhone
> 1 test1 12345
23455
> 2 test2 68638
34454
45445
> 3 test3 67684
> 4 test4 54808
74594
>
> this view would be sent to UI for display...
>
> Thanks
> Arunkumar.D- Hide quoted text -
>
> - Show quoted text -

Re: sorting table while inserting

Oonz (arundhaj@gmail.com) writes:
> Actually i will combine the multiple numbers into one single record
>
> the temporary table structure would be like this
>
> No Name Phone FinalPhone
> 1 test1 12345 12345
> 1 test1 23455 12345
23455
> 2 test2 68638 68638
> 2 test2 34454 68638
34454

> 2 test2 45445 68638
34454
45445
> 3 test3 67684 67684
> 4 test4 54808 54808
> 4 test4 74594 54808
74594
>
>
> and by using GROUP BY clause i would select single record having all the
> information
>
>
> No Name FinalPhone
> 1 test1 12345
23455
> 2 test2 68638
34454
45445
> 3 test3 67684
> 4 test4 54808
74594
>
> this view would be sent to UI for display...

GROUP BY is not your guy, in this case. See this link for some ideas on
to produce concatenated lists:
http://www.projectdmx.com/tsql/rowconcatenate.aspx


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: sorting table while inserting

I created delete and update triggers... here's the complete ddl.

CREATE TABLE [dbo].[phone_numbers](
[num] [int] NOT NULL,
[phone_name] [varchar](50) NOT NULL,
[phone] [varchar](50) NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE NONCLUSTERED INDEX [nat_key] ON [dbo].[phone_numbers]
(
[num] ASC,
[phone] ASC
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[phone_output](
[num] [int] NOT NULL,
[phone_name] [nvarchar](50) NOT NULL,
[final_phone] [varchar](max) NULL
) ON [PRIMARY]
GO


CREATE TRIGGER [dbo].[insert_final_phone]
ON dbo.phone_numbers
AFTER INSERT
AS
BEGIN
insert into
phone_output(num, phone_name)
select distinct
i.num,
i.phone_name
from
inserted i
left outer join phone_output p
on i.num = p.num
where
p.num is null


update
phone_output
set
final_phone = coalesce(final_phone + '
','') +
phone
from
inserted i
where
phone_output.num = i.num
END
GO

CREATE TRIGGER [dbo].[delete_final_phone]
ON dbo.phone_numbers
AFTER DELETE
AS
BEGIN
update
phone_output
set
final_phone = replace(final_phone, d.phone+'
', '')
from
deleted d
where
phone_output.num = d.num

update
phone_output
set
final_phone = replace(final_phone, d.phone, '')
from
deleted d
where
phone_output.num = d.num

delete phone_output
where final_phone = ''
END
GO

CREATE TRIGGER [dbo].[update_final_phone]
ON dbo.phone_numbers
AFTER UPDATE
AS
BEGIN
update
phone_output
set
final_phone = replace(final_phone, d.phone+'
', '')
from
deleted d
where
phone_output.num = d.num

update
phone_output
set
final_phone = replace(final_phone, d.phone, '')
from
deleted d
where
phone_output.num = d.num

delete phone_output
where final_phone = ''

insert into
phone_output(num, phone_name)
select distinct
i.num,
i.phone_name
from
inserted i
left outer join phone_output p
on i.num = p.num
where
p.num is null


update
phone_output
set
final_phone = coalesce(final_phone + '
','') +
phone
from
inserted i
where
phone_output.num = i.num
END
GO

insert into phone_numbers (num,phone_name,phone) values
(1,'test1','12345')
insert into phone_numbers (num,phone_name,phone) values
(1,'test1','23455')
insert into phone_numbers (num,phone_name,phone) values
(1,'test1','68638')
insert into phone_numbers (num,phone_name,phone) values
(3,'test3','67684')
insert into phone_numbers (num,phone_name,phone) values
(4,'test4','54808')
insert into phone_numbers (num,phone_name,phone) values
(4,'test4','74594')
go

delete phone_numbers where num = 1 and phone = '23455'
delete phone_numbers where num = 3
go

update phone_numbers set num = 2 where phone = '54808'
update phone_numbers set phone = '7686' where phone = '12345'
go



On May 29, 2:01 pm, Jason Lepack wrote:
> I created two tables and a trigger and I have all the functionality
> that you wanted, without having to create a cursor. The key is to let
> the database do the work and this way, any record that validly gets
> inserted into the phone_numbers table automatically gets put into the
> phone_output list.
>
> If you expect to be deleting or modifying records from the
> phone_numbers table then you will need to add update and delete
> triggers. (Note: for delete, use the replace function)
>
> If you need more help, just post back.
>
> Cheers,
> Jason Lepack
>
> Try this:
>
> -- Begin SQL
> CREATE TABLE [dbo].[phone_numbers](
> [num] [int] NOT NULL,
> [phone_name] [varchar](50) NOT NULL,
> [phone] [varchar](50) NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE UNIQUE NONCLUSTERED INDEX [nat_key] ON [dbo].[phone_numbers]
> (
> [num] ASC,
> [phone] ASC
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[phone_output](
> [num] [int] NOT NULL,
> [phone_name] [nvarchar](50) NOT NULL,
> [final_phone] [varchar](max) NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TRIGGER [dbo].[update_final_phone]
> ON dbo.phone_numbers
> AFTER INSERT
> AS
> BEGIN
> insert into
> phone_output(num, phone_name)
> select distinct
> i.num,
> i.phone_name
> from
> inserted i
> left outer join phone_output p
> on i.num = p.num
> where
> p.num is null
>
> update
> phone_output
> set
> final_phone = coalesce(final_phone + '
','') + phone
> from
> inserted i
> where
> phone_output.num = i.num
> END
> GO
>
> insert into phone_numbers (num,phone_name,phone) values
> (1,'test1','12345')
> insert into phone_numbers (num,phone_name,phone) values
> (1,'test1','23455')
> insert into phone_numbers (num,phone_name,phone) values
> (2,'test2','68638')
> insert into phone_numbers (num,phone_name,phone) values
> (3,'test3','67684')
> insert into phone_numbers (num,phone_name,phone) values
> (4,'test4','54808')
> insert into phone_numbers (num,phone_name,phone) values
> (4,'test4','74594')
> go
>
> select * from phone_numbers
> select * from phone_output
> -- End SQL
>
> On May 29, 12:42 pm, Oonz wrote:
>
>
>
> > On May 29, 9:27 pm, Jason Lepack wrote:
>
> > > What exactly do you want to do? Maybe there is a better way of doing
> > > it that you haven't come across.
>
> > > Give a little more detail and more help can be given.
>
> > > Cheers,
> > > Jason Lepack
>
> > > On May 29, 12:24 pm, Oonz wrote:
>
> > > > On May 29, 9:19 pm, Jason Lepack wrote:
>
> > > > > Tables don't store data in a logical order. They store data in a
> > > > > physical order. If you want to see data in an ordered fashion then
> > > > > create a view to do that. Also, Name is a reserved word, don't use
> > > > > it...
>
> > > > > SELECT
> > > > > No,
> > > > > NameField,
> > > > > Phone
> > > > > FROM
> > > > > yourTable
> > > > > ORDER BY
> > > > > No, NameField, Phone
>
> > > > > Cheers,
> > > > > Jason Lepack
> > > > > On May 29, 12:04 pm, Oonz wrote:
>
> > > > > > Hi Friends,
>
> > > > > > How can we insert records in sorted order
>
> > > > > > like consider a table
>
> > > > > > No Name Phone
> > > > > > 1 test1 12345
> > > > > > 1 test1 23455
> > > > > > 2 test2 68638
> > > > > > 3 test3 67684
> > > > > > 4 test4 54808
> > > > > > 4 test4 74594
>
> > > > > > if i add a new record like this
>
> > > > > > 2 test2 34454
>
> > > > > > it should go in specific order. so that the final table should look
> > > > > > something like this
>
> > > > > > No Name Phone
> > > > > > 1 test1 12345
> > > > > > 1 test1 23455
> > > > > > 2 test2 34454
> > > > > > 2 test2 68638
> > > > > > 3 test3 67684
> > > > > > 4 test4 54808
> > > > > > 4 test4 74594- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > But actually i want to do further processing with that sorted table.
> > > > Thats why i am in need of such structure
>
> > > > Thanks
> > > > Arunkumar.D- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -
>
> > Actually i will combine the multiple numbers into one single record
>
> > the temporary table structure would be like this
>
> > No Name Phone FinalPhone
> > 1 test1 12345 12345
> > 1 test1 23455 12345
23455
> > 2 test2 68638 68638
> > 2 test2 34454 68638
34454

> > 2 test2 45445 68638
34454
45445
> > 3 test3 67684 67684
> > 4 test4 54808 54808
> > 4 test4 74594 54808
74594
>
> > and by using GROUP BY clause i would select single record having all
> > the
> > information
>
> > No Name FinalPhone
> > 1 test1 12345
23455
> > 2 test2 68638
34454
45445
> > 3 test3 67684
> > 4 test4 54808
74594
>
> > this view would be sent to UI for display...
>
> > Thanks
> > Arunkumar.D- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -