Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    76

    Unanswered: option to store the inserted values

    Hi,

    In oracle there is "RETURNING" Clause which stores the values inserted to a variable. Is there any option in sql server. i.e storing and fetching takes place at one shot.

    eg:
    insert into tab (name) values ('hello') returning name into val;

    -cheeku

  2. #2
    Join Date
    Dec 2004
    Posts
    47
    In an INSERT statement, a SELECT statement returning a scalar value was allowed in the VALUES clause.
    __________________________________________________ _____________

    The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.

    Go thru this link,

    http://msdn.microsoft.com/library/de...kcomp_3tbt.asp

    Compatibilty Verson 60 or 65... allows INSERT.. SELECT INTO option....

    Sree

  3. #3
    Join Date
    Aug 2004
    Posts
    76
    Thanks for the info. But am now introduced to new term compatibility setting to 60 or 65. Will changing the compatibility have any impact with SQL Server 2000 behaviour. What are its effect.

  4. #4
    Join Date
    Dec 2004
    Posts
    47
    I havent tried doing it.... Usually it need to be backward compatible... but as there is a list of features in older one which is not there in newer. If u have gone thru all the supported links in that u could see that new version Trigger can be used for the sane purpose.... Like if we need to get a scalar value assigned to variable after inserting use trigger & while using that 'SET NOCOUNT' ON for this scenario... something like that.....

    For Eg:

    create trigger test on TableName for insert, update, delete
    as
    declare
    @TableName varchar(128)

    SET NOCOUNT ON

    "select @TableName = For Last Inserted value..................."

    Does this solve ur problem.....

  5. #5
    Join Date
    Oct 2004
    Location
    London
    Posts
    21
    If the value you would like to be returned is the primary id field then you can use 'SCOPE_IDENTITY()'.


    INSERT INTO dbo.TL1_MyTestTable
    (-- Primary_ID,
    Test1_ID,
    Test2_ID,
    txtTestText1
    VALUES
    (-- @Primary_ID,
    10,
    20,
    'Test Text1')
    IF @@Error <> 0
    BEGIN
    RAISERROR('The insert did not work dude!',16,1)
    ROLLBACK TRANSACTION
    RETURN -1
    END
    ELSE
    BEGIN
    SET @Primary_ID = SCOPE_IDENTITY()
    COMMIT TRANSACTION
    RETURN 0
    END
    GO



    This way you can use the id returned by SCOPE_IDENTITY() to return the whole row within the stored proc if you need to:


    Select * from dbo.TL1_MyTestTable
    Where Primary_ID = @Primary_ID
    GO


    Hope this helps.

    Regards

    Eamon.

Posting Permissions

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