Insert a row to SQL table

Question

I am writing a store procedure in T-SQL which inserts a row to the table, based on parameters

@UserName ,@CompanyName ,@RestName,@Desc

INSERT INTO Orders(UserId,CompanyId,RestId)
    SELECT UserNames.Id,CompanyNames.Id,RestNames.Id FROM UserNames,CompanyNames,RestNames
    WHERE
    UserNames.Name = @UserName AND 
    CompanyNames.Name = @CompanyName AND
    RestNames.Name = @RestName

Besides the insert to the 3 columns above,I also want to insert the @Desc value. I tried :

INSERT INTO Orders(UserId,CompanyId,RestId,Desc)
        VALUES(
        (SELECT UserNames.Id,CompanyNames.Id,RestNames.Id FROM UserNames,CompanyNames,RestNames
        WHERE
        UserNames.Name = @UserName AND 
        CompanyNames.Name = @CompanyName AND
        RestNames.Name = @RestName),@Desc)

Only one expression can be specified in the select list when the subquery is not introduced with EXISTSt- It doesn`t work giving the following error:

M.Ali · Accepted Answer · 2014-01-01 23:05:45Z

2

@UserName ,@CompanyName ,@RestName,@Desc

INSERT INTO Orders(UserId,CompanyId,RestId, Desc_Column)
SELECT UserNames.Id,CompanyNames.Id,RestNames.Id , @Desc  --<-- Just SELECT that variable
FROM UserNames,CompanyNames,RestNames                        -- in your select statement.
WHERE UserNames.Name = @UserName 
AND CompanyNames.Name = @CompanyName 
AND RestNames.Name = @RestName

Retrieve ID Values Inserted

DECLARE @t TABLE (ID INT);   --<-- Declare a table variable 

INSERT INTO Orders(UserId,CompanyId,RestId, Desc_Column)
OUTPUT Inserted.ID    INTO @t                        --<-- use OUTPUT, get values from INSERTED Table
SELECT UserNames.Id,CompanyNames.Id,RestNames.Id , @Desc  --and insert them into your table variable
FROM UserNames,CompanyNames,RestNames                        
WHERE UserNames.Name = @UserName 
AND CompanyNames.Name = @CompanyName 
AND RestNames.Name = @RestName

/*At last just simply select from that table variable to get the inserted IDs*/
 SELECT * FROM @t

edited Jan 1, 2014 at 23:05

answered Jan 1, 2014 at 22:33

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Berkowski Over a year ago

Otherwise, use [Desc] if that is the column name, as it's a SQL keyword.

YAKOVM Over a year ago

@M.Ali - Thank you - it works!How can this query also return a value of a new (inserted ) row?ID

M.Ali Over a year ago

@Yakov do you mean the ID generated by the Identity column ?? or the ID you have just inserted ???

YAKOVM Over a year ago

@M.Ali - yes!the ID generated by the Identity column

YAKOVM Over a year ago

@M.Ali - I need to return only the ID,of the inserted row

|

Collectives™ on Stack Overflow

Insert a row to SQL table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related