locked
Regarding INSERT DEFAULT 10000 RECORDS AT ONCE RRS feed

  • Question

  • User264732274 posted

    check this url first http://www.webcodeexpert.com/2015/08/auto-generate-auto-incremented-unique.html

    i try their code in my sql server2012 but encounter error

     CREATE TABLE #Temp
    (
                    Id INT NOT NULL IDENTITY(1,1),
                    AutoId AS 'ABC' + RIGHT('0000'+ CONVERT(VARCHAR(5),Id),5)
    )
    --INSERT DEFAULT 10000 RECORDS AT ONCE
    INSERT INTO #Temp DEFAULT VALUES;
    GO 10000
    
    --CHECK INSERTED RECORD
    SELECT * FROM #Temp

    how GO 10000 can insert 1000 records in temp table ?

    what does it means AutoId AS 'ABC' ?

    Friday, September 23, 2016 11:52 AM

Answers

  • User753101303 posted

    Hi,

    Column AS Expression is to define a "computed column". See the Transact SQL doc for details.

    GO x just does a loop to run what is in the batch x times (ie it should run 10000 times or do you mean it inserted 1000 rows when you expected 10000 rows ?). My understanding is rather that you wondered how it could insert 10000 rows without seeing an explicit loop ?

    sudip_inn

    but encounter error

    Please NEVER post about an error without telling which error you have (or the best English translation you can or a description of the unexpected behavior you see). To me it should work though past 100000 it might starts to not behave as expected (having always 00000 ?)

    Edit: gave a quick try. Not with that much rows. It seems to work. I tried CONVERT(VARCHAR(2),100) and to my surprise it seems to return a * character.

    So what do you see? Does it generate ABC* values because your id is >100000

    As I said I didn't know about that as I'm always try to convert as less as I can and if I do to ensure I always have enough room for the expected values.

    Edit 2 :

    if using what you shown you may end up to try to create the table again and again. You should have another GO just before the INSERT statement so that only this INSERT statement is repeated 10000 times.  And it seems to miss from the article as well.

    As you see even with few line of code we can find at least 2 things that could produce an error or at least an unexpected result and I have no idea which one you have (and you could even have some other error caused by something not shown in the code etc...)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 15, 2016 5:54 PM