Good day! I have the same in structure and content of the two SQL servers. Each of them has the same base 'base', each of which contains the same table 'table'. Only on the second server the table is empty, and there it is necessary to transfer data from the first one. Export / Import and Linked server are not available due to rights restrictions. Can you please tell how it is possible to transfer data under such conditions?

  • Planned one transfer? - Nick Proskuryakov
  • Yes, this is a one-time necessity. - Cutis Jackson
  • Make a backup of the first base, throw a second server and raise the database with a similar name in the same instance. - Nick Proskuryakov
  • It's not so simple, the database structures are different, and stupidly backup / restore will not work. This is me to simplify the perception described the greenhouse conditions. In fact, you should not even transfer the entire table, but the data in a certain date range. I just know how to select this range and insert it into a table on another server, but how to configure the interaction between servers? - Cutis Jackson
  • one
    I advised you not to restore the restore, but to raise the base of the first server on the second instance, then we take the data from the first database and pour it on the condition of the second database, it all happens within one instance. - Nick Proskuryakov

2 answers 2

Use the data import and export wizard. enter image description here

  • specify the data source (first server)
  • specify the destination point - the second server.
  • write the query text to fetch the source data
  • configure select the destination table on the second server
  • set up matching columns and their types
  • run the package

Trash option two, but generally working. data (field1 int, field varchar) there is a data (field1 int, field varchar) table data (field1 int, field varchar) and you need to transfer it, then you can write a query like

 SELECT 'insert into data values (' + cast(field1 as varchar) + ' , ''' + field2 + ''');' as sql FROM data WHERE ...

execute, copy the generated SQL queries (or results to -> results to file ) and execute a set of scripts (file) on the second server.

I in a similar way sometimes transfer simple data from, since this is faster than clicking on the steps of the import wizard. Here, however, you can also copy the result to Excel, form the lines of the insert queries there, and execute them.

  • "Export / Import and Linked server are unavailable due to rights restrictions." ( C ) - Nick Proskuryakov
  • @NickProskuryakov for import except select-insert is right and there’s no need, like. - teran
  • @NickProskuryakov has added another option. - teran
  • I think it will work, although of course it's a pity the author, you can solve the problem a lot easier if you have rights. - Nick Proskuryakov
  • @NickProskuryakov well, I didn’t look at something, you thought it was the author :) I missed something in the question about import-export, I saw the server about linked. - teran

In Management Studio PCM on the item Databases >> SQL Assistant >> Script Data. You mark what data you need to transfer, set up Export Options and get the necessary scripts:

Tables

Settings