Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: incremtals i.e. nextvalue in SQLServer

    Hi I'm trying to write a script for SQL server and having a bit of trouble.

    I'm rewriting a script that was originally wrote for Oracle and part of this script relys on the sequence.nextvalue concept. Since I know there is no concept of sequence next value in SQL Server I was wondering if anyone had any other suggestions?

    Here is a "rough" snippet of script

    Code:
    CREATE TABLE TBLNEW(
    ID	DECIMAL(5,0) NOT NULL
    ,uniqueID	DECIMAL(10,0) NOT NULL
    ,one		VARCHAR(2) NULL
    ,TWO DATETIME NULL
    ,Three DECIMAL(10,0) DEFAULT 0 
    ,CONSTRAINT pk_TBLORIGINAL PRIMARY KEY (I,C));
    GO
    
    
    CREATE TABLE SBLORIGINAL(
    I DECIMAL(10,0) NOT NULL IDENTITY(1,1));
    
    -------------------------------------------------------------------------------
    -- Migrate TBLORIGINAL into TBLNEW
    -------------------------------------------------------------------------------
    insert into TBLNEW(
    id
    ,uniqueID
    ,one
    ,two
    ,three
    )
    select
    11112222
    ,SBLORIGINAL.nextval   -- HERE IS THE PROBLEM!!
    ,one
    ,two
    ,three
    From TBLORIGINAL;
    go
    I'm creating the new table creating the sequence table? then trying to copy from one table to another. I have to have an incremental unique ID because other table will be migrated to the new table.

    Any suggestions would be great. or if I haven't explained this well then just shout.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you considered using an IDENTITY column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL server uses IDENTITY values rather than sequences. And Identity values are MUCH easier to use.
    If you need to preserver the values of the data you are importing, then check out the SET IDENTITYINSERT command.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2006
    Posts
    13
    Code:
    -------------------------------------------------------------------------------
    -- Migrate TBLORIGINAL into TBLNEW
    -------------------------------------------------------------------------------
    DECLARE @v_I_WorkPattern			DECIMAL(10,0);   
    
    INSERT INTO SBLORIGINAL DEFAULT VALUES SET @v_I_WorkPattern = (SELECT @@IDENTITY AS 'ident');
    
    insert into TBLNEW(
    id
    ,uniqueID
    ,one
    ,two
    ,three
    )
    select
    11112222
    , @v_I_WorkPattern,
    ,(INSERT INTO SOLPaymentDetailsForInt DEFAULT VALUES SET @v_I_WorkPattern = (SELECT @@IDENTITY AS 'ident'));
    ,one
    ,two
    ,three
    From TBLORIGINAL;
    go
    Ok the above is a guess at what might be the right road? I still get the feeling I'm going to have to use a loop to step through all the row's in the database incrementing the sequence/identity every time?

    Could be going very wrong here?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you will need to use a loop or a cursor to step through all the records and increment the values one at a time. This is because you have not bothered to read any of the documentation regarding IDENTITY values.
    DBAs who HAVE bothered to read Books Online are not required to use cursors or loops. They just define the column as an IDENTITY and move on.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2006
    Posts
    13
    Quote Originally Posted by blindman
    Yes, you will need to use a loop or a cursor to step through all the records and increment the values one at a time. This is because you have not bothered to read any of the documentation regarding IDENTITY values.
    DBAs who HAVE bothered to read Books On-line are not required to use cursors or loops. They just define the column as an IDENTITY and move on.
    Blindman, I asked a question in a forum. trust me asking in these places are always the LAST port of call when I'm stuck. Maybe if I was a DBA I wouldn't have to ask in these place's and put up with smart remarks from people like your self, but since I have maybe you should consider cutting people a bit of slack as maybe all people aren't up to your standards.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You were already advised to use IDENTITY columns.

    Books Online clearly explains how to use IDENTITY columns and the @@IDENTITY variable, and it is obvious that you did not bother to read either before you plunged into your coding. So it is equally obvious that asking you question on the forum was NOT your "last port of call".

    The very first line of the section on @@IDENTITY states: "Returns the last-inserted identity value.", so why you would think you could use it as you do is beyond me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have any column or columns that will guaranteedly pick exactly one row from your existing data? For instance a sequence number, a unique date and time, or something similar?

    If not, can you add a column to your existing data (or a copy of that data) before building your new table?

    There are lots of ways to solve this kind of problem, but without knowing a bunch more about your specific application it is hard for any of us to suggest a single, sinple solution.

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I have been informed by more patient persons than myself that I need go easier on you.

    So....
    IDENTITY is a property of a column in a table. Once you define a column as an identity, you can no longer insert data directly into it. Instead, SQL Server automatically populates the column with sequential values as data is inserted.

    @@IDENTITY is used within code to determine exactly what value was last assigned by the database engine, should you actually need to know that in your code.

    Again, Books Online has plenty of information regarding these subjects.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2006
    Posts
    13
    Quote Originally Posted by Pat Phelan
    Do you have any column or columns that will guaranteedly pick exactly one row from your existing data? For instance a sequence number, a unique date and time, or something similar?

    If not, can you add a column to your existing data (or a copy of that data) before building your new table?

    There are lots of ways to solve this kind of problem, but without knowing a bunch more about your specific application it is hard for any of us to suggest a single, sinple solution.

    -PatP
    Thanks for the input pat, after looking into using @@INDENTITY concept, I decided to use this along with looping through the table (this is what I was trying to avoid as coming from a DB2 background this is a little bit more cumbersome in DB2 than in SQL Server). this seems to be working and doing exactly what I need.

    Thanks again all for your usefull suggestions.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I am 90% sure you do not need to loop through your table.

    Post your latest code, and I'd bet we can find a more efficient solution for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    OK, I have been informed by more patient persons than myself that I need go easier on you.

    OK, I take exception then. If you ask a question, and ignore advise, why should you get your panties in a bunch.

    For example, did you even look at the code I posted? Did you do that much leg work?

    And you say you have a DB2 background? What version, because I've been doing that sheet since 2.3

    Becuase the link that I posted was the only way to do what you wanted to do back then

    So stop giving people grief, and Pat I gotta disagree with you on this one (OMG, I defending the blind dude)

    What's worse, pushing in the right direction, or you totally ignoring the advice?

    Here

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99 (Col1 char(1))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
    
    SELECT * FROM myTable99
    GO
    
    CREATE TABLE myNewTable99([id] int IDENTITY, Col1 char(1))
    GO
    
    INSERT INTO myNewTable99(Col1)
    SELECT Col1 FROM myTable99 ORDER BY Col1
    
    SELECT * FROM myNewTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99, myNewTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    So stop giving people grief, and Pat I gotta disagree with you on this one (OMG, I defending the blind dude)
    Um. Thanks. I think.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you get an answer and then just dismiss it, then you must have something that you have in mind...if you can't think in set based methods, that's not our fault
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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