如何UNION不同数据库的两个表

Question

t1和s1不在同一数据库中
ConnectionStrings写哪一个的
        
         using (SqlConnection conn = new SqlConnection
                 (ConfigurationManager.ConnectionStrings["t1_conn"].ConnectionString))
        {
           
           SqlCommand comm = new SqlCommand(@"
                                          SELECT name, id FROM t1
                                          UNION
                                          SELECT name, id FROM s1
                                         ", conn)
           //... 
        }

Answer 1

using (SqlConnection conn = new SqlConnection
                 (ConfigurationManager.ConnectionStrings["t1_conn"].ConnectionString))
        {
           
           SqlCommand comm = new SqlCommand(@"
                                          SELECT name, id FROM db1.dbo.t1
                                          UNION
                                          SELECT name, id FROM db2.dbo.s1
                                         ", conn)
           //... 
        }

Answer 2

SELECT name, id FROM db1.dbo.t1
                                          UNION --要不要加All你看着办
                                          SELECT name, id FROM db2.dbo.s1要有访问权限.

Answer 3

use pubs
goselect name, id from master.dbo.sysobjects
union all
select name, id from sysobjects
在對象名前加數據庫名, 所有者

Answer 4

前后两个Select后面的字段一定要是相同的！

Answer 5

using (SqlConnection conn = new SqlConnection
                 (ConfigurationManager.ConnectionStrings["t1_conn"].ConnectionString))
        {
           
           SqlCommand comm = new SqlCommand(@"
                                          SELECT name, id FROM t1
                                          UNION
                                          SELECT name, id FROM 库2..s1
                                         ", conn)
           //... 
        }
也可以用   库..表名来调用
没有的字段可以用   null as 字段 来表示

Answer 6

using (SqlConnection conn = new SqlConnection
                 (ConfigurationManager.ConnectionStrings["t1_conn"].ConnectionString))
        {
           
           SqlCommand comm = new SqlCommand(@"
                                          SELECT name, id FROM t1
                                          UNION
                                          SELECT name, id FROM [表s1所在库名]..s1
                                         ", conn)
           //... 
        }

Answer 7

select name, id from 服務器1.master.dbo.sysobjects
union all
select name, id from 服務器2.master.dbo.sysobjects

Answer 8

sp_addlinkserver 然后就像在一台服务器上访问一样.用于频繁访问
或者openrowset.用于偶尔访问.