SQL : Insert to Table via OPENQUERY

Question

Hi Team - I am trying to insert Data into ServerA / databaseA / TableA via Linked Server from ServerB / databaseB / TableB.

Below is the sample script : Truncate script is working as accepted but the insert script is throwing an error. Am i missing something.

The Linked server name is CDW. Could you please help on this.

Error :

This is in MS SQL version v 18.6.

SELECT * FROM OPENQUERY (CDW, 'TRUNCATE TABLE [CDW].[dbo].[TABLEA] SELECT ''OK''' )

INSERT INTO OPENQUERY (CDW, 'INSERT INTO CDW.dbo.TABLEA(
		[DATE],[DATE_IN_SQL_FORMAT]
      ,[WW]
      ,[C_WW]
      ,[AQUARTER]
      ,[CQUARTER]
      ,[YEAR]
      ,[AMONTH]
      ,[CMONTH]
      ,[MONTH_MNE]
      ,[DAY])')

--Server B	  
SELECT [DATE],[DATE_IN_SQL_FORMAT]
      ,[WW]
      ,[C_WW]
      ,[AQUARTER]
      ,[CQUARTER]
      ,[YEAR]
      ,[AMONTH]
      ,[CMONTH]
      ,[MONTH_MNE]
      ,[DAY

      FROM #CALENDAR10 A

Answer 1

You get an error, because you pass the remote server an INSERT statement without any VALUES or SELECT part. The fact that you have a SELECT on your local server is completely irrelevant.

I don't think you can do INSERT through OPENQUERY at all. Try this instead:

INSERT INTO CDW.CDW.dbo.TABLEA(
		[DATE],[DATE_IN_SQL_FORMAT]
      ,[WW]
      ,[C_WW]
      ,[AQUARTER]
      ,[CQUARTER]
      ,[YEAR]
      ,[AMONTH]
      ,[CMONTH]
      ,[MONTH_MNE]
      ,[DAY])')

--Server B	  
SELECT [DATE],[DATE_IN_SQL_FORMAT]
      ,[WW]
      ,[C_WW]
      ,[AQUARTER]
      ,[CQUARTER]
      ,[YEAR]
      ,[AMONTH]
      ,[CMONTH]
      ,[MONTH_MNE]
      ,[DAY]

      FROM #CALENDAR10 A

But don't be surprised if this explodes as well. It usually better to run the INSERT on the server of the target table.

Answer 2

Hi @kkran

Here is a sample:

INSERT INTO mytable (firstcolumn, secondcolumn)
SELECT firstcolumn, secondcolumn 
FROM OPENQUERY (mylinkedserver, 'SELECT firstcolumn, secondcolumn FROM linkedtable')

Best regards,

Cosmog Hong

Answer 3

INSERT INTO OPENQUERY (CDW, 'INSERT INTO CDW.dbo.TABLEA(
		[DATE],[DATE_IN_SQL_FORMAT]

You can not write an INSERT within the OPENQUERY statement, it must be a SELECT statement to address the table instead.

See B. Executing an INSERT pass-through query

INSERT OPENQUERY (Server, 'SELECT name FROM table')  
VALUES ('NewTitle');