Parsing SQL Server data is applied in different databases

In the initial stages of software development, developers always want to design the smallest details of the whole system and then write the code in a single thread. It takes a long time for software to develop, but developers have been doing it.

So developers have to shrink their ambitions by designing a small system, but this small system is only part of the puzzle of the whole system. This leads to many small systems that are designed and created by different teams that are hardly compatible with each other.

Currently, many organizations employ database modelers or DBAs who oversee database design and development. Unfortunately, those organizations will only be aware of the need for such people in their employees when certain conditions arise.

A very common problem faced by data modelers and DBAs is how to parse SQL data in different databases. This article will use a reliable method to illustrate this problem.

An instance

In this instance scenario, there are two databases that contain similar data: one using the term OrderNumber and the other using the term OrderNo. The first database has a keyword and the second database does not.

First, you'll find in two databases that their order is similar. Listing a creates two databases (Test_cross_1 and test_cross_2) with one table in each database (Orders_1 and orders_2, respectively).

Suppose Orders_1 includes these lines:

The following are the referenced contents: Ordernumberorderdate 2007-02-23 00:00:00.000 2007-02-24 00:00:00.000 2007-02-25 00:00:00.000 Suppose orders_2 includes these lines: OrderNo OrderDate 2007-02-23 00:00:00.000 2 2007-02-24 00:00:00.000 2007-02-24 00:00:00.000 3011 2007-02-25 00:00:00.000

Once you understand how to reference table SQL to perform a merge operation is easy. In short, you have to have a legal name. As shown in Listing B. This will not succeed because order_2 includes a row of order_1 data. Changing the connection and joining the external connector will not be successful, as you can see in Listing C. The second query gets the same result as the first query because OrderNo 301 does not exist in the first table. To find this line, you must reverse the order of the table in the second query. As shown in Listing D. Now you can find the line that doesn't match.

The following are the referenced contents: 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.000 NULL NULL 2007-02-25 00:00:00.0003 011 2007-02-25 00:00:00.000

Suppose there are some rows in the order_1 and not in the order_2. You flip the above query and it works, however, then you get two queries and two result sets and end your query, which you have to manually compare. This is not difficult if you have four rows per table, but imagine how you could use this impractical method to compare if you had 4,000 rows of records. You must check the records for each of the two tables that do not appear on the other table.

The following are the referenced contents: 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]

Listing a

The following are the referenced contents: SELECT * from Test_cross_1.dbo. Orders_1inner JOIN test_cross_2.dbo. Orders_2 On Test_cross_1.dbo. Orders_1.ordernumber

Listing b

The following are the referenced contents: SELECT * from Test_cross_1.dbo. Orders_1left OUTER JOIN test_cross_2.dbo. Orders_2 On Test_cross_1.dbo. Orders_1.ordernumber

Listing C

The following are the referenced contents: SELECT * from Test_cross_2.dbo. Orders_2left OUTER JOIN test_cross_1.dbo. Orders_1 On Test_cross_2.dbo. Orders_2.orderno

Listing D