9

I cannot use linked server.

Both databases on both servers have the same structure but different data.

I have 10k rows to transfer from the DB on one server to the same DB on the other. I cannot restore the DB on the other server as it will take a huge amount of space that I don't have on the other server.

So, I have 2 options that I don't know how to execute:

  1. Backup and restoring only one table - the table is linked to many other tables but these other tables exist on the other server too. Can I somehow delete or remove the other tables or make a backup only over one table?
  2. I need to transfer 10k rows. How is it possible to create 10k insert queries based on selected 10k rows?
Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
Alexxx
  • 299
  • 1
  • 2
  • 9
  • https://github.com/csharpbd/SQL-Server-Databases-Transfer – Imran Ali Khan Dec 19 '17 at 05:15
  • Linked servers, select into new database then file copy, Bulk Insert, SSIS, `FOR XML`, or script table data. I'm sure there are more options, these are just the top ones I can think of. – Mitch Dec 19 '17 at 05:28
  • Please [edit] your question to show [the code you have so far](http://whathaveyoutried.com). You should include at least an outline (but preferably a [mcve]) of the code that you are having problems with, then we can try to help with the specific problem. You should also read [ask]. As it is, it appears that you're interested in using standard database tools, rather than seeking programming help, making this question off-topic here. – Toby Speight Dec 19 '17 at 11:03
  • Possible duplicate of [What is the best way to auto-generate INSERT statements for a SQL Server table?](https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table) – gofr1 Dec 19 '17 at 11:11

6 Answers6

5

Can I somehow delete or remove the other tables or make a backup only over one table?

No you can not do this, unfortunately

How is it possible to create 10k insert queries based on selected 10k rows?

Right-click on Database -> Tasks -> Generate scripts -> (Introduction) Next

Chose Select specific database objects -> Tables, chose table you need -> Next

Advanced -> Search for Types of data script change from Schema only (by default) to Data only -> OK

Chose where to save -> Next -> Next. Wait the operation to end.

This will generate the file with 10k inserts.

Another way is to use Import/Export wizard (the most simple way for one-time-import/export) if you have link between databases.

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • While this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please [edit] to provide example code to show what you mean. Alternatively, consider writing this as a comment instead. – Toby Speight Dec 19 '17 at 11:01
  • @TobySpeight there is no need for any code. It is done through SSMS. There is few option to done this through scripts - but this is much more complicated than this solution. – gofr1 Dec 19 '17 at 11:10
  • @TobySpeight this question (I guess) is a duplicate of this [one](https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table) – gofr1 Dec 19 '17 at 11:11
3

There are many ways to choose from, here is one way using BCP. That's a tool that ships with SQL Server to Import and Export Bulk Data.

The outlines of the process:

  1. Export the data from the source server to a file using BCP - BCP OUT for a whole table, or BCP QUERYOUT with a query to select the 10k rows you want exported
  2. Copy the file to the destination server
  3. Import the data using BCP on the destination database - BCP IN.
TT.
  • 15,774
  • 6
  • 47
  • 88
  • Why not use SSIS straight out? I think that would be the safest route since he is using SQL SERVER on both machines (no MYSQL tag on the question) – Mr.J Dec 19 '17 at 07:49
  • @Mr.J As I said, many ways, this is just one. Some scenarios favour particular solutions. – TT. Dec 19 '17 at 07:51
  • I have not used BCP to migrate data so that made me confused. I always use SSIS, I would try your solution if I would have the chance. – Mr.J Dec 19 '17 at 07:55
  • @Mr.J It's basically scripting the process rather than configuring in a tool like SSIS. Honestly I'm on the other side of your situation, I've never used SSIS and would try it out if I'm ever in a scenario where data transfer is needed. – TT. Dec 19 '17 at 07:58
  • While this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please [edit] to provide example code to show what you mean. Alternatively, consider writing this as a comment instead. – Toby Speight Dec 19 '17 at 11:02
  • @TobySpeight A pointer to a proper solution outlining the necessary steps is sufficient IMHO. There is too much detail involved in all peculiarities regarding BCP, which is an extensive tool with a myriad of options. The basic steps, plus an indication of what mode of BCP (QUERYOUT -> IN) to use should suffice. – TT. Dec 19 '17 at 11:29
2

My suggestion would be to export these rows to excel( you can do this by copy pasting your query output) and transfer this to other server and import it there.

this is the official method :- https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql

and this is the the unofficial method : http://therealdanvega.com/blog/2010/08/04/create-sql-insert-statements-from-a-spreadsheet.

Here I have assumed that you only need to transfer the transactional data and your reference data is same on both server. So you will need to execute only one query for exporting your data

BlindSniper
  • 1,731
  • 3
  • 16
  • 30
  • 1
    Copy-paste of query output may alter field contents - excel will treat numeric strings as numbers, removing leading zeroes (01234 > 1234); excel may convert numbers to dates (if date separators collides with decimal separator - 12.11 > 12.11.2017), excel may convert long numeric strings (EAN codes for example) to exponential form etc. – Arvo Dec 19 '17 at 08:53
0

I would definietely go down the SSIS route once you use SSIS to do a task like this you will not use anything else very simple to script up. You can use any version and it will be a simple job and very quick.

  1. Open new SSIS project in available visual studio version/s there are many different but even a 2008 version will do this simple task you may have to install integration services or something similar used to be called bids (business information development studio in 2008) (anything up to 2015 although support is nearly there in 2017)
  2. add a data flow task
  3. double click the data flow task
  4. Bottom of screen add two connection managers (1 to source and 1 to destination database)
  5. add oledb source pointing to source database table
  6. add oledb destination pointing to destination database table
  7. drag line between the source and destination (should auto map all columns if the same name)
  8. hit Start and the data will flow very quickly
Andy Setchell
  • 1
  • 1
-1

you have create DbInstaller. using dbInstaller you have share whole database. Dbinstaller work both ado.Net and Entity Frame Work but I have using Entity Frame Work.

Abhishek Kulshrestha
  • 1
  • 2
-2

you can do it by sql server query

first select first database like

Use database1 --- this will be your first database

after select first database we will put our table row in temp table by this query

select * into #Temp from select * from table1

now we select second database and insert temp table data into second database table by this code

use secondDatabaseName



INSERT INTO tableNameintoinsert (col1, col2, )
SELECT col1, col2 FROM #temp;
vishal Jaiswal
  • 1
  • 2