2

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? – Randolph West Aug 23 '16 at 5:53
  • 2
    No, there is no way. – a_horse_with_no_name Aug 23 '16 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. – Mikael Eriksson Aug 23 '16 at 6:51
  • 2
    They're not mutually exclusive - all three could be true. – Michael Green Aug 23 '16 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 Aug 23 '16 at 10:46
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
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME='Table'

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.

EDIT:

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

SELECT @Sql

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 - Team Monica Aug 23 '16 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 Aug 23 '16 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? – James Alesi Aug 23 '16 at 15:53
  • @JamesAlesi just Select @ variableName will show it, bear in mind that variables exist only for that transaction – Ian_H Aug 23 '16 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 Aug 23 '16 at 15:56
1

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)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YourSchema'
AND TABLE_NAME = 'YourTable'

PRINT @UpdateStatement
1

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))
as 
 begin
declare @sql nvarchar(max)
set @sql = '';

-- inserts

declare @cols varchar(max)
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME=@tbl_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

--updates

set @cols =null
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME + '=x.' + COLumn_name
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    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;

end
go

And is called like this:

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

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. – James Alesi Aug 23 '16 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. – Max Vernon Aug 23 '16 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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