RESEED in order

[ddeegan]

Hello

I am doing a data migration project from SQL to SQL

The new table has an identity column that must be started with a specific value.

Using identity(int,1,1) and DBCC CHECKIDENT(TableName, RESEED, @mynewid), I can populate the table - BUT

Is there a way that I can control the order that the identity column is populated?

The old table has a field called "LINE_NO" that keeps the records in chronological order - but it doesn't match the order of the new identity column ( the column created with identity() ).

Doing this in the interface is another set of headaches so I was hoping I could store the data in a certain order. (The data will be read only) The interface displays it in the order of the identity column. But I can't seem to figure out how to set the order of the identity column.

Thanks
Dave

 

[jbenson001]

what does the line_no column's data look like? Integer? Does it change on a certain grouping?

 

[druer]

Have you thought creating the new table format and populating it via a select command from the old table where you could use the ORDER BY and the order you want it?

 

[ddeegan]

Hello

jbenson001 -
The line_no is varchar - it is changed for each customer. So it would be

Custno | LINE_NO
123 | 1
123 | 2
123 | 4
456 | 1
456 | 2
456 | 3

druer -
I'm populating the first table with

before I call reseed, I put it in my first table with a statement similar to...

SELECT
custno,
line_no,
[text] ,
identity(int,1,1) as seqnum
into USR_IMPORTTEMP_TABLE
from customerhp c join scratch_pad s on
c.[ACCT#]=s.[ACCT#]
order by 1, 2, 4

then calling RESEED...

DBCC CHECKIDENT(USR_IMPORTTEMP_TABLE, RESEED, @mynewid)
go
- @mynewid is the # that I have to start with


insert into FINAL_TABLE
select
custno, seqnum, line_no...
from
USR_IMPORTTEMP_TABLE
order by custno, seqnum, line_no

The interface will filter by custno and sort by seqnum


Thanks for pointing out where I am doing it wrong or any suggestions - in advance

 

[jbenson001]

an identity column will be unique, from what i see you want to do is to restart the sequence on each new group, correct? If so, you cannot use an identity column.

 

[ddeegan]

No, thats not it, the data would look
like this

Custno | LINE_NO | SEQUENCE_NO
123 | 1 | 456
123 | 2 | 457
123 | 3 | 458
456 | 1 | 459
456 | 2 | 460
456 | 3 | 461

but the way it comes out is like this

Custno | LINE_NO | SEQUENCE_NO
123 | 2 | 456
123 | 1 | 457
123 | 3 | 458
456 | 2 | 459
456 | 1 | 460
456 | 3 | 461

I want the LINE_NO to appear in order. SEQUENCE_NO doesn't get displayed to the user. When I use RESEED, I want it to assign the sequence # according to the order of the LINE_NO.

Is this possible?

Thanks
Dave

 

[jbenson001]

Yes, what you are saying is the line_no will restart at 1 each time the custno changes. So, in that case you will not be able to use an identity column. Unless you are referring to the sequence_no being the identity column.

 

[druer]

I'm not sure why:

Create table RealTable( cust_num, line_num, identity_field)

insert into realtable
select cust_num, line_num
from old_table
order by cust_num, line_num

won't give you exactly what you want. The data going into the new table would be in exactly the order you want it to be, and would get the identity stamp in the correct order. And you would never need the reseed. I think you are complicating the issue beyond what is necessary.

Dalton

 

[SQLDenis]

here we go

create table #testid (id int,id2 int,id3 int)
insert into #testid
select 123,  2 , 456 union all
select 123 , 1 , 457 union all
select 123 , 3 , 458 union all
select 456 , 2 , 459 union all
select 456 , 1 , 460 union all
select 456 , 3 , 461

--test
select t1.*,id3-minid +1 from (
select id,min(id3) as MinID from #testid
group by id) t2 join #testid t1 on t2.id=t1.id

update t1 
set id2 =id3-minid +1
from (
select id,min(id3) as MinID from #testid
group by id) t2 join #testid t1 on t2.id=t1.id

select * from #testid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

Right - "the sequence_no being the identity column." thats what I meant.

LINE_NO is from the old data - I want to sort by LINE_NO - it already has data, I'm not repopulating it.

Sorry about that, its hard to make clear

 

[SQLDenis]

druer

this is not guaranteed to be in order

insert into realtable
select cust_num, line_num
from old_table
order by cust_num, line_num

even though it is specified ;-(

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Denis,

Why wouldn't druer's example work? It looks like it should.

Jim

 

[druer]

Denis,

I know enough to believe you, and your ;-( indicates that you understand my confusion, puzzlement and bewilderment.

Thanks,
Dalton

 

[vongrunt]

line_no is varchar, right? Probably not right-aligned and therefore not sortable as string.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[druer]

I didn't even think about the varchar aspect. But if that is the only reason, then the order by could just be done as Cust_Num, Convert(int, Line_Num) so it would sort correctly. (Right?)

You had me worried that somehow if sorted data goes into a table as a single insert like that the server didn't guarantee that the first line would actually get into the table first or something.

 

[vongrunt]

Hm...

Run this code (warning: 2 million sample rows):

-- use crap

create table blah (oldid int)
insert into blah values (1)
while @@rowcount < 1e6
	insert into blah (oldid) select (select count(*) from blah) + oldid from blah

select oldid, identity(int, 1, 1) as id
into blah2
from blah
order by oldid

select count(*) as mismatches
from blah2
where oldid <> id

-- drop table blah, blah2

I got:

- 0 on Athlon64/3200+ machine (1CPU)
- 2097124 on Xeon 3.06 rack (1CPU, hyperthreading on).

Both SQL2000 SP4.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

0 on sp3 on P4 with hyperthreading on

(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(4 row(s) affected)
(8 row(s) affected)
(16 row(s) affected)
(32 row(s) affected)
(64 row(s) affected)
(128 row(s) affected)
(256 row(s) affected)
(512 row(s) affected)
(1024 row(s) affected)
(2048 row(s) affected)
(4096 row(s) affected)
(8192 row(s) affected)
(16384 row(s) affected)
(32768 row(s) affected)
(65536 row(s) affected)
(131072 row(s) affected)
(262144 row(s) affected)
(524288 row(s) affected)
(1048576 row(s) affected)
(2097152 row(s) affected)
(1 row(s) affected)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Um... interesting. Can you post exec plan (in textual form) for SELECT INTO statement only?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

  |--Table Insert(OBJECT:([blah2]), SET:([blah2].[id]=[Expr1003], [blah2].[oldid]=[blah].[oldid]))
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1003]=setidentity([Expr1002], -7, 0, 'blah2')))
                 |--Compute Scalar(DEFINE:([Expr1002]=getidentity(-7, 0, 'blah2')))
                      |--Parallelism(Gather Streams, ORDER BY:([blah].[oldid] ASC))
                           |--Sort(ORDER BY:([blah].[oldid] ASC))
                                |--Table Scan(OBJECT:([tempdb].[dbo].[blah]))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Here is what I got (Xeon box):

  |--Table Insert(OBJECT:([blah2]), SET:([blah2].[id]=[Expr1003], [blah2].[oldid]=[blah].[oldid]))
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1003]=setidentity([Expr1002], -7, 0, 'blah2')))
                 |--Parallelism(Gather Streams, ORDER BY:([blah].[oldid] ASC))
                      |--Sort(ORDER BY:([blah].[oldid] ASC))
                           |--Compute Scalar(DEFINE:([Expr1002]=getidentity(-7, 0, 'blah2')))
                                |--Table Scan(OBJECT:([crap].[dbo].[blah]))

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]