Can I use SELECT statement inside INSERT values?

Question

I tried this:

INSERT  INTO tbl_vaucher
        (
          vaucher_name,
          created_date
        )
VALUES  (
          ( SELECT TOP 1
                    con_full_name
            FROM    tbl_contact
          ),
          GETDATE()
        )

, getting: Subqueries are not allowed in this context. Only scalar expressions are allowed.

I need a solution that would work without functions.

Solution

INSERT
INTO    tbl_vaucher (vaucher_name, created_date)
SELECT  TOP 1
        con_full_name, GETDATE()
FROM    tbl_contact

Comments

[Sasha]

not good, i need exactly that one field be select statement i cant use insert with one select

[Mark Dickinson]

The answer is right, just try using the select statement in a new query, it will return just what you are looking for.

[Benjamin Autin]

Yeah a lot of people forget that you can select constants and functions as part of statement as well.

[Clarence Liu]

If anyone is confused like msony, think of it this way, for a basic INSERT INTO table (field1, field2, field3) VALUES (value1, value2, value3) by default you have scalar values for values1-3, this is replacing value1-3, with SELECT value1, "A FIELD IN THE TABLE", value3. The downside is you don't seem to be able to have multiple selects, one for each field, maybe with nesting and building a temporary table you can?