2

I'm trying to figure out how to create a copy of a table from one linked server to another inside Management Studio. I have both linked servers created, and I can query them. However, one is a SQL Server instance and the other is a ODBC connection to a QuickBooks QODBC connection.

When querying the SQL Server instance I run a query like this

SELECT * 
FROM [MYSERVERNAME\SQLSERVICEINSTANCE].[DATABASENAME].[DBO].[TABLENAME]

When querying the QODBC QuickBooks database I run a query like this

SELECT *
FROM OPENQUERY(QUICKBOOKS, 'SELECT * FROM Invoice')

How can I select * into SQLSERVER.Invoices FROM QUICKBOOKS.Invoices?

Phil
  • 4,029
  • 9
  • 62
  • 107

1 Answers1

2

select * into SQLSERVER.Invoices FROM QUICKBOOKS.Invoices is actually pretty close to what you want. 

insert into [DATABASENAME].[DBO].[TABLENAME] 
(column1, columns2)
SELECT column1, columns2
FROM OPENQUERY(QUICKBOOKS, 'SELECT * FROM Invoice')

Now I guess you want to do this regularly? The most straightforward way is to to run this first to clear the target table:

 TRUNCATE TABLE [DATABASENAME].[DBO].[TABLENAME]

If you want to make an exact copy use this:

USE [DATABASENAME]


IF EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id =     OBJECT_ID(N'[DBO].[TABLENAME] ') 
    AND type in (N'U')
    )
DROP TABLE [DBO].[TABLENAME]


SELECT *
into [DBO].[TABLENAME] 
FROM OPENQUERY(QUICKBOOKS, 'SELECT * FROM Invoice')

You can pop any of these scripts into a sql agent job to refresh on a regular basis

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • that's exactly what I want to do, but I won't want to have to specify the column names. In case a new column gets added in QuickBooks I want to simply perform a INSERT INTO SELECT * FROM – Phil Feb 01 '17 at 13:40
  • in essence, I want to create an exact copy of the table from QuickBooks into my SQL Server database, but without having to specify each and every column – Phil Feb 01 '17 at 19:25