How to insert records into BigQuery Linked server

Question

I have used Simba ODBC driver to connect SQL server to Bigquery as linked server in SQL Server Management Studio. Not able to insert into BigQuery, only able to select data from BigQuery. I have checked 'AllowInProcess' and 'NonTransactedUpdate' too.

select * from openquery([GoogleBigQuery], 'select * from first.table2' )

The above select query is working.

Query:

insert into OPENQUERY([GoogleBigQuery], 'select * from first.table2') values (1,'c')

Error generated:

"The OLE DB provider "MSDASQL" for linked server "GoogleBigQuery" could not INSERT INTO table "[MSDASQL]" because of column "id". The user did not have permission to write to the column."

Query:

INSERT INTO [GoogleBigQuery].[midyear-byway-252503].[first].[table2] select * from Learning_SQL.dbo.demo

Error generated:

OLE DB provider "MSDASQL" for linked server "GoogleBigQuery" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

The OLE DB provider "MSDASQL" for linked server "GoogleBigQuery" could not INSERT INTO table "[GoogleBigQuery].[midyear-byway-252503].[first].[table2]" because of column "id". The user did not have permission to write to the column.

Was wondering if anyone has tried inserting into a dataset in BigQuery using Linked server.

Answer 1

This error is due to this limitation. It seems that Microsoft's SQL Server "Linked Servers" option does not support making INSERT, UPDATE, or DELETE calls to the external database being linked to unless the connection supports transactions.

Since BigQuery does not support explicit transactions, MSSQL would not allow INSERT, UPDATE, or DELETE calls to BigQuery.

If you would like to insert data into BigQuery, consider exporting the data into a file, and load that file into BigQuery. The import file can be in Avro, CSV, JSON (newline delimited only), ORC, or Parquet format.

For more information, refer to importing data into BigQuery,