Migrating data from local MSSQL server generated script to Azure web app MSSQL server , when it failed executing from SQL Management studio

SQL Server Administration
,
1

I have generated 200 mega data script from local SQL Server, which I generated via Tasks -> Generate Script in SQL Server Management Studio.

When I try to execute it from the SQL Server Management Studio, I get massage that I have not enough memory to run it, which is weird I have 16 gigabytes of ram in my pc.

So my question is simple: how can I update my remote SQL Server with the data script ?

Can I configure SQL Server Management Studio to use more memory or there is better way to execute big script?

Thanks

2

I don't know how many rows you generated but consider the size of the statements that were generated. Every row is a full blown INSERT/VALUES with column names for every row. It's damned near as bad as XML.

The, on top of all that, you're running it all in a single batch. Again, I don't know how many rows you actually ended up with but there's an execution plan that get's generated for each and every row even it it's a "reused" plan.

It would be much better if you used a single INSERT/SELECT to insert into your remote server. An even faster way would be to do the same thing that replication does on an initial table setup... BCP the data out of your source server in "native" format and then do either a BCP insert in the remote server or a BULK INSERT. If you can also take advantage of "Minimal Logging", it will be at least twice as fast and you can populate the remote table with the clustered index in place. If you lookup Trace Flag 610, you can even do faster inserts and can also take advantage of parallel inserts, if the notion strikes you.

But those 10 bazillion INSERT/VALUES statements just aren't going to do it for you even for such a relatively small table.

3

i did run the script using sqlcmd
and it runes great the problem is im getting this error allot
Cannot insert explicit value for identity column in table 'Table_Name' when IDENTITY_INSERT is set to OFF.

which means acording to the internet to disable inserting the "ID" of the tables
The problem is i couldn't find in the MSSQL management studio option to generate script where
i should not include IDENTITY in the insert statement SQL Server

4

You are inserting values for that is an identity column.

You can turn ON identity insert on the table so that you can specify your own identity values.

5

maybe this might help. different methods of migrating data to Azure