2 Tables 2 Servers

Other SQL Server Topics
1

I've searched and searched and am at a loss here. It seems like something so simple I'm trying to do. Please keep in mind that I'm still very green in the world of SQL. Here is what I'm trying to accomplish.

I have Table1 sitting on a database on Server1. I'm trying to run a query to grab data on Table2 on Server2 and insert that pulled data into Table1 on Server1. I have a connection to the servers done through SQL Developer. A quick sample of what I've tried.....

GRANT INSERT ON Server1.Table1 TO Server2.Table2
SELECT data
INSERT INTO Table1
FROM Table 2
WHERE criteria

If I just run it to a temp table on Server2, it will work. But I need the data I'm pulling to be housed in Server1. Any suggestions?

2
  1. Use the Import Wizard to import the data.
  2. Select delete data in the mapping dialog.
  3. Save to the file system.
  4. Create a new SSIS project in SSDT.
  5. Delete the default Package.
  6. Right click Packages and select Add Existing Package.
  7. Navigate to your saved package created in the Import Wizard. Run to test.
  8. Deploy to your SQL Server.
  9. Create a Job to execute it.
3

Hi

There is concept of LINKED Servers

Please google

If you need help COME online lets chat

Thanks

1 Like
4

If you're running this just the once, follow JoTorre's advice. If this will be an ongoing event, then create a linked server per HarishGG1, but be aware that you'll need to use a four part name to reference the external object on Server2. The four parts would be ServerName.DatabaseName.SchemaName.ObjectName. So your insert would look something like:

INSERT INTO Table1
SELECT data
FROM Server2.MyDatabase.dbo.Table2 -- I'm assuming the database and schema names
WHERE criteria

1 Like
5

There is another way to do that, and it can run the query on the remote server. This is important if you need to make joins. REMOTEQUERY?