Parsing SQL Server data is applied in different databases

Source: Internet
Author: User
Tags datetime join log sql null null one table query create database

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



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.