search

TimelordUK / node-sqlserver-v8

branched from node-sqlserver, SQL server driver compatible with all versions of Node
Other
135 stars 43 forks source link

Error When Bulk Inserting Data into a Table with a Column Used as Both Primary and Foreign Key #335

Closed lePauloRicardo closed 1 month ago

lePauloRicardo commented 1 month ago

When we try to bulk insert data to a table where a column is used both as primary and foreign key

we have the following error

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The column name 'column_used_as_pk_and_fk' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

This is may be the same problem as https://github.com/TimelordUK/node-sqlserver-v8/issues/100, but it's happening on the latest version (4.2.1).

I noticed that the query that is used to fetch the column names (https://github.com/TimelordUK/node-sqlserver-v8/blob/master/lib/queries/table_describe.sql) is using two cases )lines 31 to 45 that are duplicating the results (one for primary key, and the other for foreign key)

ordinal_position    table_catalog   table_schema    table_name  column_default  name                type    max_length  precision   scale   is_nullable is_computed is_identity object_id   generated_always_type   generated_always_type_desc  is_hidden   is_primary_key  is_foreign_key
1           CTLG        dbo     My_Table    NULL        column_used_as_pk_and_fk    int 4       10      0   0       0       0       1877581727      0           NOT_APPLICABLE          0       0       1
1           CTLG        dbo     My_Table    NULL        column_used_as_pk_and_fk    int 4       10      0   0       0       0       1877581727      0           NOT_APPLICABLE          0       1       0

After some tweaking with the query, I was able to have it generating only one column

ordinal_position    table_catalog   table_schema    table_name  column_default  name                type    max_length  precision   scale   is_nullable is_computed is_identity object_id   generated_always_type   generated_always_type_desc  is_hidden   is_primary_key  is_foreign_key
1           CTLG        dbo     My_Table    NULL        column_used_as_pk_and_fk    int 4       10      0   0       0       0       1877581727      0           NOT_APPLICABLE          0       1       1

I'm not sure if I'm doing something wrong, but if you'd like, I'll create a PR and you may use it, if you think it's the away to solve it.