How to Upload Database in Sql Server 2016
Nosotros quite ofttimes confront the task of migrating database directory information. And the database for a recruitment service, which we created in the previous series of articles, is no exception. In this article, we are going to demonstrate how you can export data to a .sql file and so import information technology to the target database. Is information technology worth mentioning that you tin can either migrate the whole database or certain objects depending on your purpose.
To start with, y'all should know that this issue can be solved in a bully many ways, including the following:
- With dbForge Data Compare for SQL Server.
- Using data export tools embedded in SSMS.
- Via SSIS package implementation.
However, most of the solutions mentioned above are also cumbersome when information technology comes to migrating 1 or several tables.
Hence, in this commodity, nosotros are going to have a look at the Data Pump solution, which is also a function of dbForge Studio for SQL Server. This tool is sure to facilitate information import and export as it offers advanced options, templates, and a number of widely used data formats for both export and import.
Yous can discover a stride-by-step guide to designing a database for a recruitment service right here.
Fig.i. The database schema for a recruitment service
How to export SQL Server information to a SQL script
- Select data export on the database level
And then, let's become started! Select the required database and correct-click the necessary table (if you need to export a specific tabular array) or the database (if yous need to export several tables) and select "Export Data":
- Select data export on the tabular array level
- Select the export format
Next, on the "Export format" tab, you can choose from different formats of data export. We choose SQL scripts and press "Next":
- Select data to export
Now, on the "Source" tab, select the tables to export data from and press "Next".
In this case, we select 3 directories:
1. "Company" is a list of companies.
2. "Position" is the listing of positions.
3. "Skill" is a listing of skills.
Notation that it is possible to change the connection and the database whenever required.
- Select the type of script generation
Next, on the "Options" tab, select the blazon of script generation for data export and select if you need to include the database proper noun in the script. Then, click "Side by side".
Note that the window suggests iv types of script generation for data export:
1. INSERT. The script for inserting data volition exist generated.
ii. UPDATE. The script for updating data will be generated, that is, the matching cardinal fields will be found and the update will exist performed.
three. DELETE. The script for deleting information volition be generated, that is, all data that matches the exported information by primal fields on the target database side will be deleted.
4. MERGE. The script for merging data volition be generated, it will include the first two types: insert and update.
- Select columns and central fields for export
At present, on the "Table columns" tab, you need to select the required columns and fundamental fields for consign (by default we select all columns for export, and the key fields lucifer principal keys' definitions of the respective tables). Then, click "Side by side":
- Select information to be exported
Post-obit that, on the "Exported rows" tab, select which data to export and press "Next".
Note that you tin can select all rows as well as an exact range of rows for data export.
- Fix errors handling tab
Additionally, you can configure errors handling parameters on the "Errors treatment tab".
Annotation that users often select the "Write a report to a log file" option when they need to analyze the report results. Only to brand information technology simple, leave the default options and click "Export" to start exporting data.
- Cease the export
When the export is complete, yous can either click "Finish" or open the folder with generated scripts past pressing the "Open issue folder.." button:
- View the scripts
As a issue, at that place will be 3 scripts generated for each directory table:
The T-SQL script is going to expect the post-obit way:
SET DATEFORMAT ymd Set ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF Get SET IDENTITY_INSERT JobEmplDB.dbo.Skill ON GO INSERT JobEmplDB.dbo.Skill(SkillID, SkillName) VALUES (689, N'C#') ... INSERT JobEmplDB.dbo.Skill(SkillID, SkillName) VALUES (14, Northward'SQL') GO SET IDENTITY_INSERT JobEmplDB.dbo.Skill OFF Get You demand to apply the generated scripts to the necessary target database.
But what if the data was saved in a unlike format?
For that purpose, there is data import, which yous tin open by correct-clicking the database or the desired tabular array:
Fig.12. Selecting information import on the database level
Fig.thirteen. Selecting data import on the tabular array level
Keep going in a similar way as we take performed data export. Also, visit Documentation center to find out more nearly how to import data from a CSV file. For reference, CSV is a compact text format that is used for storing tabular data. Too as that, it is a very common format as most modern spreadsheet applications (like Excel) tin can work with files in CSV format (consign/import data).
That'southward all for now, folks. To summarize, this time nosotros take looked upon information import and consign to a SQL file using a highly customizable solution from dbForge Studio for SQL Server.
- Author
- Recent Posts
Evgeniy is an MS SQL Server database analyst, developer, and ambassador. He is involved in the development and testing of the SQL Server database management tools. Evgeniy also writes SQL Server-related articles.
Source: https://blog.devart.com/how-to-export-and-import-sql-server-database-data-to-a-sql-script.html
0 Response to "How to Upload Database in Sql Server 2016"
Postar um comentário