SQL insert from select, but set some values by hand

Question

Let's say that I have table with A LOT of columns. I have one column with primary key that has autoincrement set to 1. I want to insert a new row and in this new row I have following requirements:

• The row must have generated ID
• All non-specified columns have to be copied from row with id='9999'
• I have to be able to set some values of columns by hand (for example columns name and age

I have tried:

Insert Into demo_table
  Select * From demo_table Where id='9999';

However, I get this error:

An explicit value for the identity column in table 'demo_table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What do I need:

I want to duplicate a row -> let the id be set by database (I have PK and autoincrement configured) -> set some columns by hand -> have other column's values duplicated, without specifying column names (as I have a lot of columns and their names could change in future.)

Form of solution:

I would prefer if I was able to achive this using only one query. If necessary, I have stored procedures available.

My question:

Is this even possible? How could I achive such query/procedure?

Answer 1

There is a way to build sql query by table schema:

USE <databaseName>

DECLARE
    @SourceTableName NVARCHAR(255) = <TableName>,
    @SqlQuery NVARCHAR(MAX)

IF OBJECT_ID('tempdb.dbo.#IdentityCols', 'U') IS NOT NULL 
    DROP TABLE #IdentityCols; 
CREATE TABLE #IdentityCols(
    ColumnName NVARCHAR(255)
)

INSERT INTO #IdentityCols
SELECT
    --TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 AND TABLE_NAME = @SourceTableName
UNION
SELECT
    --o.name,
    c.name
FROM
    sys.objects o inner join
    sys.columns c on o.object_id = c.object_id
WHERE
    c.is_identity = 1 AND o.name = @SourceTableName

--STRING_AGG in SQL SERVER 2017 and greater. As aproach for early versions is cursor or loop
SELECT @SqlQuery = 'SELECT ' + STRING_AGG(COLUMN_NAME, ',') + ' FROM ' + @SourceTableName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@SourceTableName AND COLUMN_NAME NOT IN (Select ColumnName FROM #IdentityCols)


exec sp_executesql @SqlQuery

For more information you can see this questions:

How can I show the table structure in SQL Server query?

How do you determine what SQL Tables have an identity column programmatically

How to concatenate text from multiple rows into a single text string in SQL Server

SQL Server Loop through Table Rows without Cursor

SQL Server loop - how do I loop through a set of records

Answer 2

For anyone interested, how I've solved this problem:

After I've read your comments (thanks btw) and some threads online, I've realized why I cannot do what I asked. However, I've come seen solution to similar problem somewhere, where they wanted to select * except one specific column, they solved it like this:

• copied the entire table
• selected from there.

I've come up with similar solution to my problem

-- check data, remove after testing
select * from demo_table order by ID desc;

-- create table with one column I want to duplicate
select * into Temp_demo_table from demo_table where ID=9999;

-- drop id, so it does not get included in the inter-table insert
alter table Temp_demo_table
    drop column ID;

-- update columns that I need to modify, doesn't have to have WHERE clause, becuase there's only one row there
update Temp_demo_table set MyCustomProperty='abc', name=NULL, age=NULL

-- insert the desired and edited row
insert into demo_table
    select * from Temp_demo_table;

-- drop the temp table
drop table Temp_demo_table;

-- check data, remove after testing
select * from demo_table order by ID desc;

I realize how inefficient this is, however the function (on my api) executing this command will not be called so often (max 100 times per day). I believe that this query could be optimized, however I do not have sufficient knowledge to do it at this moment (100% going to put it in my TODO :D).

Edit-1:

Just found out that you can write queries in oracle db like this:

(select * from demo_table) - (select name, age from demo_table)

I currentlly don't know if I can apply this to sql server, however as soon as I have an access to mssql, I'll try it out and keep this answear updated!