Does sql data compare write out all data or do inserts/sel

pkellner · August 26, 2015 8:15PM

That is, is it reasonable to try and use sqldata compare to copy a 5 gig database (to azure).

if it is doing insert into (select..) then I'd think it's OK. If it's writing out raw insert statements, not so much.

Eddie D · September 15, 2015 7:28AM

Hi

Thank you for your forum post and sorry for the delay in replying.

SQL Data Compare will use an 'INSERT INTO' statement, similar to the following example:

PRINT(N'Add 3 rows to &#91;HumanResources&#93;.&#91;Shift&#93;')
SET IDENTITY_INSERT &#91;HumanResources&#93;.&#91;Shift&#93; ON
INSERT INTO &#91;HumanResources&#93;.&#91;Shift&#93; (&#91;ShiftID&#93;, &#91;Name&#93;, &#91;StartTime&#93;, &#91;EndTime&#93;, &#91;ModifiedDate&#93;) VALUES (1, N'Day', '1900-01-01 07:00:00.000', '1900-01-01 15:00:00.000', '1998-06-01 00:00:00.000')
INSERT INTO &#91;HumanResources&#93;.&#91;Shift&#93; (&#91;ShiftID&#93;, &#91;Name&#93;, &#91;StartTime&#93;, &#91;EndTime&#93;, &#91;ModifiedDate&#93;) VALUES (2, N'Evening', '1900-01-01 15:00:00.000', '1900-01-01 23:00:00.000', '1998-06-01 00:00:00.000')
INSERT INTO &#91;HumanResources&#93;.&#91;Shift&#93; (&#91;ShiftID&#93;, &#91;Name&#93;, &#91;StartTime&#93;, &#91;EndTime&#93;, &#91;ModifiedDate&#93;) VALUES (3, N'Night', '1900-01-01 23:00:00.000', '1900-01-01 07:00:00.000', '1998-06-01 00:00:00.000')
SET IDENTITY_INSERT &#91;HumanResources&#93;.&#91;Shift&#93; OFF

If you are worried about copying the 5GB of data, you can perform comparison and deployment in stages by selecting the tables for deployment or/and configure 'Where' clause for each table. It will mean repeating the comparison and deployment two or more times as required.

Many Thanks
Eddie

Does sql data compare write out all data or do inserts/sel

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk