Identity Insert

  • Comments posted to this topic are about the item Identity Insert

  • Easy one to the end of week 😛

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.

    Anyway, thanks for question.:-)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • kapil_kk (5/2/2013)


    Easy one to the end of week 😛

    +1. But good for testing basics...even if its not a real scenario.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • good one, thank you for the post. 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario.

    Sure it is, if the table contains other columns, all with defaults.

    As a simplistic example:

    CREATE TABLE TableA(ID INT IDENTITY (1,1),

    LastUpdated datetime default (getdate()))

    I don't use this technique often, but have used it in production systems before.

  • Toreador (5/3/2013)


    Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario.

    Sure it is, if the table contains other columns, all with defaults.

    As a simplistic example:

    CREATE TABLE TableA(ID INT IDENTITY (1,1),

    LastUpdated datetime default (getdate()))

    I don't use this technique often, but have used it in production systems before.

    ... what Mr. Ocean meant was to use the defaults with the combination of identity column.

    (the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.

    Anyway, thanks for question.:-)

    A real (especially in old release of sqlserver) scenario is a TALLY table.

    [sql]

    INSERT INTO TableA DEFAULT VALUES

    GO 10000

    [/sql]

    Another way to insert values in Table A is:

    [sql]

    SET IDENTITY_INSERT TableA ON

    INSERT INTO TableA SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY A.object_id) FROM sys.objects A, sys.objects B

    SET IDENTITY_INSERT TableA OFF

    [/sql]

  • I've learned to read the discussions about all the questions because I learn things there as well. Did not know about adding a number to GO to get multiple executions. THANKS!

  • Raghavendra Mudugal (5/3/2013)


    Toreador (5/3/2013)


    Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario.

    Sure it is, if the table contains other columns, all with defaults.

    As a simplistic example:

    CREATE TABLE TableA(ID INT IDENTITY (1,1),

    LastUpdated datetime default (getdate()))

    I don't use this technique often, but have used it in production systems before.

    ... what Mr. Ocean meant was to use the defaults with the combination of identity column.

    (the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)

    nice explanation... But i have another column in this table "Name" this column is not have default properties.

    will this property allow to insert???:w00t:

    Manik
    You cannot get to the top by sitting on your bottom.

  • manik123 (5/3/2013)


    Raghavendra Mudugal (5/3/2013)


    Toreador (5/3/2013)


    Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario.

    Sure it is, if the table contains other columns, all with defaults.

    As a simplistic example:

    CREATE TABLE TableA(ID INT IDENTITY (1,1),

    LastUpdated datetime default (getdate()))

    I don't use this technique often, but have used it in production systems before.

    ... what Mr. Ocean meant was to use the defaults with the combination of identity column.

    (the example what you have given is very nice and we have that set up in the prod server for one database. it actually reduces couple of parameters while inserting - createdate or updating the date)

    nice explanation... But i have another column in this table "Name" this column is not have default properties.

    will this property allow to insert???:w00t:

    Does your NAME column allow null values? (Moved my question to the end where it belonged :-P)

  • Nice question. Thanks.

    Anyone who reads SQLSkills articles written by Paul, sure knows about it. He uses it a lot in his examples.


    Sujeet Singh

  • Danny Ocean (5/2/2013)


    Most of people don't know about it because it's not in use in any real scenario. I never use this kind of table in production environment, But i knew it due to someone ask me about it earlier.

    Anyway, thanks for question.:-)

    Good question!

    Sometime ago I learned this trick when I had to do something similar to what the new SEQUENCE does, but in SQL 2008 (before SQL2012 was released), so I created a dummy table just to generate new ID's exactly like this, with only an IDENTITY column.

    Thanks for the Friday-easy-one!

  • Interesting, nice question...

  • Thanks for the easy one to end the week!



    Everything is awesome!

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply