1

I have to import 500,000 records from a database source in another server using wizard, because import wizard uses bulk insert. I want to provide a source query with a filter to check if record exists in destination.

Is it possible? What's the best way to do that?

enter image description here

Souza
  • 110
  • 11
  • 2
    It's *possible*, but expect it to be horrendously inefficient. Linked queries have a tendency of simply pumping over all the rows of the remote server to do the comparison "efficiently". In many cases you will need to take manual charge and do things like 1) query all IDs (and only those) on the remote server and insert them locally and 2) write the query with the filter based on that local data. Step 1 can actually be done with another import/export wizard invocation, of course. If you need to repeat this operation, writing an SSIS package makes more sense. – Jeroen Mostert Jul 03 '19 at 14:46
  • I thought about linked server, but I don't have permission on remote server to create linked server. Maybe there are another efficient way to import those records, not necessarily with wizard (ssms). – Souza Jul 03 '19 at 14:56
  • 2
    500K rows is actually not *that* much, assuming you are actually going to end up inserting most of them. You could consider just importing all of them into a brand new table, then writing an `INSERT` to insert only the rows that aren't present yet. This requires no extra steps or permissions. – Jeroen Mostert Jul 03 '19 at 14:58
  • Yes, I'll try that. – Souza Jul 03 '19 at 15:02
  • Jeroen Mostert I did like you said and worked. The time I would waste looking or a solution I spent creating a temp table . Thanks a lot. – Souza Jul 03 '19 at 15:18

1 Answers1

0

Yes, this should be do-able if both databases are on the same server, or the servers are linked.

You can test this by running the query you are trying to enter here as Source directly into SSMS on the source database and see if it runs (maybe limited with a TOP 100 or something).

If linked servers aren't an option, look into SQL Server Integration Services. With SSIS you can craft a data flow (in fact, the Import- and export wizard creates a very simple SSIS package). How your data flow might look is described quite well in the answers to this question. Especially if this is a recurring task, it might be beneficial to have this stored in an SSIS package that you can then run on demand or plan execution in the SQL Server Agent.

If SSIS isn't an option either, consider splitting the data transfer in two steps, where you first transfer all rows from source to destination into a staging table, then copy from the staging table to the destination table what you need based on the filter. This might present quite the hit in both performance, bandwidth and disk space however and is the least elegant solution.

steenbergh
  • 1,642
  • 3
  • 22
  • 40
  • I thought about linked server, but I don't have permission on remote server to create linked server. Maybe there are another efficient way to import those records, not necessarily with wizard (ssms). – Souza Jul 03 '19 at 14:57
  • @Elizeu there is, SSIS. I've extended my answer. – steenbergh Jul 03 '19 at 15:14