General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old April 11th, 2004, 03:24 AM
MikeAsh MikeAsh is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Posts: 1 MikeAsh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Insert Into (single row vs multiple rows)

I am using a LAMP Script 'Cardfile' the table Structure is shown below. My server is runing php 4.3.3 & MysQL 4.0.16 I am using phpmyadmin to run the querry,

The query below works fine for inserting a single row/record but I want to insert a couple hundred. I have three books on Php/Mysql and it seems they all show how to insert a single row/record but not multiples.

What modifications to this query do I need to make, to addmultiple records. If there is a better (easier) wasy to do this I'd appreciate any tips.

INSERT INTO cardfile (display_name,first,last,address,city,state,zip,ph one,fax,cell,pager,email,website,company,category, comments,created)

VALUES ('Homer Simpson','Homer','Simpson','123 Springfield Dr','Springfield','IN','12345','1-123-456-7890','1-123-456-7890',

'1-123-456-7890','1-123-456-7890','homer@atomicplant.com','thesimpsons.com','a tomic plant inc','customer','I love this guy!',now());

Here is (TABLE cardfile) Structure

id integer(11) not null auto_increment,

userid integer(11) default 0 not null,

display_name varchar(50) null,

first varchar(40) null,

last varchar(40) null,

address varchar(100) null,

city varchar(40) null,

state char(2) null,

zip varchar(20) null,

phone varchar(20) null,

fax varchar(20) null,

cell varchar(20) null,

pager varchar(20) null,

email varchar(50) null,

website varchar(50) null,

company varchar(50) null,

category varchar(40) null,

comments text null,

modified timestamp(14) null,

created timestamp(14) null,

PRIMARY KEY (id) );




Reply With Quote
  #2  
Old July 26th, 2006, 01:37 PM
rpominville rpominville is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2006
Posts: 1 rpominville User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 17 m 59 sec
Reputation Power: 0
To add multiple records in a single query, add multiple value groups in the query. I.e., follow the following format:

INSERT INTO [table]
( [field1], [field2], [field3] ) VALUES
( '[value1.1]', '[value1.2]', '[value1.3]' ),
( '[value2.1]', '[value2.2]', '[value2.3]' ),
( '[value3.1]', '[value3.2]', '[value3.3]' ),
etc.

Here's an example of adding two rows, expanding upon your example:

INSERT INTO cardfile (display_name, first, last, address, city, state, zip, phone, fax, cell, pager, email, website, company, category, comments, created) VALUES ('Homer Simpson', 'Homer', 'Simpson', '123 Springfield Dr', 'Springfield', 'IN', '12345', '1-123-456-7890', '1-123-456-7890', '1-123-456-7890', '1-123-456-7890', 'homer@atomicplant.com', 'thesimpsons.com', 'atomic plant inc', 'customer', 'I love this guy!', now()), ('Eric Cartman', 'Eric', 'Cartman', '987 S. Park Ave.', 'South Park', 'CO', '98765', '1-555-555-5555', '1-555-555-5555', '1-555-555-5555', '1-555-555-5555', 'ecartman@ihateyouguys.com', 'http://www.southpark.com/', 'South Park Elementary', 'customer', 'Respect my authoritah', now());


If you have your data in a normalized, delimited file or in a spreadsheet, you should be able to loop through the data using PHP without having to copy-and-paste or retype it -- just parse the data and write it to the database. It will save you lots of time and significantly shorten your code. You can either perform a database insert for each loop, or concatenate the value groups and perform a single database insert after the loop has ended.

Good luck.

Reply With Quote
  #3  
Old March 18th, 2010, 02:13 AM
bijayani bijayani is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2009
Posts: 8 bijayani User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 34 m 58 sec
Reputation Power: 0
How to insert more than one record using a single insert statement

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.

Hope it did answer the question

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > Insert Into (single row vs multiple rows)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap