Serial Number Generation In Select List

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Serial Number Generation In Select List

Author

Topic

ChaitanyaPottam
Starting Member

3 Posts

Posted - 2012-03-13 : 08:55:06

hi guys i need a help.. i have mentioned my requirement below in this message.

Table A
----------
ID SlNo Name
-- ----- --------
1 1 Abcdf

Table B
----------

ID ParentID SlNo Name
-- ----------- ------ -------
1 1 1 Asdew
2 1 2 Wqwer
3 1 3 Oirtwer

So when I duplicate Table A then all the records in Table B should be duplicated with SlNo starting with 4.

So my question is can we do this with out using the cursor in sql server.

Below this is how the data should look in Table A And Table B, after duplicating the first record of Table A.

Note:

1) i cant use identity column as i have used uniqueidentifier as my primary key. i can't change the existing data.

2) this is only one level deep. But i have 3 level deep.

3) i m looking whether it's possible with a simple select statement.

Table A
----------
ID SlNo Name
-- ----- --------
1 1 Abcdf
2 2 Abcdf

Table B
----------

ID ParentID SlNo Name
-- ----------- ----- -------
1 1 1 Asdew
2 1 2 Wqwer
3 1 3 Oirtwer
4 2 4 Asdew
5 2 5 Wqwer
6 2 6 Oirtwer

ChaitanyaPottam
Starting Member

3 Posts

Posted - 2012-03-15 : 02:53:55

hi guys thanks for looking into the query..

finally got the answer..in a single select statement

insert into TableB(ParentID, SlNo, Name)
select 2, row_number() over(order by id) +
(select max(TB.slno) from TableB), TB.Name
from TableB TB join TableA TA
on TB.ParentID = TA.ID where TB.ParentID = 1;

ChaitanyaPottam
Starting Member

3 Posts

Posted - 2012-03-15 : 02:55:27

hey guys..if u find any other solution then please let me know or post u r answer as reply

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-15 : 06:57:11

I think you want to look at the IDENTITY property on the tables

This autonumbers the column in question.

If you had an IDENTITY property for the [ID] columns then you could make your SQL *very* simple

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources