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 |