Month: May 2021

Copy data from one DB to second DB using Script

I would go with this template to copy data from one database to another database using scripts;

Use MyDB
GO

BEGIN TRY
	BEGIN TRANSACTION
	
	IF NOT EXISTS (SELECT  1 FROM  [dbo].[MyTable] WHERE [Id] = '801DA66B-F5F7-463E-AD56-D432E12B429E' )
	BEGIN
		Print 'INSERT / UPDATE / DELETE'
		INSERT INTO ... 
		UPDATE ....
		DELETE ....
	END

	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
	,ERROR_MESSAGE() AS ErrorMessage
	 ROLLBACK TRANSACTION
END CATCH

Basically it’s a row by row insertion where row ID is been checked for existence and operation is performed.

Copy BLOB (binary data) from one DB to second DB using script

I would go with generate script option in SQL Server Management Studio;

  1. Highlight source database that has the source table
  2. Right-click – Tasks | Generate Scripts
  3. Select source table under “Select specific database objects”. Click Next
  4. Click Advanced and under General options – Change ‘Type of data to script’ to “Data Only”
  5. Save to file.

I have tried a sample that store excel files as varbinary type. The output from scrpting process is ;

USE [MyDB]
GO
INSERT [dbo].[Document] ([DocumentId], [FileExtension], [FileName], [Doc_Content]) VALUES (N'3af2497c-a190-eb11-b1bb-000d3adde0a7', N'.xlsx', N'Sample-01.xlsx',  0x504B0304140006000800000021002FDCAF2EEC010000310D0000130008025B436F6E74656E745F54797065735D2E786D6C20A2040228A00002000000000000000000000000000000000000000000000000000000000000000000000000000000