Professional Web Applications Themes

accessing current written record - Microsoft SQL / MS SQL Server

See IDENTITY and SCOPE_IDENTITY( ) in SQL Server Books Online. -- HTH, Vyas, MVP (SQL Server) [url]http://vyaskn.tripod.com/[/url] What hardware is your SQL Server running on? [url]http://vyaskn.tripod.com/poll.htm[/url] "James Hall" <halljhdshs.wa.gov> wrote in message news:014301c33f3a$5cee73d0$a501280aphx.gbl... I am working on a multiple table insert. My master table has an id field that is an identity field. Before writing records to the related tables, I need to find out what the id is that was just created. I seem to remember being able to get to it by accessing a system cache - probably tempdb. I can't find my example - anybody know how ...

  1. #1

    Default Re: accessing current written record

    See IDENTITY and SCOPE_IDENTITY( ) in SQL Server Books Online.

    --
    HTH,
    Vyas, MVP (SQL Server)
    [url]http://vyaskn.tripod.com/[/url]
    What hardware is your SQL Server running on?
    [url]http://vyaskn.tripod.com/poll.htm[/url]




    "James Hall" <halljhdshs.wa.gov> wrote in message
    news:014301c33f3a$5cee73d0$a501280aphx.gbl...
    I am working on a multiple table insert. My master table
    has an id field that is an identity field. Before writing
    records to the related tables, I need to find out what the
    id is that was just created. I seem to remember being able
    to get to it by accessing a system cache - probably
    tempdb. I can't find my example - anybody know how to do
    this?

    Thank you,
    Jim.


    Narayana Vyas Kondreddi Guest

  2. #2

    Default Re: accessing current written record

    If you are performing a single insert you can use the SCOPE_IDENTITY()
    function to retrun the last identity value inserted within the same scope
    (proc, etc.)

    Ex:
    declare ident int

    INSERT ...

    set ident = SCOPE_IDENTITY()

    --Drew


    "James Hall" <halljhdshs.wa.gov> wrote in message
    news:014301c33f3a$5cee73d0$a501280aphx.gbl...
    > I am working on a multiple table insert. My master table
    > has an id field that is an identity field. Before writing
    > records to the related tables, I need to find out what the
    > id is that was just created. I seem to remember being able
    > to get to it by accessing a system cache - probably
    > tempdb. I can't find my example - anybody know how to do
    > this?
    >
    > Thank you,
    > Jim.

    Drew Seale Guest

  3. #3

    Default Re: accessing current written record

    DECLARE id INT
    INSERT tbl(col_list) VALUES(val_list)
    SELECT id = IDENTITY
    PRINT id


    If using SQL Server 2000, replace IDENTITY with SCOPE_IDENTITY()

    (It's always helpful to post the version of SQL Server you're using; the
    solution can be different.)




    "James Hall" <halljhdshs.wa.gov> wrote in message
    news:014301c33f3a$5cee73d0$a501280aphx.gbl...
    > I am working on a multiple table insert. My master table
    > has an id field that is an identity field. Before writing
    > records to the related tables, I need to find out what the
    > id is that was just created. I seem to remember being able
    > to get to it by accessing a system cache - probably
    > tempdb. I can't find my example - anybody know how to do
    > this?
    >
    > Thank you,
    > Jim.

    Aaron Bertrand - MVP Guest

Similar Threads

  1. Accessing the current DataRow of the DataSource from ItemDataBound
    By Nathan Sokalski in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: September 25th, 02:10 PM
  2. Replies: 3
    Last Post: March 9th, 05:19 PM
  3. Accessing current user in Managed C++ Web Service?
    By Sharon McCarty in forum ASP.NET Web Services
    Replies: 3
    Last Post: November 9th, 01:04 AM
  4. Accessing the current datasource row and column?
    By Christian in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 2nd, 08:33 AM
  5. Augh! EOF/BOF/non-current record
    By stewboy webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 22nd, 08:00 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