How to insert multiple rows into table

Checking the Employee table alredy exists or not.if exists droping table.

IF OBJECT_ID('Employee','U') IS NOT NULL

DROP TABLE Employee

Ceating the Employee table

CREATE TABLE Employee

(

ID INT IDENTITY(1,1) PRIMARY KEY ,

NAME VARCHAR(20),

SALARY INT

)

Method -1:

Here inserting only one row into Employee table

INSERT INTO Employee (NAME,SALARY) VALUES('Rakesh',5000)

DELETE FROM Employee

Method -2:

Here inserting multiple rows into Employee table using union all statement.

INSERT INTO Employee

SELECT 'Rakesh',5000

UNION ALL

SELECT 'Ramu',6000

UNION ALL

SELECT 'Naresh',7000

DELETE FROM Employee

Method -3:

Here inserting multiple rows into Employee using values statement with comma separated (2008 sql server).

INSERT INTO Employee (NAME,SALARY)

VALUES ('Rakesh',5000)

,('Ramu',6000)

,('Naresh',7000)

DELETE FROM Employee

Comments

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee ( EmpId int identity ( 1 , 1 ) primary key , ...

@@SERVERNAME

@@servername returns the name of the server currently running. Syntax: @@SERVERNAME SELECT @@SERVERNAME O/P sys

Difference between LEN and DATALENGTH

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...

Tech Learn

Search This Blog