Professional Web Applications Themes

auto increment without using a identity column - Microsoft SQL / MS SQL Server

Hello! I have a little problem, maybe someone can help me out? I want to use the following table: CREATE TABLE [dbo].[Study] ( [study_id] [int] NOT NULL , .... .... [study_date_start_registration] [datetime] NULL , [study_date_end_registration] [datetime] NULL ) ON [PRIMARY] I use the fields study_date_start and study_date_end to determine what is the active record. In this way I can always look back to what the situation was f.e. 5 months ago. Here is the problem: In fact, study_id is a primary key, but it is possible that there are more records with the same key. When something changes in the ...

  1. #1

    Default auto increment without using a identity column

    Hello!

    I have a little problem, maybe someone can help me out?
    I want to use the following table:

    CREATE TABLE [dbo].[Study] (
    [study_id] [int] NOT NULL ,
    ....
    ....
    [study_date_start_registration] [datetime] NULL ,
    [study_date_end_registration] [datetime] NULL
    ) ON [PRIMARY]

    I use the fields study_date_start and study_date_end to determine what is
    the active record. In this way I can always look back to what the situation
    was f.e. 5 months ago.
    Here is the problem: In fact, study_id is a primary key, but it is possible
    that there are more records with the same key. When something changes in the
    study table, the actual record is closed, and a new one created with a new
    'start_registration'.
    Because the id is not unique, I can't set the IDENTITY property to study_id,
    but I want to add a large amount of (new) records to the study table. I have
    written a function (nextkey_study) to determine what the new id has to be:
    select MAX(studie_id)+1 from studie
    but when I want to insert a large amount of records with this function, the
    same value is returned for every record.
    insert into studie
    select nextkey_studie, bla, bla, bla, ....., getdate(), null
    from anothertable

    nextkey is always 1.

    Is there any function I can use so this will work? I don't want to add the
    records one by one: this will take to much time.

    Thanks!

    Kind regards,

    Rob

    (when replying to me personaly, please remove the antispam part in the
    emaladdress)




    Janssen Guest

  2. #2

    Default Re: auto increment without using a identity column

    Hi!

    I think you would win in the long range by rearranging your database
    completely :/ (sorry)

    Make two tables for the studies instead, having one with the study
    description and other static data, and one with each instance of a study.
    IE. You can set up a study called "Math" and run in five times.
    You would have a "master record" with StudyID 1 and StudyDescription "Math"
    Then you would have a couple of child records like this:
    StudyInstanceID 1, StudyID 1, Startdate 18.8.03
    StudyInstanceID 2, StudyID 1, Startdate 15.9.03
    StudyInstanceID 3, StudyID 1, Startdate 10.10.03

    With this structure, you could fetch all history for the math study by
    fetching all StudyInstances with StudyID 1, and still have a unique id for
    each session. Also, connect attendees etc. to the "instances" instead of the
    study itself..

    GL!
    Lars-Erik

    Here's the DDT:

    CREATE TABLE [dbo].[Study] (
    [study_id] int IDENTITY (1, 1) NOT NULL,
    [study_name] varchar(255) NOT NULL
    )

    CREATE TABLE [dbo].[StudyInstances] (
    StudyInstanceID int IDENTITY (1, 1) NOT NULL,
    StudyID int NOT NULL,
    StudyInstanceStart datetime NOT NULL,
    StudyInstanceEnd datetime NOT NULL,
    CONSTRAINT [FK_StudyInstances_Study] FOREIGN KEY
    (
    [StudyID]
    ) REFERENCES [Study] (
    [StudyID]
    )
    )


    "Janssen" <com> wrote in message
    news:bhqbj7$22407$news.uni-berlin.de... 
    situation 
    possible 
    the 
    study_id, 
    have 
    the 


    Lars-Erik Guest

  3. #3

    Default Re: auto increment without using a identity column

    Thanks for your answer! That makes sence....but the situation is different:
    I have already a relation in this table. A student can follow a course (f.e.
    math). This is saved in a table opleiding (dutch for course). When a student
    follows a course, in the study table some data is saved as: startdate of the
    course, wich class, reason why a student left the study, etc. I can apply
    your solution to this table, but I don't think it will work for me,
    considering the queries I have to make on this table in the future.

    Isn't there an onther way to determine the key using my table?

    Regards,

    Rob



    "Lars-Erik Aabech" <no> schreef in bericht
    news:phx.gbl... 
    "Math" 
    the [/ref]
    is 
    > situation 
    > possible 
    > the [/ref]
    new 
    > study_id, 
    > have [/ref]
    be: 
    > the [/ref]
    the 
    >
    >[/ref]


    Janssen Guest

  4. #4

    Default Re: auto increment without using a identity column

    Thant's an option, but it takes a 'long' time to insert all the records.

    Rob

    "Murray Bryant" <murray.bryantharmonygolddotcomdotau> schreef in bericht
    news:.. [/ref]
    is [/ref]
    situation [/ref]
    possible [/ref]
    the [/ref]
    new [/ref]
    study_id, [/ref]
    have [/ref]
    be: [/ref]
    the [/ref]
    the 
    > Rob
    >
    > Could you not create a trigger that when you insert a number of records
    > it creates a cursor on the inserted values steps throught them one by
    > one and increments the new id of each?
    >
    > Murray
    >[/ref]


    Janssen Guest

Similar Threads

  1. auto-increment
    By tony in forum MySQL
    Replies: 2
    Last Post: October 6th, 05:17 PM
  2. Auto Increment increase by 100 at a time?
    By Gary@garywhittle.co.uk in forum MySQL
    Replies: 2
    Last Post: November 23rd, 04:50 PM
  3. Auto-increment numbers and letters possible?
    By Justin in forum PHP Development
    Replies: 7
    Last Post: June 24th, 03:45 PM
  4. id after auto increment from a form
    By Faith in forum ASP
    Replies: 7
    Last Post: August 6th, 05:18 PM
  5. Auto Increment a number field
    By jaycee in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 11:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139