Professional Web Applications Themes

Insert Identity back into same record? - Microsoft SQL / MS SQL Server

You have 2 databases? In first database you insert records and PREVIOUS id is ID of the recodr previously inserted? In second database you wan't to do what? Be more precise! -- Dean Savovic www.teched.hr "FN" <com> wrote in message news:XvXSa.26583$socal.rr.com... ...

  1. #1

    Default Re: Insert Identity back into same record?

    You have 2 databases?
    In first database you insert records and PREVIOUS id is ID of the recodr previously inserted?
    In second database you wan't to do what?

    Be more precise!

    --
    Dean Savovic
    www.teched.hr


    "FN" <com> wrote in message news:XvXSa.26583$socal.rr.com... 


    Dean Guest

  2. #2

    Default Re: Insert Identity back into same record?

    You're reading too much into it. There's just 1 table, and I'm inserting 1
    record. I can't insert IDENTITY into that record during the insert
    because I don't get it (SELECT myvar = IDENTITY) until after the insert.
    Then, I have to go back and update the record to add it once I get
    IDENTITY. The question is just can I do this all in one step? Can I add
    IDENTITY into a field at the time of the insert?


    "Dean Savovic" <hr> wrote in message
    news:bfir2e$v7p$net4u.hr... 
    previously inserted? 
    news:XvXSa.26583$socal.rr.com... 
    >
    >[/ref]


    FN Guest

  3. #3

    Default Re: Insert Identity back into same record?

    Here's an example of records:


    ID PREVIOUSID
    1234 null
    1512 1234
    1581 1234
    1851 1234


    Sessions 2-4 show that the first time the person was on the site, it was
    under session 1234. Now, if I want to count how many times the person
    visited the site, I can do COUNT(PREVIOUSID) grouped by PREVIOUSID. The
    problem, of course, is when I insert that first 1234, I can't insert a NULL.
    I want to insert:

    1234 1234

    Then my counts and group by statements will work.

    So, how would I do that?

    INSERT INTO WHATEVER
    (field2)
    VALUES
    (????)

    Can I just say "field1" where I wrote ????





    "John Bell" <com> wrote in message
    news:3f1d8b46$0$15038$news.pipex.net... [/ref]
    inserting 
    > insert. 
    > add [/ref][/ref]
    recodr 
    > > news:XvXSa.26583$socal.rr.com... [/ref]
    > sessions [/ref][/ref]
    want [/ref][/ref]
    of [/ref]
    > and 
    > >
    > >[/ref]
    >
    >[/ref]


    FN Guest

  4. #4

    Default Re: Insert Identity back into same record?

    Hi

    I seems that the ID is being used as a UserId type field. In which case I
    would expect there to be two tables one for users and one for sessions.
    There will always be a check for the user existing and that will get their
    ID, if the user does not exist SCOPE_IDENTITY() (or IDENTITY for earlier
    versions) would be used to for the ID field.

    If you stick with this design then your select statement could use:

    CREATE TABLE #Test ( ID INT NOT NULL, PREVIOUS_ID INT )

    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 1, NULL )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 2, 1 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 3, 1 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 3, 1 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 5, NULL )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 6, NULL )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 7, 6 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 8, 6 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 9, 6 )
    INSERT INTO #Test ( ID , PREVIOUS_ID ) VALUES ( 10, 6 )

    SELECT ISNULL(PREVIOUS_ID,ID), COUNT(ISNULL(PREVIOUS_ID,ID))
    FROM #Test
    GROUP BY ISNULL(PREVIOUS_ID,ID)

    DROP TABLE #Test

    John

    "FN" <com> wrote in message
    news:7rkTa.1564$socal.rr.com... 
    NULL. [/ref]
    by [/ref]
    > inserting 
    > > insert. [/ref][/ref]
    I [/ref]
    > recodr 
    > > sessions [/ref]
    > want [/ref]
    > of [/ref][/ref]
    identity, 
    > >
    > >[/ref]
    >
    >[/ref]


    John Guest

  5. #5

    Default Re: Insert Identity back into same record?

    If you have this logic of session auditing then use the following to see how many times the user has visited the site:

    create table sessions
    (id int not null primary key
    , previousID int)

    insert into sessions
    select 1234, null
    union
    select 1235, 1234
    union
    select 1236,1234
    union
    select 1237, 1234


    select count(previousID) + 1
    from sessions
    where previousID = 1234

    drop table sessions

    I think that it would not be right to have in record that records the first visit ID equal to previousID, because in that moment
    there are no previous ID's.

    --
    Dean Savovic
    www.teched.hr


    "FN" <com> wrote in message news:7rkTa.1564$socal.rr.com... [/ref]
    > inserting 
    > > insert. 
    > > add [/ref]
    > recodr 
    > > sessions [/ref]
    > want [/ref]
    > of 
    > > and 
    > >
    > >[/ref]
    >
    >[/ref]


    Dean Guest

  6. #6

    Default Re: Insert Identity back into same record?

    Thanks, but is there a way to do the insert in one step if I want to, where
    "previousid" is not left as Null?

    Your count works as you said (+1), but I actually have many queries to write
    (e.g. Select * from Table where PreviousID = 1234), and I will need a
    solution to the original question (the first sentence above).



    "Dean Savovic" <hr> wrote in message
    news:bflf9m$8pn$net4u.hr... 
    how many times the user has visited the site: 
    first visit ID equal to previousID, because in that moment 
    news:7rkTa.1564$socal.rr.com... [/ref]
    The [/ref]
    NULL. [/ref][/ref]
    populated by [/ref][/ref]
    computed 
    > > inserting [/ref][/ref]
    insert [/ref][/ref]
    Can I 
    > > recodr 
    > > want [/ref][/ref]
    instead [/ref][/ref]
    identity, 
    > >
    > >[/ref]
    >
    >[/ref]


    FN Guest

Similar Threads

  1. retrieving last record inserted using IDENTITY - ASPJScript
    By Fabiano Arruda in forum Dreamweaver AppDev
    Replies: 4
    Last Post: May 11th, 05:54 PM
  2. Identity from inserted record
    By Millsaps in forum Dreamweaver AppDev
    Replies: 4
    Last Post: April 15th, 09:19 AM
  3. How to insert a new record
    By European in forum ASP.NET Building Controls
    Replies: 0
    Last Post: December 1st, 03:39 AM
  4. Match an Identity to an Active Directory Record.
    By Todd Thompson in forum ASP.NET Security
    Replies: 0
    Last Post: July 11th, 09:05 PM
  5. Problem with IDENTITY and ORDER BY in a INSERT statement
    By Steve Kass in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 06:26 AM

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