CODEWITHSUNDEEP
sql-serverdb2dbeaver
cardinalsfan
cardinalsfan

Reputation: 87

Move data between different servers

I'm working on a project where I need to automatically run an insert statement to insert a result set - problem is that I need it to go from a SQL Server over to a DB2 server. I can't create a file or script and then import it or run it on the other side. I need to insert or update the DB2 side from the SQL Server side.

Is this possible? I need this to run all by itself as part of a stored procedure in SQL Server.

Upvotes: 0

Views: 1179

Answers (2)

userfl89
userfl89

Reputation: 4790

As mentioned you can use a linked server on the SQL Server side to perform operations between two servers. I haven't done much with running DML on DB2 from SQL Server, but from my experience SSIS performs far better than linked servers for transactions pulling data from DB2 to SQL Server using an OLE DB connection. You can read more about OLE DB connections in SSIS here and you'll want to reference the DB2 documentation for the specific DB2 type (Mainframe, LUW, etc.) that's used for details on setting up the connection there. If you setup the SSIS catalog you can run packages using SQL Server stored procedures, which you can either use directly or execute from an existing user stored procedures.

Upvotes: 1

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

You're looking for the linked server feature.

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

  • The ability to access data from outside of SQL Server.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

(I believe most of the major RDBMSs have a similar feature)

For the most part, this essentially allows you to treat tables or sources in the other database as if they were part of the SQL Server instance - an INSERT statement should just work "normally".

Upvotes: 1

Related Questions