Sunday, 20 October 2013

SQL Query to Insert Multiple Rows or Records into a Table with Single INSERT Statement

SQL Server- Inserting Multiple Rows or Records a with Single INSERTStatement
Creation of a Table Called EMPLOYEE :

CREATE TABLE EMPLOYEE(EID INT NOT NULL , ENAME VARCHAR(10),
SEX VARCHAR(6) NOT NULL,SAL DECIMAL(10,2),PRIMARY KEY(EID));

METHOD I :
SQL Query to Insert Multiple Records at a time into a Table called "EMPLOYEE".

INSERT 
 INTO EMPLOYEE(EID,ENAME,SEX,SAL)
VALUES (1,'JAN','M',5000.50),
(2,'FEB','F',8000.80),
(3,'MAR','M',6000.60) ;

SELECT *FROM EMPLOYEE;
TRUNCATE TABLE EMPLOYEE;

METHOD II :
SQL Query to Insert Multiple Records at a time  a "EMPLOYEE".

INSERT 
 INTO EMPLOYEE(EID,ENAME,SEX,SAL)
SELECT 1,'JAN','M',5000.50 UNION ALL
SELECT 2,'FEB','F',8000.80 UNION ALL
SELECT 3,'MAR','M',6000.60 ;

SELECT *FROM EMPLOYEE;
TRUNCATE TABLE EMPLOYEE;

METHOD III :
SQL Query to Insert Multiple Records with out specifying Feild Names a "EMPLOYEE".

INSERT
INTO EMPLOYEE VALUES(1,'JAN','M',5000.50);
INSERT 
INTO EMPLOYEE VALUES(2,'FEB','F',8000.80);
INSERT
 INTO EMPLOYEE VALUES(3,'MAR','M',6000.60);

SELECT *FROM EMPLOYEE;
TRUNCATE TABLE  EMPLOYEE;

METHOD IV :
SQL Query to Insert Multiple Records a  "EMPLOYEE".

INSERT 
 INTO EMPLOYEE(EID,ENAME,SEX,SAL)VALUES(1,'JAN','M',5000.50);
INSERT 
 INTO EMPLOYEE(EID,ENAME,SEX,SAL)VALUES(2,'FEB','F',8000.80);
INSERT 
 INTO EMPLOYEE(EID,ENAME,SEX,SAL)VALUES(3,'MAR','M',6000.60);

SELECT *FROM EMPLOYEE;
TRUNCATE  TABLE EMPLOYEE;

DROP TABLE EMPLOYEE;

Note :
The TRUNCATE EMPLOYEE Query only deletes all the Records from the EMPLOYEE with out deleting the entire Table Structure from the Database.

Where as , The DROP TABLE EMPLOYEE Query deletes the entire Table with Structure from the Database.

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts