SQL Server big data import and export: import data from one table to another, import and export data import

SQL Server big data import and export: import data from one table to another, import and export data import

At the end of this afternoon, I was thrown to another task: to import the sub-district data from all over the country into the sub-district table. However, the SQL scripts they threw to me are down from the Internet, which is totally different from the database design on the platform side.

At that time, the idea was: first generate a table from the scripts that were thrown to me, then select the desired data, and insert them to the tables on the server where the development library is located in batches.

Then, we conducted a test based on this idea:

Insert into dbo. test_Street (Code, CountyCode, CnName, VersionNo, Creator, CreateTime, ValidStatus)/* Table to be inserted */select newid () AS Code, id AS CountryCode, name AS CnName, 1 AS VersionNo, 'lhc ', GETDATE () AS CreateTime, 1 AS ValidStatus FROM pre_common_district/* data source table */

Then, use a Bad script to successfully import more than pieces of data, and then import the tables in the local database to the development database.

Here, we first use the bcp command to export data, and then use Bulk Insert to Insert data to the Development Library:

Run the following command:

Bcp "kfglxt. dbo. test_Street" out f: \ test.txt-c-q-S "ZB-LIUHCH-PC \ SQLEXPRESS"-T

Bcp "kfglxt. dbo. test_Street" out f: \ test. Dat-c-q-S "ZB-LIUHCH-PC \ SQLEXPRESS"-T (yes, other formats are also supported)

It seems that more than pieces of data are still minutes long.

In addition to executing with cmd, you can also directly execute in the query Analyzer:

EXEC kfglxt .. xp_cmdshell '/* enter the bcp command */'

Next, we will import the data backed up into a file to the development database:

 USE kfglxt BULK INSERT dbo.test_Street    FROM 'f:\test.txt'    WITH (     FIELDTERMINATOR = '\t',                 ROWTERMINATOR = '\n'       ); 

It seems like a lot of pitfalls...