none
how to combine two different SQL server data instance into one db?

    Question

  • Hi

    I'd like to know how I can combine two tables from two different sql instances ito one db?

    thanks.

    Monday, January 08, 2007 11:00 PM

Answers

  • If both the DBs are on same Server and the current user have access permission on both database then you can use the following query..

    Select SomeColumns From CurrentDBName..TableName
    Select SomeColumns From OtherDBName..TableName

    If the databases are on different Server then you have to use the Linked Server..

    EXEC sp_addlinkedserver  @server = 'SERVER', @provider = 'SQLOLEDB.1', @srvproduct = '', @provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

    go
    Exec sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @useself = true, @locallogin = null, @rmtuser = 'Userid', @rmtpassword = 'Password'


    On your SP you can use..

    Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

    --OR

    Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

    Tuesday, January 09, 2007 6:38 AM

All replies

  • Volkan, can you provide a little more info?

    Are you trying move the data from sql instance 1 and sql instance 2 into a new database on sql instance 3?

    Are you asking how you can query the data from two different instances?

    You can query separate instances by using a four-part qualifier if the instances are defined as linked servers.

    select * from [servername].[databasename].[schema].[tablename]

    Example: server = MySQL1, database = MyDatabase, schema = dbo, table = MyTable

    select * from MySQL1.MyDatabase.dbo.MyTable

     

    Tuesday, January 09, 2007 12:08 AM
  • If both the DBs are on same Server and the current user have access permission on both database then you can use the following query..

    Select SomeColumns From CurrentDBName..TableName
    Select SomeColumns From OtherDBName..TableName

    If the databases are on different Server then you have to use the Linked Server..

    EXEC sp_addlinkedserver  @server = 'SERVER', @provider = 'SQLOLEDB.1', @srvproduct = '', @provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

    go
    Exec sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @useself = true, @locallogin = null, @rmtuser = 'Userid', @rmtpassword = 'Password'


    On your SP you can use..

    Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

    --OR

    Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

    Tuesday, January 09, 2007 6:38 AM
  • Thanks a lot ManiD.

    I found what I'm looking for....

    Have a good day!

    Kido
    Wednesday, June 25, 2008 3:10 PM
  • Thank you, Mani.
    I need one further info.

    We are trying to combine two table instances as mentioned above, however both the instances might have same data in the specific field which is unique to its own data.
    However the data in the individual tables are necessarily different. On combining, the data might get over-written, which we do not want.

    is it possble to append the data in the fields with a character before the data, so as to identify the table it comes from?

    Thanks in advance
    Srivats
    Wednesday, October 21, 2009 11:04 AM
  • You have to first create the linked server for both these database and then can use JOIN statement to fetch the records.
    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India
    Wednesday, October 21, 2009 12:46 PM