DaniWeb IT Discussion Community - View Single Post - insert multiple rows in database

Re: insert multiple rows in database

Jun 22nd, 2007

Just replying for anyone looking for an answer. Using MS SQL, you can do the following:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
INSERT INTO test_table
SELECT 62
UNION
SELECT 91
UNION
SELECT 95
UNION 
SELECT 98
UNION 
SELECT 99
INSERT INTO test_table
SELECT 62
UNION
SELECT 91
UNION
SELECT 95
UNION 
SELECT 98
UNION 
SELECT 99

This will insert 5 rows into the table. Testing this on SQL Server 2000, the following works:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TABLE test
(
	val1 VARCHAR(10), 
	val2 VARCHAR(10), 
	val3 VARCHAR(10), 
	val4 VARCHAR(10)
)
GO
 
INSERT INTO test
(val1, val2, val3, val4)
(SELECT 'a1', 'a2', 'a3', 'a4')
UNION
(SELECT 'b1', 'b2', 'b3', 'b4')
UNION
(SELECT 'c1', 'c2', 'c3', 'c4')
GO
CREATE TABLE test
(
	val1 VARCHAR(10), 
	val2 VARCHAR(10), 
	val3 VARCHAR(10), 
	val4 VARCHAR(10)
)
GO

INSERT INTO test
(val1, val2, val3, val4)
(SELECT 'a1', 'a2', 'a3', 'a4')
UNION
(SELECT 'b1', 'b2', 'b3', 'b4')
UNION
(SELECT 'c1', 'c2', 'c3', 'c4')
GO