INSERT with subquery and scalar vars

[blozzy]

hi,

i would like to make an insert statement with a subquery, i know that it is possible with a following select command, but i would like to combine it with scalar vars from a data grid ? please help:

INSERT INTO akquise_dispo_stunden(sb_id,akquiseprojekt_id,monat,jahr,ma_name,stunden)VALUES(@sb_id,'test',1,@jahr,([B]SELECT name FROM ma WHERE user_id=@sb_id[/B]),@JanStd);

please help thank you

[sknake]

In order to Insert Into with a subquery you cannot use Values(), you must use select. Here is an example:

--Getting a test environment set up.....

IF OBJECT_ID('Users', 'U') IS NOT NULL DROP TABLE Users
Create Table Users
(
  UserId int PRIMARY KEY,
  UserName varchar(100)
)

IF OBJECT_ID('Comment', 'U') IS NOT NULL DROP TABLE Comment
Create Table Comment
(
  CommentId int identity(1000, 1) PRIMARY KEY,
  UserId int NOT NULL,
  UserName varchar(100) NOT NULL,
  Comment varchar(100) NOT NULL
)

Insert Into Users (UserId, UserName) Values (5000, 'Scott Knake')

--Now answering your question
Declare 
@UserId int, 
@Comment varchar(100)

Set @UserId = 5000 --This is the same value as above, you will fetch it off the logged in user info
Set @Comment = 'Hello forum!'

Insert Into Comment (UserId, UserName, Comment)
Select @UserId,
(
  Select UserName
  From Users (NOLOCK)
  Where Users.UserId = @UserId
) As UserName, --Use a subquery to fetch the username from the user table
@Comment

Notice I am using Insert Into (...) Select ... without using a FROM clause, this is legal and will return a single row. I hope this answers your question.