Ms SQL Remote Data Import and Export

1. Export data to the local database (this operation is applicable to databases with large data volumes. For databases with small data volumes, you can perform Step 2 in step 2)
1. Right-click the database to be exported and choose "all tasks"> "generate SQL script"> "generate SQL script ".

The parameters in the create SQL script dialog box are set as follows:

A) Click the "show all" button of "regular", and then click the check box before "write all object scripts ".

B) Click "options" to go to the "options" tab, and then select all options in "Table script options" on this tab (compile the index script, write the full-text index script, and write the trigger script, write the primary key, foreign key, default value, and check constraint script). At this point, the SQL script is generated. Click OK to generate the SQL script

2. Open the generated SQL script. You can see that some table creation statements are followed by "TEXTIMAGE_ON [PRIMARY]

"Code, this part of code may encounter errors when running in the query analyzer. The error prompt is:" TEXTIMAGE_ON cannot be used when the table does not have text, ntext, or image columns ."

The following code creates a table:

Create table [dbo]. [commodity] (

[Id] [int] IDENTITY (1, 1) not null,

[Name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL,

[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,

[Addtime] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Solution: My solution is to set all "TEXTIMAGE_ON [PRIMARY]

"Replaced with" "(null). After modification, you can run the query analyzer. However, I don't know if this solution will bring any side effects. I hope you can get some advice!

3. Create a database in the local database, select the database you just created in the query analyzer, and run the modified SQL script (the remote table structure is directed to the local database ).

4. Right-click the database you just created-> all tasks-> import data (or right-click the database you want to export remotely-> all tasks-> export data, at this time, the source database is opposite to the target database) and then fill in the identity authentication for the source database and the target database as prompted (other settings are set by default) go to the next step and select all the tables (the view is not selected) when "select source table and view" and then click Next until the data is exported locally.

Ii. import data to a remote database

1. Import the table structure to the remote database: Create a new table in the remote database, right-click the created table and choose all tasks> import data (or right-click the table to be imported locally> all tasks> export data, at this time, the source database is opposite to the target database) and then fill in the identity authentication for the source database and the target database as prompted (other settings are set by default) then, go to the next step and select "copy objects and data between SQL Server databases" when "specify the table to restore or query" and then click Next until the operation is completed (this operation can only export a database with a small amount of data or table structure, to separate the table structure, see the first three steps in step 1)

2. import data to a remote database: see"I"Step 2