SQL using variable in SELECT

I can copy a row and change the value of a single field in the same table like this which works perfectly fine:

INSERT INTO myTable (foo,bar)
    SELECT foo,'new bar'
    FROM myTable
    WHERE rowIndex=5

But I'd like to replace 'new bar' with an inline variable. I have tried this to no avail:

DECLARE @Bar varchar(50) = 'new bar'
INSERT INTO myTable (foo,bar)
    SELECT foo,@开发者_开发百科Bar
    FROM myTable
    WHERE rowIndex=5

I just get thrown an error that says @Bar is an invalid column.

Is there a way to do this?

Just in case you thought that Yuck's answer had an issue with the temp table here it is with a non-temp table

CREATE TABLE 
         myTable 
  (foo varchar (50),
   bar varchar(50) ,
   rowIndex int identity)

SET identity_INSERT MyTable on 
INSERT INTO myTable  (foo, bar, rowIndex ) values ('foo','bar', 50)
SET identity_INSERT MyTable oFF

DECLARE @Bar varchar(50) = 'new bar'
INSERT INTO myTable (foo,bar)
    SELECT foo,@Bar
    FROM myTable
    WHERE rowIndex=50

    select foo, bar , rowIndex
    from myTable
    Drop table mytable

Results in

foo bar     rowIndex
--- ------- --------
foo bar     50
foo new bar 51

This also works in c# using the SQLDataClient

using(SqlConnection cnn = new SqlConnection("Data Source=yoursever;Initial  Catalog=yourDB;Integrated Security=SSPI;"))
{ 
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();

    cmd.CommandText = @"DECLARE @Bar varchar(50) = 'new bar' 
                        INSERT INTO myTable (foo,bar)   
                        SELECT foo,
                             @Bar    
                         FROM 
                           myTable    
                         WHERE rowIndex=50";
    cmd.ExecuteNonQuery();
}

I ran this code on my laptop - SQL Server 2K8 v10.0.1600

CREATE TABLE #MyTable (
  rowIndex Int IDENTITY(1, 1),
  foo VarChar(10),
  bar VarChar(50)
);

DECLARE @Bar VarChar(50) = 'new bar';
INSERT INTO #MyTable (foo, bar)
    SELECT foo, @Bar
    FROM #MyTable
    WHERE rowIndex = 5;

DROP TABLE #MyTable;

It works fine if executed as a single batch and is (other than the temporary table) identical to yours.

Instead of:

DECLARE @Bar VarChar(50) = 'new bar' 

Can you try? :

DECLARE @Bar VarChar(50) 
SET @Bar = 'new bar' 

for stuff like that you should be using Stored Procedures

Sorry this might be my experiences but i have never seen an insert SELECT before?

MYSQL Example

DELIMITER //
    CREATE PROCEDURE InsertInto_myTable(
        IN Bar              VARCHAR(11)
    )
    BEGIN
        INSERT INTO `myTable` (foo,bar) SELECT `foo`,@Bar FROM `myTable` WHERE rowIndex = 5;
    END //
DELIMITER ;

Then once this is done you just call CALL InsertInto_myTable('new bar')

MSSQL Example

CREATE PROCEDURE InsertInto_myTable(
    Bar varchar(50)
)
BEGIN
    INSERT INTO myTable (foo,bar) 
    SELECT foo,@Bar
    FROM myTable
    WHERE rowIndex=5;
END

Use EXECUTE InsertInto_myTable('new bar');