Is there really no way in SQL to update all fields in a table without explicitly typing field names

Question

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:

There IS a way after all,
It's ridiculous to have a table with 50 columns, or
Database Admins are masochists.

I don't understand. Do you not have access to SELECT * FROM sys.columns WHERE object_name(object_id) = 'table_name'? — Randolph West, Aug 23 '16 at 05:59
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
Do you really want to trust data modifications to an algorithm that decides which columns match up between two tables? — datagod, Aug 23 '16 at 16:55
If you need to do this once - just copy-paste or use excel with simple formulas. If you expect this is a repeatable action - create a procedure that will build (and optionally execute) dynamic sql to dml data between tables with same structure. — Anton Krouglov, Aug 25 '16 at 20:09

score 5 · Answer 1 · edited May 23 '17 at 12:40

5

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)

edited May 23 '17 at 12:40

Community

1

answered Aug 23 '16 at 10:36

Ian_H

1,646
10
17

I think the question was about updating – Tom V 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

score 2 · Answer 2 · answered Aug 23 '16 at 17:52

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

Jim · Answer 3 · 2019-07-09T10:38:05.577

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

score 0 · Answer 4 · edited Apr 13 '17 at 12:42

0

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

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.

edited Apr 13 '17 at 12:42

Community

1

answered Aug 23 '16 at 16:17

Hannah Vernon

70,041
22
171
315

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

Is there really no way in SQL to update all fields in a table without explicitly typing field names

4 Answers4