How to

The code below is to search those records where first name contains % character. This question also important for interview /* Search those records where first name contains % */ CREATE TABLE #userdata ( RecordId INT IDENTITY , FirstName VARCHAR ( 50 ), LastName VARCHAR ( 50 )) INSERT INTO #userdata ( FirstName , LastName ) VALUES ( 'Dave%' , 'Hanks' ), ( 'Simon' , 'Deja' ), ( 'Peter%' , 'Hans' ) SELECT * FROM #userdata /* Query 1 */ SELECT * FROM #userdata WHERE CHARINDEX ( '%' , FirstName , 0 ) > 0 /* Query 2 */ SELECT * FROM #userdata WHERE FirstName LIKE '%[%]%' DROP TABLE #userdata

Below is the code to generate arranging records in the same year and the consequent sum. Query is simple and understandable, no group or any aggregate function is used. Just simple insert and update statements can do the task. /* Creating temporary table */ CREATE TABLE #entries ( RecordId INT IDENTITY ( 1 , 1 ), FinancialYear INT , Amount INT , Total INT DEFAULT 0 ) /* Inserting dummy data to table */ INSERT INTO #entries ( FinancialYear , Amount ) VALUES ( 2011 , 5 ),( 2011 , 10 ),( 2011 , 15 ),( 2012 , 10 ), ( 2012 , 20 ), ( 2012 , 30 ), ( 2012 , 50 ),( 2012 , 50 ), ( 2013 , 100 ), ( 2013 , 150 ), ( 2013 , 200 ) /* Get all data of entry table to temp table with two aditional columns*/ SELECT *, CAST ( 0 AS INT ) MatchYear , CAST ( 0 AS INT ) EntryId , CAST ( 0 AS INT ) OverAllTotal INTO #temp FROM #entries DECLARE @id INT = 0 , @total INT = 0 , @nextAmt INT = 0 , @firstYear INT , @overAllTot...

Below is the simple code to insert data in to two tables in single Insert statement CREATE TABLE #registration ( RegistrationId INT IDENTITY ( 1 , 1 ), FirstName NVARCHAR ( 50 ), LastName NVARCHAR ( 50 )) CREATE TABLE #logindetails ( LoginId INT IDENTITY ( 100 , 1 ), RegistrationId INT , UserName NVARCHAR ( 50 ), Pwd NVARCHAR ( 50 )) Here RegistrationId is related in both tables. Inserting two records in to both tables INSERT INTO #registration ( FirstName , LastName ) OUTPUT   INSERTED . RegistrationId , 'admin' , 'admin@123' INTO #logindetails SELECT 'Jawala' , 'Parsad' INSERT INTO #registration ( FirstName , LastName ) OUTPUT   INSERTED . RegistrationId , 'mylogin' , '123@testone' INTO #logindetails SELECT 'Happy' , 'Singh' SELECT * FROM #registration SELECT * FROM #logindetails DROP TABLE #registration , #logindetails After selecti...