SQL copies data across servers (or another instance name on the local machine)

This is the case: when developing a website, the database uses sql2005 because the server where the website to be released is not sql2005, and only SQL2000 is used, you can put the database on a machine with sql2005 and connect it remotely, but it will not be easy to maintain in the future.

The database of sql2005 has the same structure as the database of SQL2000, but I only need to copy a specific table of sql2005 to SQL2000 (not a simple SQL 2005 to SQL2000, but a table ).

 

At first, I did not expect cross-server (on my computer, it is a cross-instance ). So the first method I used was to export sql2005 to excel and then import it to sql2000. I will post the second method later.

Method 1:

Step 1: Create an Excel file named table.xls on the table. The file cannot be empty and you need to fill in the column name, because I do not know how many columns are in the column name of the table to be copied. I want to query the number of columns, then, 1, 2, 3 until the number of columns is the same. Unfortunately, I have not found this statement. So let's take a look at Step 2.

Step 2: query statement: Select top 1 * from table name to show the result of the selection to copy to table.xls. OK. copy the file and paste it to the xls file. Remember to select the text mode. Otherwise, an error occurs, as shown in the figure:

Remember: select the text mode. My graph is not marked.

Then, clear the remaining column names. OK

Step 3: Execute the SQL statement:

Insert
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; database = c: \ Documents and Settings \ Administrator \ Desktop \ table.xls ', [sheet1 $])
Select * From nt_news_class

 

OK. Next, import. I will not talk about it here. I am interested in reading my other article SQL import Excel experiment.

 

Method 2:

Executed in SQL2000

Insert into table name 1 (SQL2000)
Select * From OpenRowSet ('sqloledb ','. \ sql2005 (remote server name or Instance name) '; 'sa (account)'; '2014 (password) ', database. DBO. table name)

This execution is definitely not feasible. Why? Because a column is automatically increasing, what should I do? I read a sentence on the Internet:

Set identity_insert nt_news_class on

Insert into table name 1 (SQL2000)
Select * From OpenRowSet ('sqloledb ','. \ sql2005 (remote server name or Instance name) '; 'sa (account)'; '2014 (password) ', database. DBO. table name)

This still does not work, because the prompt is clear, you must use the auto-increment column name and set it to on, because the above sentence does not show a column name that grows automatically.

In this case, you can:

Set identity_insert nt_news_class on

Insert into table name 1 (auto-increment column, col1, col2 ....) (SQL2000)
Select * From OpenRowSet ('sqloledb ','. \ sql2005 (remote server name or Instance name) '; 'sa (account)'; '2014 (password) ', database. DBO. table name)

Therefore, auto-increment columns must exist.

But I am too lazy to write, because there are too many, so after testing, I first change the self-increasing column of the SQL2000 table to not grow, then insert, OK, and then change back. OK.

Everything is OK

 

Today we will talk about this. Next time we will summarize other statements for cross-service queries.