Diagram of Mssql backup and import into MSSQL database

Source: Internet
Author: User
Tags datetime mssql

Example 1. Back up and import the MSSQL database

This method is suitable for friends who do not frequently perform database backup and restoration operations.

I. Database connection

1. Right-click "SQL Server Group" and click "create SQL Server registration", as shown in the figure below:

 

2. Enter the IP address of the database you purchased, and click "add" and "next"

 

3. Click "(SQL Server Authentication)" and "next"

 

4. Enter your database username and password (entered at the time of purchase) and click "next"

 

5. Click "next"

 

6. Click "finish"

 

7. Database connection successful

 

2. Browsing databases

1. Database operations are the same as local operations, as shown in the figure below:



 

III. Database connection

1. Right-click your database name and click "all tasks" and "export data", as shown in the following figure:

 

2. Click "next"

 

3. Click "next"

 

4. Enter the Server IP address, click "use SQL Server Authentication", enter the user name and password, select the database, and click "next"

 

5. Click "next"

 

6. Click the name of the table to be exported and click "next"

 

7. Click "next"

 

8. Click "next"



 

9. Data exported successfully


Example 2: Import or overwrite data

Company A uses the SQL Server 2005 database. Customer data from trading partners is imported to the customer table every night. Make sure that existing customer data is updated during the import process and non-existing customer data is inserted.

 

The code is as follows: Copy code
Create table Person
   (
SSN char (11) primary key,
Name nvarchar (100 ),
Address nvarchar (100 ),
Birthdate datetime
   )
 
Create table EmployeeTable
   (
EmployeeID int primary key,
SSN char (11) UNIQUE,
Department nvarchar (10 ),
Salary money,
CONSTRAINT FKEmpPer foreign key (SSN)
REFERENCES Person (SSN)
   )
 
-- The following View uses all the relevant data in two tables of a person to create a report:
 
Create view Employee
Select p. SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
Where p. SSN = E. SSN
 
-- Record attempts to insert rows with duplicate Social Security numbers. In the PersonDuplicates table, record the inserted value, user name of the user attempting the insert operation, and insertion time:
 
Create table PersonDuplicates
   (
SSN char (11 ),
Name nvarchar (100 ),
Address nvarchar (100 ),
Birthdate datetime,
InsertSNAME nchar (100 ),
WhenInserted datetime
   )
 
-- Instead of trigger inserts rows into multiple base tables in a separate view. Record attempts to insert rows with duplicate Social Security numbers in the PersonDuplicates table. Change the repeated rows in EmployeeTable to update statements.
 
Create trigger IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (not exists (select p. SSN
FROM Person P, inserted I
Where p. SSN = I. SSN ))
Insert into Person
Select ssn, Name, Address, Birthdate, Comment
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
Insert into PersonDuplicates
Select ssn, Name, Address, Birthdate, SUSER_SNAME (), GETDATE ()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (not exists (select e. SSN
FROM EmployeeTable E, inserted
Where e. SSN = inserted. SSN ))
Insert into EmployeeTable
SELECT EmployeeID, SSN, Department, Salary, Comment
FROM inserted
ELSE
-- If duplicate, change to UPDATE so that there will not
-- Be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I. EmployeeID,
Department = I. Department,
Salary = I. Salary,
Comment = I. Comment
FROM EmployeeTable E, inserted I
Where e. SSN = I. SSN
END

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.