Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Unnecessarily split query?

    I am using SQL Server 2008

    Using the following two queries I select the maximum + 1 for the Order field and insert a new record using that value.

    Code:
    SELECT CASE WHEN MAX(DBO.SelCons.[Order]) + 1 IS NULL THEN 1 ELSE MAX(DBO.SelCons.[Order]) + 1 END AS OrderNo 
    FROM  DBO.SelCons RIGHT OUTER JOIN DBO.LimitedVars ON DBO.SelCons.TestName = DBO.LimitedVars.TestName 
    
    INSERT INTO DBO.SelCons 
    SELECT DBO.LimitedVars.TestName, [using runtime code I put the value returned by the first query here]  
    FROM DBO.SelCons RIGHT JOIN DBO.LimitedVars ON DBO.SelCons.TestName = DBO.LimitedVars.TestName 
    WHERE DBO.SelCons.TestName Is Null AND DBO.LimitedVars.TestName='04W286-DH103'
    Is there a way to do this using only one query?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Would an IDENTITY column work for this?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2006
    Posts
    72
    Quote Originally Posted by Pat Phelan
    Would an IDENTITY column work for this?

    -PatP
    I don't think that would work. From what I have just now read from your link, IDENTITY needs a seed. Unless you can set the seed in the SQL through something like below...

    Code:
    INSERT INTO DBO.SelCons 
    SELECT DBO.LimitedVars.TestName, IDENTITY(CASE WHEN MAX(DBO.SelCons.[Order]) + 1 IS NULL THEN 1 ELSE MAX(DBO.SelCons.[Order]) + 1 END,1)   
    FROM DBO.SelCons RIGHT JOIN DBO.LimitedVars ON DBO.SelCons.TestName = DBO.LimitedVars.TestName 
    WHERE DBO.SelCons.TestName Is Null AND DBO.LimitedVars.TestName='04W286-DH103'
    ...I don't see how it could work. I tried the above SQL code and it does not work.

    It is possible that I am going about this in completely the wrong way, I am very new to SQL Server.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You set the IDENTITY seed value when you create the table. You don't need to specify it again when you insert to the table. So you should leave the Order column out of your query altogether. Look again at the example in the link that Pat gave.

  5. #5
    Join Date
    Jun 2006
    Posts
    72
    Does an IDENTITY field allow you to change a value after the record has been entered?

    For example if I want swap the order of two records could I do that... If so then IDENTITY sounds like it might be what I need.

    I'll look into this myself and try to work it all out. If I am going in the wrong direction though, please correct me.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    No. You can't change an IDENTITY column at all. Use a regular numeric column instead. You can use ROW_NUMBER() in your query to generate sequential numbers.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Try this:


    Code:
    INSERT INTO dbo.SelCons (TestName, [Order])
    SELECT L.TestName,
    (SELECT COALESCE(MAX([Order])+1,1) FROM dbo.SelCons)+
     ROW_NUMBER() OVER (ORDER BY L.TestName) - 1
    FROM dbo.LimitedVars L
    LEFT JOIN dbo.SelCons C
    ON C.TestName = L.TestName 
    WHERE C.TestName IS NULL
    AND L.TestName='04W286-DH103' ;
    If you are only ever inserting one row at a time then you don't need the ROW_NUMBER line at all.

    Order is a reserved word so it isn't a great choice for a column name.

  8. #8
    Join Date
    Jun 2006
    Posts
    72
    Quote Originally Posted by dportas
    Try this:


    Code:
    INSERT INTO dbo.SelCons (TestName, [Order])
    SELECT L.TestName,
    (SELECT COALESCE(MAX([Order])+1,1) FROM dbo.SelCons)+
     ROW_NUMBER() OVER (ORDER BY L.TestName) - 1
    FROM dbo.LimitedVars L
    LEFT JOIN dbo.SelCons C
    ON C.TestName = L.TestName 
    WHERE C.TestName IS NULL
    AND L.TestName='04W286-DH103' ;
    If you are only ever inserting one row at a time then you don't need the ROW_NUMBER line at all.

    Order is a reserved word so it isn't a great choice for a column name.
    This is perfect, exactly what I want. Thank you

Posting Permissions

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