Import and export data between SQL Server databases

Document directory

• (1). Use select into to export data

Import and export data between SQL Server databases (1). Use select into to export data

The most widely used SQL Server is to export data using the select into statement. The select into statement has two functions at the same time: create an empty table based on the fields followed by the SELECT statement and the table names followed by the into Statement (if the field is * after the SELECT statement, the structure of the empty table is the same as that of the table referred to by the from statement ); insert the data from the SELECT statement to this empty table. When the select into statement is used, the table after into must not exist in the database; otherwise, an error occurs. The following is an example of using select.
Assume that there is a table Table1 with the fields F1 (INT) and F2 (varchar (50 )).

Select * into Table2 from Table1

After creating a table 2 table, this SQL statement inserts all Table 1 data into Table 1. You can also change * to F1 or F2 to insert data to an appropriate field.
Select into can not only create tables in the same data, but also create tables in different SQL Server databases.

Use db1
Select * into db2.dbo. Table2 from Table1

The preceding statement creates Table 2 with the DBO owner in the database DB2. When creating a table to DB2, the user currently logged on to the database must have the permission to create a table in DB2.
Table2. When using select into, note that select
Into cannot be used with compute because compute returns a set of records, which will lead to two meanings (that is, it does not know which table to create an empty table ).

(2) Insert and update data using insert into and update

Select into can only copy data to one empty table, while insert into can insert data from one table or view to another table.

Insertinto Table1 select * From Table2

Or

Insertinto db2.dbo. Table1 select * From Table2

However, the preceding insert into statement may generate a primary key conflict error (if a field in table 1 is a primary key, it happens that the value of this field in table 2 is the same as the value of this field in table 1 ). Therefore, the preceding statement can be modified

Insertinto Table1 -- assume that field F1 is the primary key.
Select * From Table2 where
Notexists (select table1.f1 from Table1 where table1.f1 = table2.f1)

The preceding statement inserts records that do not exist in table 1 in Table 2 into table 1.

You can use the update statement to update table 1.

Update Table1 set table1.f1 = table2.f1, table1.f2 = table2.f2 from Table2
Where table1.f1 = table2.f1

Insert
When the into and update statements are combined and run together, you can insert and update records if they do not exist in Table 1. However, you must place the update statement in
Insert into. Otherwise, the number of update records is the total number of records in Table1 and Table2.