INSERT INTO MyTable @columns Is this possible?

Last Post 08 May 2012 06:21 AM by gunneyk. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
new2sql
New Member
New Member

--
07 May 2012 07:24 AM
Ok... so this might seem like an easy one to you guru's out there, but I'm curious
as to what in the world it would take to make this work.

Basically I have a column list as a variable named @columns ( Sounds simple enough right ),
and I want to use the @columns in an insert statement.

Is this even possible? If so... what am I doing wrong?

Here's what I got so far
/*********************************************************/

-- gets the column names and coalescing them into a string.

declare @columns varchar(max)

select @columns = coalesce(@columns + ', ', '') + column_name
from information_schema.columns where table_name = 'MyTable'
order by ordinal_position


-- next i just want to use the @columns with an insert statement. simple right?

insert into MyTable(@columns)
select * from SourceTable

-- ok so that didn't work probably because the insert doesn't know what the variable is doing
-- within the brackets () so... i create another variable to add brackets in with the string.
-- like this.

declare @capsulatecolumn varchar(50)
set @capsulatecolumn = '(' + @columns + ')'

-- ok so i figure i can use the whole thing together, and try it like this

insert into MyTable @capsulatecolumn
select * from SourceTable

-- still doesn't work. what would be the best way to get this going?

/*********************************************************/

really grateful for any ideas from the pro's.

many thanks guys.
new2sql
New Member
New Member

--
07 May 2012 07:44 AM
by the way... the source table, and the destination table have exactly the same schema. just sayin.
gunneyk
New Member
New Member

--
07 May 2012 02:56 PM
Use dynamic SQL. If the variable has the list of columns formatted properly you can do something like this:

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'INSERT INTO MyTable (' + @capsulatecolumn + ') SELECT * FROM SourceTable'

EXEC(@SQL)
new2sql
New Member
New Member

--
08 May 2012 05:36 AM
gunny... bro MANY THANKS it worked perfectly!

gunneyk
New Member
New Member

--
08 May 2012 06:21 AM
You might want to read up on Dynamic SQL. Have a look here: http://www.sommarskog.se/dynamic_sql.html
You are not authorized to post a reply.

Acceptable Use Policy