Can you write a query from a database server that creates a table in a different database server?

Transact-SQL
1

Hi all

I am trying to create a table in server B, from data collected through a query ran in server A is that possible? For instance, in server A, I run a query that produces the data I want. From there I want the data created from the query to become a table in server B.

2

Not directly - you need to have some way of moving that data. There are multiple techniques available - but which will be the better option all depends on your requirements.

For example - if this is a one time operation and there are less than a 1000 rows to be copied, you could just create a script that generates insert statements for the data and copy that script to a query window connected to the other server and run it. Or you can use the import/export wizard in SSMS to extract and load the data - or even switch SSMS to output to a CSV file and import the file - or use BCP to export/import the data.

If you have a linked server created - you can do it directly with 4-part naming.

For a repeatable process that refreshes that table on a set basis, then you can use SSIS or BCP or Powershell or linked server or any other tool that allows you to connect to a database.

There are also DACPAC/BACPAC options available - again, depends on your process requirements.

And before you ask - none of these options are necessarily better than the other because it all depends on what works best for you and can be supported, managed and maintained in your environment.

2 Likes
3

Thanks Jeff

I think creating a CSV file is the best route, however, the output has 3M lines so i tried to save it as a CSV, but for whatever reason its not including the headers.

4

no way to get around that when saving from SSMS. How were you saving it?

5

Hi Yosiasz

I m doing right click "save results as" then saving it as a csv

6

Now I am trying the BULK INSERT function but keep getting a syntax error. This is what I am using:

BULK INSERT DBO.[Payor Info]
from 'H:\ID&E Race Test\Payor Info2.csv'
with (
    FORMAT = 'CSV',
    FIRSTROW = 1,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO

This the error I am getting:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'FORMAT'.
7

what version of SQL Server are you on?

8

Microsoft SQL Server Management Studio 17, however, I just did it through the import wizard and am good to go...thanks for the help

9

That is the SSMS version - the SQL Server you are connecting to is probably 2016 or lower (or compatibility is set to a lower version) where that version of bulk insert doesn't exist.

10

For a one-time operation, the import/export wizard is generally good enough. However, do be aware that the data types it uses for each column may not be accurate. It may not matter though, all depends on what you need to do with that data.