How can you fetch the data from different types of server (oracle/ big data/Teradata) to SQL server, within a procedure?

In Server there is a system function named: OPENQUERY () which supports you to fetch the data from different servers. For using this we need to follow the below steps:

1. Create a Linked server connection in SQL server with all the details such as:

  EXEC [dbo].[CreateStdLinkedServer]

 @LinkedServerName = ‘Link_server_name+ addtype’,

 @ServerProduct = 'Oracle/ big data/Teradata ',

 @DataSource = 'Database name/service name',

 @IdentityForwarding = 'FALSE',

 @Username = 'User_ name to connect the server',

 @Password = 'password of the user',

 @debug = 1;

2. Now you can connect using the open query with the syntax: OPENQUERY (linked servername, query with the schema etc.)

Sample:

SELECT * FROM OPENQUERY([LinkedServerName],'SELECT * from schema_name.table_name')