INSERT Record into TABLE with DEFAULT VALUES and other SQL tricks..!

If you want INSERT record INTO TABLE with DEFAULT VALUES without specifying all the values, then here is the trick...

SYNTAX:

INSERT INTO TABLENAME DEFAULT VALUES

EX:

INSERT INTO EMPLOYEE DEFAULT VALUES

-It will insert all default values specified for the columns in a table.

OTHER EX:

INSERT INTO TABLENAME SELECT 'VALUE1','VALUE2',...AND SO ON

-This will insert values into table without having specify all column names of table,but here in select statement you have specify all values or default values.


Solution to insert or update record using views,when view holding two joined tables.

Ex:
-CREATE VIEW

CREATE VIEW EUView
AS
SELECT coalesce(EMPLOYEE.ID,[USER].UID) TABLENAMEID,EMPLOYEE.NAME,[USER].UNAME
FROM EMPLOYEE FULL OUTER JOIN [USER] ON EMPLOYEE.ID=[USER].UID


-CREATE A TRIGGER ON VIEW

CREATE TRIGGER EUTRIGGER
ON EUVIEW
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO EMPLOYEE(ID,NAME)SELECT TABLENAMEID,NAME FROM INSERTED
INSERT INTO [USER](UID,UNAME)SELECT TABLENAMEID,UNAME FROM INSERTED

END


-FOLLOWING SHOWS INSERT A RECORD

INSERT INTO EUVIEW SELECT 3,'Test2','Chandu'



TRICK TO FIND DUPLICATE RECORD IN A TABLE

SELECT EMP_NAME,COUNT(EMP_NAME) FROM EMPLOYEE
GROUP BY EMP_NAME
HAVING COUNT(EMP_NAME)>1


TRIGGER FIRING ORDER IN SQL Server

Following shows creation of triggers and setting order of trigger firing.

set nocount on go create table dbo.customer (customerid int identity primary key) go

Create trigger 1

create trigger dbo.tr1 on dbo.customer for insert as set nocount on print 'firing original trigger 1' go

Create trigger 2

create trigger dbo.tr2 on dbo.customer for insert as set nocount on print 'firing original trigger 2' go

Sample insert statement

insert into dbo.customer default values go exec sp_settriggerorder @triggername = 'tr1', @order = 'first', @stmttype = 'insert', @namespace = null exec sp_settriggerorder @triggername = 'tr2', @order = 'last', @stmttype = 'insert', @namespace = null go

TRICK TO FIND nth MAX SALARY

1.

SELECT top 1 * FROM (SELECT TOP 3 * FROM SALARY ORDER BY SAL_MODE DESC)A ORDER BY SAL_MODE

2.

SELECT SAL_ID ,SAL_MODE FROM (SELECT row_number() over(order by sal_mode DESC) 'RN', SALARY.SAL_MODE,SALARY.SAL_ID FROM SALARY )A
WHERE A.RN=1 ORDER BY SAL_MODE