|
|
Posted by Jason Lepack on 05/29/07 18:01
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 + '<br>','') + 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 <arund...@gmail.com> wrote:
> On May 29, 9:27 pm, Jason Lepack <jlep...@gmail.com> 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 <arund...@gmail.com> wrote:
>
> > > On May 29, 9:19 pm, Jason Lepack <jlep...@gmail.com> 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 <arund...@gmail.com> 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<br> 23455
> 2 test2 68638 68638
> 2 test2 34454 68638<br> 34454<br>
> 2 test2 45445 68638<br> 34454<br> 45445
> 3 test3 67684 67684
> 4 test4 54808 54808
> 4 test4 74594 54808<br> 74594
>
> and by using GROUP BY clause i would select single record having all
> the
> information
>
> No Name FinalPhone
> 1 test1 12345<br> 23455
> 2 test2 68638<br> 34454<br> 45445
> 3 test3 67684
> 4 test4 54808<br> 74594
>
> this view would be sent to UI for display...
>
> Thanks
> Arunkumar.D- Hide quoted text -
>
> - Show quoted text -
Navigation:
[Reply to this message]
|