As indicated in this post Update all columns from another table?

I need to update a table with 50 columns with values from another table, and then insert new records from a different table (with 50 columns). I really don't want to list 50 name-value pairs (or use the bulk updating syntax here Bulk update of all columns, which SQL Server doesn't seem to like anyway).

So, I can conclude one of the following:

  1. There IS a way after all,
  2. It's ridiculous to have a table with 50 columns, or
  3. Database Admins are masochists.
  • 3
    You can copy and paste the column names, surely? Commented Aug 23, 2016 at 5:53
  • 2
    No, there is no way.
    – user1822
    Commented Aug 23, 2016 at 5:56
  • 4
    1. Sure, could be solved with programming in some language of your choice using meta data as suggested by @RandolphWest . 2. Perhaps. 3. I'm sure there are all kinds but I believe the majority of them are keen on automating tedious tasks. Commented Aug 23, 2016 at 6:51
  • 2
    They're not mutually exclusive - all three could be true. Commented Aug 23, 2016 at 10:42
  • 5
    Seems to me the time you wrote asking the question could have been spent copying/pasting those column names and you'd have your update statement.
    – Molenpad
    Commented Aug 23, 2016 at 10:46

4 Answers 4


Unless I've missed something here - and I may have - I think this is fairly straightforward.

Essentially the only problem here is you don't want to type out all the column names- that can easily be automated with a bit of dynamic SQL using the sys tables as hinted at by Randolph West.

Something like

select @Sql = COALESCE(@Sql + ', ', '') + COLUMN_NAME

Which will list out all the columns separated with commas, as if you typed them ready for insert/update (or select).

This post: Selecting column name dynamically in an insert query is a stack overflow question that goes into the detail.


Just thought I'd add for clarity and to consolidate some comments:-

Anything can be added to the @Sql 'building' bit, for example if you want the table name put before the column name, the select part would be:

select @Sql = COALESCE(@Sql + ', ', '') + TABLE_NAME + '.' + COLUMN_NAME

You can execute that variable using

sp_executesql @Sql

Or just


to give it to you in a query execution window, which you can copy & paste and run (which I would usually do - you can double check the code first and edit if needed)

  • I think the question was about updating
    – Tom V
    Commented Aug 23, 2016 at 14:25
  • @TomV - yes, but the code can be used as part of an update, or anything, what I showed is just the way to list all the column names, in a code-usable way, without having to type them out by hand, which I understood was the actual problem. The link provided is about inserting, but it shouldn't take a great deal amount of skill to adapt that for the OP's purposes and UPDATE, it just shows the principle.
    – Ian_H
    Commented Aug 23, 2016 at 15:47
  • @Ian_H thanks, I am attempting something like your response. Quesion: how do I view a variable's value after setting it in SSMS? Commented Aug 23, 2016 at 15:53
  • @JamesAlesi just Select @ variableName will show it, bear in mind that variables exist only for that transaction
    – Ian_H
    Commented Aug 23, 2016 at 15:54
  • @JamesAlesi That's one way to do it that I tend to actually, rather than executing the variable, just selecting it and then copying and pasting into another window, that way you can check it or edit it before running (assuming it's a one off and not automated)
    – Ian_H
    Commented Aug 23, 2016 at 15:56

It's not pretty and a bit of surgery needs to be done with the resulting statement (remove trailing comma and add the table joins), but I have used this in the past...

DECLARE @UpdateStatement VARCHAR(max)

SET @UpdateStatement = 'UPDATE LeftTable SET ' + CHAR(10)

SELECT @UpdateStatement = @UpdateStatement + 'LeftTable.' + COLUMN_NAME + ' = RightTable.' + COLUMN_NAME + ', ' + CHAR(10)
AND TABLE_NAME = 'YourTable'

PRINT @UpdateStatement

This stored procedure loops through fields and creates inserts followed by updates to bring two tables in line... I use it for copying data from one db to another (it doesn't do any deletes).

create procedure spInsertUpdateData (
 @tbl_name varchar(255),
 @srcDB varchar(255),
 @destDb varchar(255),
 @primaryKey varchar(255))
declare @sql nvarchar(max)
set @sql = '';

-- inserts

declare @cols varchar(max)
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME

set @sql= 
'set identity_insert ' + @destDb  +'.dbo.' + @tbl_name  +' on;'  +
'insert into ' + @destDb  +'.dbo.' + @tbl_name + '(' + @cols +') select ' + @cols + ' from '  + @srcDB + '.dbo.' + @tbl_name + ' where not exists (select 1 from ' + @destDb + '.dbo.' + @tbl_name + ' x where x.' + @primaryKey  + '='   + @srcDb +'.dbo.' + @tbl_name + '.' + @primaryKey  + ');'+
'set identity_insert ' + @destDb  +'.dbo.' + @tbl_name  +' off;'  

select @sql;
exec sp_executesql @sql


set @cols =null
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME + '=x.' + COLumn_name
    TABLE_NAME=@tbl_name and (not(COLUMN_name like @primaryKey));

set @sql = 'update dest set ' + @cols  + ' from ' + @srcDB +'.dbo.' + @tbl_name + ' x, ' +  @destDb + '.dbo.' + @tbl_name + ' dest where dest.' +@primaryKey + '=x.' + @primaryKey + ';'

select @sql;
exec sp_executesql @sql;


And is called like this:

exec spInsertUpdateData 'lkp_conditions','srdb','destdb','condition_id';

Using SQL Server Management Studio, you can very easily get a list of column names by drag-and-drop:

enter image description here

Simply drag-and-drop the "Columns" heading from the desired table onto the query window, and "hey presto", all the column names are magically typed into the query window.

See my answer on another question for more details about how to use search-and-replace with regular expressions to further edit the list of column names.

  • That's great, but I need to differentiate between the columns of the old table and the columns of the new table. Which means, that for at least one of the tables, I need a '[table_name].' in front of each column name in the list. Commented Aug 23, 2016 at 16:24
  • I added a link to an older answer where I provide an example of how to modify the list of column names as required.
    – Hannah Vernon
    Commented Aug 23, 2016 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.