One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.
In the following quick video we have demonstrated three different methods to insert multiple values into a single table.
-- Insert Multiple Values into SQL Server
CREATE TABLE #SQLAuthority (ID INT, Value VARCHAR(100));
Method 1: Traditional Method of INSERT…VALUE
-- Method 1 - Traditional Insert
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (2, 'Second');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (3, 'Third');
Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;
Method 2: INSERT…SELECT
-- Method 2 - Select Union Insert
INSERT INTO #SQLAuthority (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';
Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;
Method 3: SQL Server 2008+ Row Construction
-- Method 3 - SQL Server 2008+ Row Construction
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');
Clean up
-- Clean up
DROP TABLE #SQLAuthority;
Related Tips in SQL in Sixty Seconds:
SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
If we like your idea we promise to share with you educational material.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video
