Insert huge SQL script

http://www.diaryofaninja.com/blog/2009/09/25/running-large-mssql-scripts-amp–resulting-memory-errors It took time to get it to work, I went through the same headaches, but finally got it running with below command, including a login. https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility sqlcmd -S YOURSQLSERVER\INSTANCENAME -i “C:\Your Script.sql” sqlcmd -a […]

With q as query

Its cool to use subqueries, but it can be even cooler if you want to take data from a result and handle that in different way. And can be used instead of creating a temp […]

MSSQL Select Distinct

If you like me had multiple Serial that are equal, an really just want to get a single line as output, this can be used. The important part is ROW_NUMBER() line, deciding which line to […]

MSSQL Only trigger on certain changes

Question I want it to only update the modified information if the QtyToRepair value has been updated… but it doesn’t do that. If I comment out the where then the modified information is updated in […]

SQL DateTime

— SQL Server string to date / datetime conversion – datetime string format sql server — MSSQL string to datetime conversion – convert char to date – convert varchar to date — Subtract 100 from […]

UPDATE table FROM table2

The simple Way to copy the content from one table to other is as follow: UPDATE table2 SET table2.col1 = table1.col1, table2.col2 = table1.col2, … FROM table1, table2 WHERE table1.memberid = table2.memberid For SQL Server […]

Backup SQL Server

Examples This section contains the following examples: A. Backing up a complete database B. Backing up the database and log C. Creating a full file backup of the secondary filegroups D. Creating a differential file […]

DBCC Shrinkfile

Examples A. Shrinking a data file to a specified target size The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB. USE UserDB; GO DBCC […]

Database in restoring mode

If the database is stuck in restoring mode, all that is needed should be to run this command RESTORE DATABASE <database> WITH RECOVERY  

Silent truncation in MSSQL

The thing you have to do is set ANSI WARNINGS to OFF You can do that by calling set ANSI_WARNINGS OFF I have also written a practical example: create table bla(id varchar(2)) go   insert […]