CODEWITHSUNDEEP
sql-serverssms
dimas
dimas

Reputation: 2597

Limit number of rows to be exported using SQL Server Import Export wizard

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
)

Upvotes: 3

Views: 6391

Answers (1)

Whencesoever
Whencesoever

Reputation: 2296

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.

Upvotes: 4

Related Questions