How to insert more than one record using a single insert statement?

Question

I am trying to find a way, how I can look for one row in a table with a search for the id, but then I use this to give me the INSERT statement (which creates this row again)

What I am trying to do, is get an audit where any update - checks the state of a row, saves it as its INSERT statement and saves this to an audit file.

Answer 1

In SQL, after creation of table we insert data using INSERT keyword. Suppose we have to insert 10 records, we write 10 INSERT statement for inserting them. However, in SQL Server 20008 a new feature called Row constructor has been introduced which allows us to write a single INSERT statement for inserting more than one record at a time.
Consider the following scenario:

Step 1 : Create table:

CREATE TABLE USER(ID intNOTNULL, NAME varchar(50)NULL)

Step 2 : Insert a record into Table:
Now insert record into this table in traditional ways

INSERT INTO USER(ID, NAME)VALUES (1,'userA')
INSERT INTO USER(ID, NAME)VALUES (2,'userB')
INSERT INTO USER(ID, NAME)VALUES (3,'userC')
INSERT INTO USER(ID, NAME)VALUES (4,'userD')
INSERT INTO USER(ID, NAME)VALUES (5,'userE')
INSERT INTO USER(ID, NAME)VALUES (6,'userF')
INSERT INTO USER(ID, NAME)VALUES (7,'userG')
INSERT INTO USER(ID, NAME)VALUES (8,'userH')
INSERT INTO USER(ID, NAME)VALUES (9,'userI')
INSERT INTO USER(ID, NAME)VALUES (10,'userJ')

Step 3 : Delete all records from the USER table

TRUNCATE TABLE USER
DELETE * FROM USER

Step 4 : Insert all records into table using the Row constructor

INSERT INTO USER(ID, NAME)
            VALUES(1,'userA'),
                   (2,'userB'),
                   (3,'userC'),
                   (4,'userD'),
                   (5,'userE'),
                   (6,'userF'),
                   (7,'userG'),
                   (8,'userH'),
                   (9,'userI'),
                   (10,'userJ')

Answer 2

INSERT INTO StudentDetails (StudentID, StudentName)VALUES(1,'SMITH'),(2,'ALLEN'),(3,'JONES'),
(4,'MARTIN'),(5,'JAMES')