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 […]
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 […]
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 […]
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 […]
Create Table with Autoincrement
The important part is the IDENTITY(x,x) and PRIMARY KEY. CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); More information found here: https://www.w3schools.com/sql/sql_autoincrement.asp
Get latest order on customer
http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column/123481#123481 I see many people use subqueries or else vendor-specific features to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it […]
Reseed
The command to reset the identity property is DBCC CHECKIDENT (tablename, RESEED, new_reseed_value) When you want to set the column identity to 12345 you run this DBCC CHECKIDENT (beer, RESEED, 12345) When you […]
Convert to Unix Timestamp
Converting a datetime to unix timestamp is easy, but involves error prone typing the following: @timestamp=DATEDIFF(second,{d ‘1970-01-01’},@datetime) Where @datetime is the datetime value you want to convert. The {d ‘yyyy-mm-dd’} notation is an ODBC escape […]
Convert from datetime to varchar
CONVERT(VARCHAR(12),DATEADD(S, getdate(), ‘1970-01-01’),105) Specification according to https://msdn.microsoft.com/library/ms187928
Convert from Unix timestamp
Select dateadd(S, [unixtime], ‘1970-01-01’) From [Table] Source http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
