2

I have one database with an image table that contains just over 37,000 records. Each record contains an image in the form of binary data. I need to get all of those 37,000 records into another database containing the same table and schema that has about 12,500 records. I need to insert these images into the database with an IF NOT EXISTS approach to make sure that there are no duplicates when I am done.

I tried exporting the data into excel and format it into a script. (I have doe this before with other tables.) The thing is, excel does not support binary data.

I also tried the "generate scripts" wizard in SSMS which did not work because the .sql file was well over 18GB and my PC could not handle it.

Is there some other SQL tool to be able to do this? I have Googled for hours but to no avail. Thanks for your help!

Klay
  • 183
  • 5
  • 12

3 Answers3

2

Why don't you try the 'Export data' feature? This should work.

Right click on the source database, select 'Tasks' and then 'Export data'. Then follow the instructions. You can also save the settings and execute the task on a regular basis.

Also, the bcp.exe utility could work to read data from one database and insert into another.

However, I would recommend using the first method.

Update: In order to avoid duplicates you have to be able to compare images. Unfortunately, you cannot compare images directly. But you could cast them to varbinary(max) for comparison.

So here's my advice:
1. Copy the table to the new database under the name tmp_images
2. use the merge command to insert new images only.

alzaimar
  • 4,572
  • 1
  • 16
  • 30
2

I have used SQL Workbench/J for this.

You can either use WbExport and WbImport through text files (the binary data will be written as separate files and the text file contains the filename).

Or you can use WbCopy to copy the data directly without intermediate files.

To achieve your "if not exists" approache you could use the update/insert mode, although that would change existing row.

I don't think there is a "insert only if it does not exist mode", but you should be able to achieve this by defining a unique index and ignore errors (although that wouldn't be really fast, but should be OK for that small number of rows).

If the "exists" check is more complicated, you could copy the data into a staging table in the target database, and then use SQL to merge that into the real table.

1
INSERT INTO DB1.dbo.table_name
SELECT  * FROM DB2.dbo.table_name
WHERE column_name NOT IN
(   
  SELECT column_name FROM DB1.dbo.table_name
)
Klay
  • 183
  • 5
  • 12