CODEWITHSUNDEEP
.netsql-server
user1519220
user1519220

Reputation: 35

Fastest way to fetch records from remote sql server and save to local sql database using DataTable

I have a customer table with over 100000 records in a remote sql server database.

Customer table
--------------
ID
Name
Phone
Occupation
Gender

I also have a list of 4000 customer id's.

What will be the fastest way to fetch customer data from remote sql server and save to local sql database using DataTable in .NET.

I will also be interested in any different way to achieve that.

What I have tried:

I made use of the IN statement, but it throws an error when id's are more than 3000:

select * from customertable where id in ('CUS1','CUS2','CUS3','CUS4','CUS5',....'CUS4000')

I would have inserted customer ids in a temp table and joined, but the two database are not hosted on the same server.

Upvotes: 1

Views: 300

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Just one option is to parse your list and perform a join. If 2016+, you could use string_split().

Declare @List varchar(max) = 'CUS1,CUS2,CUS3,CUS4,CUS5,CUS6,CUS7,CUS8,CUS9,...'

Select A.* 
 From customertable A
 Join (
        Select RetVal = B2.i.value('(./text())[1]', 'varchar(max)')
         From  (Select x = Cast('<x>' + replace(@List,',','</x><x>')+'</x>' as xml).query('.')) as B1 
         Cross Apply x.nodes('x') AS B2(i)
      ) B on A.ID=B.RetVal

EDIT

I'm a little confused over your design, but tou can generate @List with something like this.

Declare @List varchar(max) = (Select Stuff((Select ',' +ID From #YourTemp  For XML Path ('')),1,1,'') )

Upvotes: 0

Related Questions