Insert multiple records using one single insert statement in Stored procedure

You can use Bulk Insert Task

The Bulk Insert task copies data from text files into a SQL Server table or view.
If you have data in text files, and the data requires no transformation, the Bulk Insert task is the quickest way to load data from text files into SQL Server.

You can use a format file together with the Bulk Insert task to define the fields and data types, or you can set the bulk insert options in the task.

The Bulk Insert task supports both XML and nonXML format files.

For more information about this task, see Bulk Insert Task[^].

The Bulk Insert task uses a File connection manager to connect to the source file and an OLE DB connection manager to connect to the Database Engine.

For more information, see Flat File Connection Manager[^] and OLE DB Connection Manager[^].


Look at some examples how you can insert multiple records:

-- Create table
CREATE TABLE dbo.MyTable 
            (my_value VARCHAR(20) NOT NULL,
             my_rec   INT         NOT NULL);
	          
-- Insert multipe records into table - prior to sql server 2008 (option 1)
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('One',
            1);	          
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Two',
            2);
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Three',
            3);	    

-- Insert multipe records into table - prior to sql server 2008 (option 2)
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
SELECT      'Four', 4
UNION ALL
SELECT      'Five', 5
UNION ALL
SELECT      'Six', 6;

-- Insert multiple records into table - sql server 2008
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Seven', 7),
           ('Eight', 8),
           ('Nine', 9)

-- Select records
SELECT * FROM dbo.MyTable;

Also refer:
Bulk Inserts[^]
Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function[^]