Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Question Unanswered: Insert into table problem...

    hi, i am trying a fairly straightforward insert, but think i may have hit a limitation of sql

    i want to run the following:

    insert into tableXXX
    (column1, columns2)
    values
    ('Value1','Value2')

    now columns2 isn't an identity column, but i need to add +1 to the current maximum value. i thought that i could just run the following:

    insert into tableXXX
    (column1, columns2)
    values
    ('Value1',(select max (column2)+1 from tableXXX))

    but get this error: "Subqueries are not allowed in this context. Only scalar expressions are allowed."

    i don't want to alter the table to create an identity, so am i able to get round this issue??

    thanks

    derek

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it like this --

    INSERT INTO tableXXX
    (column1, columns2)
    SELECT 'Value1', MAX(column2)+1 FROM tableXXX
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Make sure you appreciate that if two users execute this query at the same time you will receive duplicate values in column2...
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rarara
    now columns2 isn't an identity column, but i need to add +1 to the current maximum value.
    could you explain why you "need" to do that? what are you actually trying to achieve? and why isn't it an IDENTITY column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    Quote Originally Posted by r937
    could you explain why you "need" to do that? what are you actually trying to achieve? and why isn't it an IDENTITY column?
    hi, thanks for all replies so far. the reason i don't want an identity is that i don't know why the table (user logon details btw) was originally created that way, so would rather leave things alone for now (say the columns in question might need to be easily edited at some point?)

    i have tried your suggestion and it seems to work for the column i originally had an issue with, but now it throws this on another column which i applied the same rule to:

    "'SALESPERSONID' is not a recognized OPTIMIZER LOCK HINTS option."

    any ideas?!?

    thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, you should just go ahead and use an identity field:
    Code:
    --Current set up
    CREATE TABLE dbo.your_table (
       id   int     NOT NULL PRIMARY KEY
     , col1 char(1) NOT NULL
    )
    GO
    --Sample data
    INSERT INTO dbo.your_table (id, col1)
          SELECT  1, 'A'
    UNION SELECT  2, 'B'
    UNION SELECT  3, 'C'
    UNION SELECT  4, 'A'
    UNION SELECT  8, 'A'
    UNION SELECT  9, 'C'
    UNION SELECT 12, 'D'
    GO
    
    --Create a new table to hold the data - same set up as existing table just
    --  make the id field an identity
    CREATE TABLE dbo.your_new_table (
       id   int     NOT NULL identity(1,1) PRIMARY KEY
     , col1 char(1) NOT NULL
    )
    GO
    
    --So we can retain the existing id values, identity_insert must be turned on
    --  on our new table
    SET IDENTITY_INSERT dbo.your_new_table ON
    GO
    
    --Copy the current data to the new table
    INSERT INTO dbo.your_new_table (id, col1)
    SELECT id
         , col1
    FROM   dbo.your_table
    GO
    
    --Turn identity_insert off
    SET IDENTITY_INSERT dbo.your_new_table OFF
    GO
    
    --Show contents of new table
    SELECT *
    FROM   dbo.your_new_table
    GO
    
    --Drop the first table
    DROP TABLE dbo.your_table
    GO
    
    --Rename new table!
    EXEC sp_rename 'your_new_table', 'your_table'
    GO
    
    --Check it out!
    SELECT *
    FROM   dbo.your_table
    GO
    
    --Test identity
    INSERT INTO dbo.your_table (col1) VALUES ('Z')
    GO
    
    --So there you have it - you'd never even know it was any different!
    SELECT *
    FROM   dbo.your_table
    George
    Home | Blog

Posting Permissions

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