如何在不同的数据库中解析SQL Server数据

核心提示： OrderNo OrderDate OrderNumberOrderDate1 2007-02-23 00:00:00.0001 2007-02-23 00:00:00.0002 2007-02-24 00:00:00.0002 2007-02-24 00:00:00.0002007-02

OrderNo 　OrderDate 　OrderNumberOrderDate

1 　2007-02-23 00:00:00.0001 　2007-02-23 00:00:00.000

2 　2007-02-24 00:00:00.0002 　2007-02-24 00:00:00.000

2007-02-24 00:00:00.000NULL NULL

2007-02-25 00:00:00.0003011 2007-02-25 00:00:00.000

假设有一些行存在于Order_1中而不存在于Order_2中。你将上面的查询翻转，它也能工作，然而，随后你会得到两个查询和两个结果集，并结束你的查询，这些你都必须手动比较。如果每个表只有四行，这是不困难的，但是想象一下如果有4,000行记录的话，怎么使用这种不切实际的方法进行比较呢。你必须对两个表的每行没出现在另外那张表的记录进行检查。

清单A

CREATE DATABASE [Test_Cross_1] ON PRIMARY ( NAME = N'Test_Cross_1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATATest_Cross_1.mdf', SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Test_Cross_1_log', FILENAME = N'C:aProgram FilesMicrosoft SQL ServerMSSQL.1MSSQLDATATest_Cross_1_log.ldf', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO

USE [Test_Cross_1]

GO

CREATE TABLE [dbo].[Orders_1]( 　　[OrderNumber] [int] NOT NULL, 　　[OrderDate] [datetime] NOT NULL, CONSTRAINT [PK_Orders_1] PRIMARY KEY CLUSTERED ( 　　[OrderNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

CREATE DATABASE [Test_Cross_2] ON PRIMARY ( NAME = N'Test_Cross_2', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATATest_Cross_2.mdf', SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Test_Cross_2_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATATest_Cross_2_log.ldf', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO

CREATE TABLE [dbo].[Orders_2]( 　　[OrderNo] [int] NOT NULL, 　　[OrderDate] [datetime] NOT NULL CONSTRAINT [DF_Orders_2_OrderDate] DEFAULT (getdate()), CONSTRAINT [PK_Orders_2] PRIMARY KEY CLUSTERED ( 　　[OrderNo] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

清单B

SELECT * FROM Test_Cross_1.dbo.Orders_1INNER JOIN Test_Cross_2.dbo.Orders_2

ON Test_Cross_1.dbo.Orders_1.OrderNumber

清单C

SELECT * FROM Test_Cross_1.dbo.Orders_1LEFT OUTER JOIN Test_Cross_2.dbo.Orders_2

ON Test_Cross_1.dbo.Orders_1.OrderNumber

清单D

SELECT * FROM Test_Cross_2.dbo.Orders_2LEFT OUTER JOIN Test_Cross_1.dbo.Orders_1

ON Test_Cross_2.dbo.Orders_2.OrderNo =