Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: use of subqueries

    Hi,

    I have the following sql statement:
    INSERT INTO prg_lvl VALUES (
    'Data Recovery',
    'Data Recovery',
    7,
    'ENG',
    0,
    16,
    'Menu=w_data_recovery_stk;',
    ( select max(menu_id) + 1 from prg_lvl ),
    'K');
    that works for Sybase and I wanna make it works for SQL Server.

    The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).

    Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem

    Thanks,
    fady

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Hai
    Try to change (select max(menu_id) + 1 from prg_lvl) into
    '(select max(menu_id) + 1 from prg_lvl)'

  3. #3
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    INSERT INTO prg_lvl

    SELECT 'Data Recovery', 'Data Recovery', 7, 'ENG', 0, 16, 'Menu=w_data_recovery_stk;', max(menu_id) + 1, 'K'
    from prg_lvl;

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    marp's solutions is correct, but you should always list the columns into which the data is being inserted as part of your INSERT clause.

    INSERT INTO prg_lvl ([Column1], [Column2], ... [ColumnN])
    SELECT 'Data Recovery', 'Data Recovery', 7, 'ENG', 0, 16, 'Menu=w_data_recovery_stk;', max(menu_id) + 1, 'K'
    from prg_lvl
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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