SQL using variable in SELECT

Question

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?

MySQL or SQL? You have tagged them both but SQL is suggested in title. — Avada Kedavra
– Avada Kedavra, Commented Jul 6, 2011 at 21:44
You have this tagged with SQL Server 2008 and MySQL. Which are you really using? This is valid syntax in SQL2K8. — Yuck
– Yuck, Commented Jul 6, 2011 at 21:45
Syntax looks OK. Are you executing both the DECLARE and the INSERT in the same batch? — Joe Stefanelli
– Joe Stefanelli, Commented Jul 6, 2011 at 21:50
@Jesse, I think you are getting that error because you are using double quotes, e.g. DECLARE @Bar VARCHAR(50) = "new bar" ... see why it is important to show the EXACT code you are using? — Aaron Bertrand
– Aaron Bertrand, Commented Jul 7, 2011 at 1:06

Conrad Frix · Accepted Answer · 2011-07-06 22:18:53Z

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();
}

Yuck · Accepted Answer · 2011-07-06 21:56:08Z

3

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.

answered Jul 6, 2011 at 21:56

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

4 Comments

Jesse Over a year ago

I just keep getting thrown an error saying that @Bar is not a valid column. Ugh.

Yuck Over a year ago

@Jesse: How are you running this query? Are you using SSMS? Is this code the only code in your query window?

Jesse Over a year ago

Silly me, the error was being thrown on a different statement. I'm trying to set the value of @Bar like so SET @Bar='new bar' after I declare it. MSSQL won't let me set a default value during the variable declaration so I have to do it this way; but that's just it...@Bar isn't a column. How can I assign a value to it then?

Aaron Bertrand Over a year ago

@Jesse, there is a HUGE difference between single quotes and double quotes. I feel bad for all of the people who have wasted time chasing wild geese when you didn't bother posting the actual code you were using. :-(

ypercubeᵀᴹ · Accepted Answer · 2011-07-06 23:15:14Z

1

Instead of:

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

Can you try? :

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

answered Jul 6, 2011 at 23:15

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

2 Comments

Jesse Over a year ago

I have tried that (because I would get an error if I tried the former) and it appears that the real culprit of the error is the line SET @Bar = 'new bar'. MSSQL thinks I'm trying to SET a column value instead of the variable...

ypercubeᵀᴹ Over a year ago

@Jesse: SET @Bar = "new bar" gives me Invalid column name 'new bar'. SET @Bar = 'new bar' works fine.

Barkermn01 · Accepted Answer · 2011-07-06 21:56:32Z

0

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');

edited Jul 6, 2011 at 21:56

answered Jul 6, 2011 at 21:51

Barkermn01

6,85938 silver badges87 bronze badges

2 Comments

Yuck Over a year ago

Copy the code from the answer I've provided. It works fine in SSMS Query Analyzer if you run all three statements in one batch.

Barkermn01 Over a year ago

Thanks any ways you have made me learn some thing new with the insert select stuff any ways

Collectives™ on Stack Overflow

SQL using variable in SELECT

4 Answers 4

Comments

4 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related