Processing of primary foreign key relationships in SQL server databases when importing and exporting data and structures

2015-01-26

In software development, it often involves the data structure and the import and export of data between different databases (including different versions of different products). There are a lot of problems in the process, especially the important problem is that the master-slave table, from the table has external check constraints, resulting in some data can not be imported.

　　Scenario one, same database product, same version

In this case, the data structure of the source database and the target database and the import and export of the data are very simple.

Method 1: Back up the source database and complete the recovery to the target database.

Method 2: Use the "Copy Database" feature that comes with the SQL Sever database or the "Import data" feature to follow the wizard.

　　Scenario Two, the same database product, different versions

Scenario 1, Low source database version, high target database version

　　　　　　　　This situation is handled in the same way as scenario one.

Scenario 2, High source database version, low target database version

　　　　　　　　Because the target database version is lower than the source database, the script schema generated in the source database cannot be compatible with the lower version, so it cannot be manipulated by a direct backup restore.

This paper takes the SQL SERVER2008R2 database as the data source, SQL2008 Express as the target database to solve the problem of data import failure between master and slave tables, from the table with external check constraints. The operation process is divided into the following steps:

Step 1: Generate a data structure script from the source database " no package table with foreign key relationship "

On the data source 188 connection, right-click on the source database "task" "Generate script"

"Generate and Publish scripts" pops up

Click "Next" button to pop up the "Introduction" window

Click "Next" button to eject "Set scripting Options"

Click "Advanced" button to pop up the specific Settings window " This step is very important "

set the value of "write foreign key script" to False, It means that this step produces a data structure script that does not contain a foreign key relationship between tables . Other options are set according to the actual situation.

Click "OK" button, generate script, enter.

Save the script as "Originaldatastructurewithoutfk.sql".

Step 2: Import the data structure script to the target database

Create a new target database on the destination server, naming the same-origin database name (or other naming).

Select the newly created database, open the "originaldatastructurewithoutfk.sql" script file saved in step one, run the file, and successfully create tables, views, stored procedures, custom functions in the target database, such as

Step 3: Create a data script from the source database

This step, with the help of a third-party database plug-in sqlassistant, has powerful database extensions that are not detailed in this article. You can go to sqlassistant website to learn more http://www.softtreetech.com/isql.htm.

Select the source database, right-click, "Sql Assistant" "Scripts Data"

Popup "Table data Export" To export table window

The source database is selected by default with all tables. Click the "Export" button to generate a data script to the "New Query window"

Save the data script as "Originaldata.sql".

Step 4: Import the data script to the target database

The following processing is required for primary keys in a table or for other types set to int, and for columns that are set from the growth type:

SET IDENTITY_INSERT dbo. T_acl_user on;

If the general field is an identity, such as when the definition of NameID identity (first) to increase from 1, each add 1, then insert a record NameID field is not required to manually assign (generally also not allowed). So sometimes when you need to insert a custom value, set IDENTITY_INSERT on, and you can insert it manually. After the data is inserted, close it.

Select the target database and open the "originaldata.sql" data script that you saved in step 3, run it, view the datasheet after successful

The query results show that the data was successfully imported.

Set SET identity_insert dbo. T_acl_user Off;

Step 5: Generate a data structure script that contains only the table foreign key relationships from the source database

Step is approximately the same as Step 1, the last step is set to the opposite

Red box, set the Write foreign key script to True, and the other options are the opposite of those set in step 1. Click the "OK" button to generate the script and save it as "Originaldatastructureonlywithfk.sql".

Step 6: Import the foreign key structure relationship script to the target database

Select the target database, open the "originaldatastructureonlywithfk.sql" script file saved in step 5, run it, and view the table structure after successful operation

The foreign key has been successfully created.

The end of this article.

Technical Research direction: Focus on Web (MVC) Development Framework, WinForm Development Framework, Project (code) automation generator, ORM and other technology research and development applications Enterprise-Level Project experience: compilation management system, prepress management system, printing management system, book sales management system, book Distribution management system, library management system, Data exchange platform, ERP integrated management platform Welcome reprint, please indicate the source of the article and link information. If the article is helpful to you, please help recommend, thank you! Written by: Zhang Shuning Http://www.cnblogs.com/SavionZhang Welcome to join the Technical Exchange Group: 427789286

Next: To Be continued ...

Processing of primary foreign key relationships in SQL server databases when importing and exporting data and structures