Bulk insert not possible for tables, that contain columns with `GENERATED ALWAYS` (i.e. Temporal Tables)

[olsn]

I've started of trying this via node-mssql, but could track down the issue to this library. (Original issue-report linked here: tediousjs/node-mssql#880)

---

The issue is, that the TableMgr genereates the Insert-Signatures and includes the system-columns, even if they are set to GENERATED ALWAYS or HIDDEN (as is the case for the time-columns in temporal tables)

The following is the error-message:

[Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert an explicit value into a GENERATED ALWAYS column in table 'TEST.dbo.MyTemporalTestTable'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.] {
  sqlstate: '42000',
  code: 13536
}

I've got this to work which adjusting the recalculateAssignableColumns in table.js:68 (https://github.com/TimelordUK/node-sqlserver-v8/blob/master/lib/table.js#L68)
With the following code:

    function Meta (tableName, cols) {
	  cols = cols.filter(col =>  col.name !== "ValidFrom" && col.name !== "ValidTo");
		
      function getFullName () {
      ...

However this is not a proper fix, as the names of the system-time-columns are arbitrary and the user can set any name. (in my case I chose ValidFrom and ValidTo.
I've looked into the description-meta-data of the table as well, but I don't see any usable information about the columns, that are being generated - is_compute is false for those columns.

[TimelordUK]

looks like we can add c.generated_always_type to the table_describe.sql which gives the information we need on GENERATED ALWAYS. Also looks like we need to pick up is_hidden so as below

• ill patch and re-release

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));


WITH t_fuzzy_cte(id, full_name, table_name) AS
(SELECT  TOP (1)
	0 AS id,
	TABLE_CATALOG + '..' + TABLE_NAME AS full_name,
	TABLE_NAME
	FROM
		scratch.INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME LIKE 'People%')
,
t_exact_cte(id, full_name, table_name) AS
(SELECT TOP (1)
		0 AS id,
		TABLE_CATALOG + '..' + TABLE_NAME AS full_name,
		TABLE_NAME
      FROM
			scratch.INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = '<table_name>'
),
t_name_cte(full_name, table_name) AS
(
	SELECT
	    ISNULL(e.full_name, f.full_name) AS full_name,
	    ISNULL(e.table_name, f.table_name) AS table_name
    FROM t_fuzzy_cte AS f
		LEFT OUTER JOIN t_exact_cte AS e ON e.id = f.id
)
SELECT
	sc.table_catalog,
	sc.table_schema,
	sc.table_name,
  c.name name,
  t.Name type,
  c.max_length,
  c.precision,
  c.scale,
  c.is_nullable,
  c.is_computed,
  c.is_identity,
  c.object_id,
  (
  CASE
  WHEN CONSTRAINT_NAME IN (SELECT NAME
                           FROM scratch.sys.objects
                           WHERE TYPE = 'PK')
    THEN 1
  ELSE 0
  END) AS is_primary_key,
  (CASE
  WHEN CONSTRAINT_NAME IN (SELECT NAME
                           FROM scratch.sys.objects
                           WHERE TYPE = 'F')
    THEN 1
  ELSE 0
  END) AS is_foreign_key,
  c.generated_always_type,
  c.generated_always_type_desc,
  c.is_hidden
FROM scratch.INFORMATION_SCHEMA.TABLES st
  INNER JOIN scratch.INFORMATION_SCHEMA.COLUMNS sc
    ON sc.TABLE_CATALOG = st.TABLE_CATALOG
       AND sc.TABLE_SCHEMA = st.TABLE_SCHEMA
       AND sc.TABLE_NAME = st.TABLE_NAME
  LEFT JOIN scratch.INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    ON sc.TABLE_CATALOG = u.TABLE_CATALOG
       AND sc.TABLE_SCHEMA = u.TABLE_SCHEMA
       AND sc.TABLE_NAME = u.TABLE_NAME
       AND sc.COLUMN_NAME = u.COLUMN_NAME
  cross join t_name_cte r
  INNER JOIN 
 scratch.sys.columns c ON c.name = sc.column_name
  INNER JOIN
  scratch.sys.types t ON c.user_type_id = t.user_type_id
  INNER JOIN
  scratch.sys.tables ta ON ta.name = r.table_name
  LEFT OUTER JOIN
  scratch.sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  LEFT OUTER JOIN
  scratch.sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
  c.object_id = ta.object_id
  AND TABLE_TYPE = 'BASE TABLE'
  AND sc.TABLE_NAME = r.table_name
  AND (sc.TABLE_SCHEMA = 'dbo' or 'dbo' = '')
  AND (ta.schema_id = SCHEMA_ID('dbo') or 'dbo' = '')

[TimelordUK]

please try 0.7.3 I have addd the SQl as above, see

      function readOnly (col) {
        return (col.is_identity || col.is_computed || col.is_hidden || col.generated_always_type)
      }

[olsn]

Awesome, thanks for the update, I will give it a try tomorrow and update here.

[olsn]

Great! It works! Thank you very much!