Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to query SQLServer database remotely and import data in batches". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "SQLServer database how to query remotely and import data in bulk"!

What are the methods of remote query and batch import of data in SQLServer database

Application background:

The table structure of the two databases is the same, but the table name prefix is not the same, so we need to keep the ID of the original data table unchanged. Using the database bulk import / export tool does not work, you can only write your own SQL statements. How to have more than 180 tables, hand-written must be tired S individual.

Solution:

It is possible to connect to the server remotely using OpenDataSource, and then execute the insert statement. The statement is as follows:

TruncatetableActUserSETIDENTITY_INSERTActUseroninsertintoActUser ([ID], [PassWord], [UserName]) select [ID], [PassWord], [UserName] fromopendatasource ('SQLOLEDB','datasource=DBSERVERIP;uid=sa;password=sa') .TEST.dbo.ActUserSETIDENTITY_INSERTActUseroff

What are the methods of remote query and batch import of data in SQLServer database

Then I thought of using cursors combined with sysobjects and syscolumns to automatically generate such statements, the code is as follows:

Declaremycursorcursorforselect [id], [name] fromdbo.sysobjectswheretype='U'openmycursordeclare@tablenamesysnamedeclare@tableidsysnamefetchnextfrommycursorinto@tableid,@tablename-- get table name while (@ @ fetch_status=0) beginprint'-- ['+ @ tablename+']'--patchwork field Convert the line to a string-- DECLARE@fieldsvarchar (8000) set@fields=''SELECT@fields=''+@fields+'], ['+ nameFROMsyscolumnsWHEREid=object_id (@ tablename) ORDERBYcolidset@fields=' ['+ STUFF (@ fields,1,3) '') +']'- patchwork field End----print@fields/* to avoid truncation caused by too long fields * / print'truncatetable'+@tablename-- cleanup data print'SETIDENTITY_INSERT'+@tablename+'on'-- can turn from growing columns to pluggable data There is no need to regenerate IDprint'insertinto'+@tablename+' ('print@fieldsprint') select'print@fieldsprint'fromopendatasource (' +''SQLOLEDB'',''datasource=DBServerIP Uid=sa;password=sa'') .TEST.dbo.'+replace (@ tablename,'Wait_','Has_') print'SETIDENTITY_INSERT'+@tablename+'off'printchar (13)-- Line wrap fetchnextfrommycursorinto@tableid,@tablenameendclosemycursordeallocatemycursor

Execute the generated SQL statement in the query analyzer, generate the SQL statement, and then execute it, and you can successfully import the data in batches.

At this point, I believe that everyone on the "SQLServer database how to remote query and batch import data" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.