How to retrieve the records of the table from database to another database

Question

I have two tables with different database:

AccntID from table 'account' <--database name 'ECPNWEB'
AccountTID from table 'tblPolicy' <--database name 'GENESIS'

Now I want to insert 'tblPolicy' like this: <--database 'GENESIS'

  INSERT INTO dbo.tblPolicy
      ( 

        PolicyID                 ,
        AccountTID               ,
        DistributorID            ,
        CARDNAME                 ,
        DENOMINATION             ,
        RETAILPRICE              ,
        COSTPAYABLE              ,
        ECPAYFEES                ,
        PLUCODE 

      ) 
-- Insert statements for procedure here


select t.* from
(Select  AccountTID=@AccntID, DistributorID=@DistributorID, CARDNAME=@CARDNAME, DENOMINATION=@DENOMINATION, RETAILPRICE=@RETAILPRICE, COSTPAYABLE=@COSTPAYABLE, ECPAYFEES=@ECPAYFEES, PLUCODE=@PLUCODE) t,
account a
where a.AccntID = t.AccountTID --for account

Now what I want to do is to insert this "ONLY" to tblPolicy connected with the 'account' table with different database 'GENESIS'

score 1 · Answer 1 · answered Oct 25 '12 at 01:36

1

You can select from two databases as shown below:

SELECT table1.SomeField, table2.SomeField
FROM   [ServerName1].[Database1].[dbo].[Table1] table1
       INNER JOIN [ServerName2].[Database2].[dbo].[Table2] table2
       ON table1.SomeField = table2.SomeField

answered Oct 25 '12 at 01:36

Yaqub Ahmad

27,569
23
102
149

how can I get the value of the table account to table tblPolicy? @Yaqub Ahmad – Kristian Hernan C. Manuel Oct 25 '12 at 02:37
1

@Kristian Hernan C. Manuel , the key point is [ServerName].[DatabaseName].[databaseowner].[tableName] – Niladri Biswas Oct 25 '12 at 03:52
Can you query `account` table? like : SELECT * FROM [ServerName].[Database].[dbo].[account] – Yaqub Ahmad Oct 25 '12 at 06:47
@Yaqub Ahmad Yes I query it and it works fine.,and know I receiving another eror.An invalid schema or catalog was specified for the provider "Local Server" for linked server "(null)". – Kristian Hernan C. Manuel Oct 25 '12 at 07:16
oops something wrong with your linked server!! my suggestion to post another question about this.. – Yaqub Ahmad Oct 25 '12 at 07:21

score 0 · Accepted Answer · answered Oct 25 '12 at 03:52

0

The key point is [ServerName].[DatabaseName].[databaseowner].[tableName]..i.e. Fully qualified name

This should work

;With Cte As
(
    Select AccountTID=@AccntID
    , DistributorID=@DistributorID
    , CARDNAME=@CARDNAME
    , DENOMINATION=@DENOMINATION
    , RETAILPRICE=@RETAILPRICE
    , COSTPAYABLE=@COSTPAYABLE
    , ECPAYFEES=@ECPAYFEES
    , PLUCODE=@PLUCODE
)

INSERT INTO GENESIS..dbo.tblPolicy
  ( 
    PolicyID                 ,
    AccountTID               ,
    DistributorID            ,
    CARDNAME                 ,
    DENOMINATION             ,
    RETAILPRICE              ,
    COSTPAYABLE              ,
    ECPAYFEES                ,
    PLUCODE 

  ) 

Select t.*
From Cte t,ECPNWEB..account a WITH (NOLOCK) 
where a.AccntID = t.AccountTID

answered Oct 25 '12 at 03:52

Niladri Biswas

4,153
2
17
24

Thanks for the Effort but How can I get the value of table account <--database name 'ECPNWEB' – Kristian Hernan C. Manuel Oct 25 '12 at 04:13
This is the error I receive Could not find server 'GENESIS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. – Kristian Hernan C. Manuel Oct 25 '12 at 04:16
@Kristian Hernan C. Manuel are not you r getting the value if u do select top 10 * from ECPNWEB..account a WITH (NOLOCK) – Niladri Biswas Oct 25 '12 at 04:34
@Kristian Hernan C. Manuel for the error, please check this http://stackoverflow.com/questions/10226692/to-add-server-using-sp-addlinkedserver – Niladri Biswas Oct 25 '12 at 04:37
this error occured when I execute the program. An invalid schema or catalog was specified for the provider "Local Server" for linked server "(null)" – Kristian Hernan C. Manuel Oct 25 '12 at 07:11

How to retrieve the records of the table from database to another database

2 Answers2