Tag: SQL Script

SQL Server – User Defined Group

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports) Example: CREATE TABLE test_custom_group ( id INT, dept VARCHAR(20), detail VARCHAR(30), salary NUMERIC(5,2) ) Created a Function CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20), grp varchar(10) ) AS BEGIN INSERT @temp_table(sno,dept,grp ) SELECT 1,'C','A' UNION all SELECT 2,'A','A' UNION all SELECT 3,'D','B' UNION all SELECT 4,'E','B' UNION all SELECT 5,'B','B' RETURN END Created a Store Procedure USE test_db IF EXISTS

» Read more
, ,

SQL Server – User Defined Sort Order

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order Example: Created a table as : CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) ) Inserted few rows in table as Requirement: Now we want to change the sort order and new sorted output should be: REQUIRED OUTPUT: For this created a function: CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20)

» Read more
, , ,

SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

SQL Server database administrators frequently need in especially development and test environments to find and kill all the blocked process. Following scripts are useful in that scenario. Script – 1 — Find All the Blocked Processes SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=SUBSTRING(hostname,1, 12), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)   Script 2 — Kill all the Blocked Processes of a Database DECLARE @DatabaseName nvarchar(50) — Set the Database Name SET

» Read more
, , ,

TSEQUAL and JOIN syntax as *= or =* not supported in SQL Server 2008

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, Varinder Sandhu

I have faced an issue during SQL Server Migration. Here sharing with you. As I need to move some databases from SQL Server 2000 to SQL Server 2008 then I faced a problem because TSEQUAL and JOIN syntax such as *= or =* not supported in SQL Server 2008. Some of scripts are using the function and syntax as Example: TSEQUAL (TxnStatus, @ciTxnSts) JOIN syntax which has *= and =* to specify the OUTER JOIN. Solution: TxnStatus = @ciTxnSts instead of 

» Read more
, , ,

SQL Server – Comma Separated INSERT Option

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is new feature of SQL Server 2008 – “Comma Separated INSERT Option” In this feature using a single INSERT statement you can pass multiple value for the table using comma separated option. USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’), (‘Amit’, ‘CDE Street’), (‘Dinesh’,’XYZ Street’) Result :    

» Read more
, , , , ,

SQL Server – Insert same value multiple time in SQL table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is an example:  Inserting same value multiple time in SQL table USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’) go 5 Result :  

» Read more
, , , ,