How to insert multiple rows of data using INSERT INTO statement?

Question

I need to create a dropdownlist that list down countries...
I have a list of countries (starting from Afghanistan....till Zimbabwe )

how to write an INSERT INTO statement so that i can insert this list in one shot....

the table name - tblCountry
the column name - strCountry

can anyone help me with this?

thanks in advance.

Answer 1

the code below is written for MySQL RDBMS:



INSERT INTO tblCountry (strCounty) VALUES ('US'),('GB'),('Russia'),...



if you're running this statement on a production database don't forget to add LOCK TABLES tblCountry WRITE; before statement and UNLOCK TABLES; after statement to prevent data integrity to be broken.

Answer 2

I'm not sure you can do that with just any dbms. But, I think MS SQL Server 2008 lets you do the following:



INSERT INTO tblCountry(strCountry) values ('Country1'), ('Country2'), ('Country3');



I don't think this insert statement is valid in other dbms's though, I might be wrong. Your best bet, if the previous doesn't work, is to write a small script or program to loop through the country list you have and create your insert statements or insert the data into the table for you.

Answer 3

MS Excel Rows = 65536 (comes from raising 2 to the power of 16) Cols = IV = 256 (comes from raising 2 to the power of 8) .

Answer 4

If you're using MSSQL, the syntax would be:



INSERT INTO tblCountry (strCountry)

SELECT 'Afghanistan'

UNION ALL

SELECT 'Algeria'

UNION ALL

SELECT 'Albania'



with as many 'union all's between the selects as needed.

Answer 5

you must use commas

for example



insert into tblcountry (a,b,c,d,e,f,g) values (1,2,3,4,5,6,7)



etc. something like this.