Try the following query: I found this one working properly. This will select data in columns without identity = 1
DECLARE @Temp NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND columnProperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') !...
You could make the identity column IMPLICITLY HIDDEN.
CREATE TABLE T(I INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, A INT)
ALTER TABLE T ALTER COLUMN I SET IMPLICITLY HIDDEN
INSERT INTO T VALUES (42)
SELECT I, A FROM T
You can generate the columns like:
select listagg(colname,', ') as cols within group (order by colno)
where identity = 'N'
and tabschema = ?
and tabname = ?
Now put cols in both the select and the insert. The:
within group (order by colno)
is not necessary, but I consider it nice if the columns are generated in that order.
If the source and target tables do not have exactly the same columns, created in the same order, you cannot avoid specifying what columns to insert where, one way or another.
You can either specify them in the insert statement, excluding the identity column:
insert into target (col1, col2) select * from source
or, if you run such inserts regularly, you can ...