3

I have two databases from different servers let's say DB_1 and DB_2. I was able to insert data from DB_1 table1 to DB_2 table1 without any problem. Now I need to transfer data from DB_1 table2 to DB_2 table2 however the contents of DB_1 table2 is around 200 Million rows. Is there a way to export only like 10K or 50K rows from DB_1 table2 to DB_2 table2 using SQL Server import export wizard?

I've tried the options on the wizard and I can't seem to find it.

Also both DB_2 is an exact replica of DB_1 but without data, I just need the data from DB_1 for testing purposes.

Any suggestions from experts on how to achieve this?

As suggested by whencesoever I tried to create a script using the import export wizard of SSMS and imported it the data from DB_1 to DB_2. However there were a couple of steps that I need to do as it wasn't straight forward for me.
I had to resort to creating LinkedServers in my DB_2 you can check the link here for explanation on how it is done.

or use the command below to create a linked server

EXEC master.dbo.sp_addlinkedserver @server = N'LINKED_SERVER_NAME_HERE', @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'IP_ADDRESS_HERE'

After the linked server is created you can right click on the properties of LINKED_SERVER_CREATED.

  1. Click Properties
  2. Click Security Under General
  3. Click the radio button where it says "Be made using this security context:"
  4. Enter the remote login and password of the remote database you want to have access
  5. Click ok

Test the connection if its ok. You can easily execute the query below which is in my case was to transfer data from DB_1 Table2 to DB_2 Table2

insert into table2
(
select * from LINKED_SERVER_NAME.DB_1.dbo.table2
)
Community
  • 1
  • 1
dimas
  • 2,487
  • 6
  • 40
  • 66

1 Answers1

4

When importing You can check option to write your own Query, it's just after You pick source and destination of data. The first option takes always entire table. (it is even written in it's description). If You pick "write Your own query, You will be able to limit row number and use other restrictions.

Whencesoever
  • 2,218
  • 15
  • 26
  • i did try to research on that but most of the articles I found says that i need to have like dblink etc to make it work. – dimas Mar 09 '17 at 07:16
  • But after checking it You need to provide Query. Let's say: "select top (10000) * from table1. And it is simple like that. Did You try it at least? – Whencesoever Mar 09 '17 at 07:24
  • You don't need to have the servers linked, you just need to have permissions on both of them and set your own credentials for this one-time transaction. And don't go on "export" data from the source server but go with importing it on the destination one as @Whencesoever already told you. – MK_ Mar 09 '17 at 07:56
  • Thanks for the advice I was able to create a script but I had to do a couple of things first and was able to transfer the data – dimas Mar 09 '17 at 10:33