Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

I am migrating data from a legacy system into this table and there is a requirement that Collection and Delivery notes have their own sequential numbers.

I had previously implemented two sequence tables as

CREATE TABLE CollectionNoteSequence (
    Id int IDENTITY(1,1) NOT NULL,
    NoteId int NOT NULL
)

where the Id column is the unique, sequential Id for collection notes, and then the NoteId foreign key's to Notes.Id.

It's getting towards time to do the final (real) data migration and this setup seems hard to work with.

Is there a way I could bin the two sequence tables and add a NoteNo field to the Notes table such that NoteNo would be sequential depending on the Note.Type ? Is this a composite key or something?

The new table might look like

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   NoteNo int NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

and the data would look like:

Id    NoteNo    Type    CustomerId
1     4000      1       123
2     4001      1       456
3     15123     2       789
4     4002      1       753
5     15124     2       741

I'm using MS SQL Server 2008.

share|improve this question
    
FYI, SQL Server 2012 supports sequences natively... –  gbn Mar 26 '12 at 7:49
add comment

2 Answers

up vote 4 down vote accepted

Very simple answer...

Why not 2 separate tables? And UNION as needed or in a view when required combined? As I see it, a "Collection Note" and a "Delivery Note" are separate entities. At some point, the definition of each will be different. Are they really the same now anyway: I'd expect some differences?

Also, SQL Server 2012 support SEQUENCE natively too

share|improve this answer
    
Thinking about it, maybe two tables is the way to go. and thanks for the heads-up re: SEQUENCE –  Greg B Mar 26 '12 at 8:03
add comment

You could use partition by on the note type, which allows a calculation to be reset by groups. For example:

create table OldNotes (
       OldNoteRef           varchar (20) not null
      ,OldNoteType          varchar (20)
      -- Other data goes here
)
go

insert OldNotes (OldNoteRef, OldNoteType) values ('CABC123', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC456', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC789', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ001', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ034', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('XZYZ100', 'D')
go


select OldNoteRef
      ,OldNoteType
      ,row_number() over (
          partition by OldNoteType
          order by OldNoteRef
      ) as NewNoteID
  from OldNotes
go

Would produce the following results, which will give you a sequence within the note type.

╔════════════╦═════════════╦═══════════╗
║ OldNoteRef ║ OldNoteType ║ NewNoteID ║
╠════════════╬═════════════╬═══════════╣
║ CABC123    ║ C           ║         1 ║
║ CABC456    ║ C           ║         2 ║
║ CABC789    ║ C           ║         3 ║
║ DXYZ001    ║ D           ║         1 ║
║ DXYZ034    ║ D           ║         2 ║
║ XZYZ100    ║ D           ║         3 ║
╚════════════╩═════════════╩═══════════╝

BTW, table produced by http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

share|improve this answer
    
So I would use the partition by in my INSERT statement when migrating the data from the old database to the new one? –  Greg B Mar 25 '12 at 10:36
    
The select statement with the partition by would give you the sequence numbers; you could then insert from the output of that. –  ConcernedOfTunbridgeWells Mar 25 '12 at 11:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.