Insert into <table> select with specific column values

Question

So I was wondering if there was something like this:

insert into Table_History select *, CreationUser=getUser() from deleted;

where I overwrite/set the value of only one column of the asterisks without having to write out all columns and replacing CreationUser with getUser().

I don't want to write every column out like this:

insert into Table_History select Id, Name, getUser() from deleted;

or do I have to just update the value after the insert?

Edit: The reason for this is not that I'm too lazy to type the columns out, but rather that this is inside trigger. And I don't want to edit the trigger to add the rows to the insert every time I alter the Table. But rather only update the two Tables and not have to worry about the trigger.

* returns all the columns within the dataset (or objectname.* all the columns for the object). There is no * {replace column with this} or * {except this column}. You need to define the columns you want to SELECT. If you really don't want to type them out, look at purchasing an add-in tool that offers functionality to expand the * into the relevant column names. — Thom A
– Thom A ♦, Commented Dec 1, 2020 at 15:31
Isn't simpler to just add a new ModificationUser column to Table_History, so you can fill it with all the columns on deleted, plus getUser() ? — Marc Guillot
– Marc Guillot, Commented Dec 1, 2020 at 15:40
@MarcGuillot ye probably. I wanted to I guess reuse the column since I dont really have a use for the column in the History table. Because the original creation User would still be in the normal Table. And changing the creation user sounded correct to me since the row was created because of the user that changed the row and not necessarily the user that created the original row. — Jan
– Jan, Commented Dec 1, 2020 at 15:47
every time I alter the Table but you shouldn't be consistently changing the schema in the first place. — SMor
– SMor, Commented Dec 1, 2020 at 16:33
@SMor you're right but shouldn't I try to keep it as simple as possible in case there is something that's getting added. Because what if someone just adds it and never realises that they also have to edit the trigger. Yes this would be a mistake by them I guess but what if I can just write the trigger "that good" that that mistake could just never happen. — Jan
– Jan, Commented Dec 1, 2020 at 17:39

jw11432 · Accepted Answer · 2020-12-01 15:38:23Z

1

Highlight the table name. Press Alt + F1 to bring up table details. In there, you can click on the Column_name column header in the second box and copy the column names and paste them into your query to avoid typing out every column name. Just need to add commas at that point.

answered Dec 1, 2020 at 15:38

jw11432

5933 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Over a year ago

Thanks for answering :)! Not what I wanted but I updated the question to make it more clear. Still thanks for the answer. I'm gonna use that next time since that's still a great tip :P!

Alex · Accepted Answer · 2020-12-01 22:52:52Z

There are no "easy" shortcuts for what you are trying to do.

Reasons:

SQL Server has no functionality for positional column referencing.
As @Larnu said there are no "There is no * {replace column with this} or * {except this column}."
CreatedBy column will not always be last as when adding columns to the tables, they will be added to the last position, unless you force a specific position by re-creating the table.

Solutions:

Update after insert - as you have mentioned.
Use a temp table / table variable to temporarily store the data and update that before writing it to history table.

The choice depends on how many rows are typically upserted (updated / inserted) at one time and how big the history table is. Small upsert batches - use table variable. For very large batches table variables will probably slow down performance as you would be copying a lot of data around.

Collectives™ on Stack Overflow

Insert into <table> select with specific column values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related