You should use sqlcmd.exe using dos prompting to execute large sql files (GREATER THEN 1gb)

Whenever I have to execute a huge file, I use sqlcmd.exe via dos.

My strategy is

STEP 1

Create a specific user for that - something like USR_TOUGH_GUY

OWNED SCHEMAS
db_accessadmin

ROLE MEMBERS
db_accessadmin

STEP 2

Find the executable sqlcmd.exe in the machine where SQL SERVER is installed.

STEP 3

Execute
sqlcmd -S myServer\instanceName -U USR_TOUGH_GUY -P THE_PASSWORD_OF_MR_TOUGH_GUY  -i  C:\yourscript.sql


STEP 4

Disable mr TOUGH_GUY

Final considerations

You could do this using the SQLStudio but it will consume a lot of memory of the machine, if you are, for instance, trying to execute a 4GB file.

Good luck!

How do we solve problems with quotes when it comes to insert?

Sometimes we have to insert text with quotes in database tables, but when we try to format the query problems happen.

Example:

Be Table a with a Column c1. And c1 is a column type text.

string lsql = "insert into a (c1) values ('[here you put your string value]')";
SqlConnection lConn = new SqlConnection([you connectionstring]);
SqlCommand lCmd = new SqlCommand(lsql, lCon);
lCon.open();
lCmd.ExecuteNonQuery();
lCon.close();
This is going to work if you string value has no quotes. If there's quotes you're gonna receive a message like

You have fewer fields parameters than values

// ********************************************************************* //

You can do a lot of manouvers, but theres a technique wich I consider the most elegant of all.

string lsql = "insert into a (c1) values (@yourstring)";
SqlConnection lConn = new SqlConnection([you connectionstring]);
SqlCommand lCmd = new SqlCommand(lsql, lCon);

// Parameters are very usefull when it comes to texts with quotes

lCmd.Parameters.Add(new SqlParameter("@TextFile", System.Data.SqlDbType.Text,5000,"textFile"));
lCmd.Parameters["@yourstring"].Value = [here you set your string];

lCon.open();
lCmd.ExecuteNonQuery();
lCon.close();

And voilà! Done!