CODEWITHSUNDEEP
phpmysqlsql
Koala
Koala

Reputation: 5543

What is the best way to create a query with a big set of data?

I have about 360 lines of data which I need to make into 360 query's for SQL, Is there any quick way of putting all the data into one query instead of having to put them all in separate, for example here is just 3 lines of data I have:

   1,81,32
   1,101,82
   1,60,65

I have to make this data into query's so these 3 would look like:

   INSERT INTO `Fixtures` (weeks, HomeID, AwayID) Values (1,81,32);
   INSERT INTO `Fixtures` (weeks, HomeID, AwayID) Values (1,101,82);
   INSERT INTO `Fixtures` (weeks, HomeID, AwayID) Values (2,60,65);

But I have 360 lines of this, so It will be hard to make these all into query's 1 by 1, Is there any quicker way of doing this, Like just one query to insert them all?

Upvotes: 0

Views: 76

Answers (3)

wesside
wesside

Reputation: 5740

If its in a file, use file(), it will separate them line by line.

$file = 'test.txt';

$lines = file($file); 

foreach ($lines as $line)
{
  // Can build an output of the inserts to use as an .sql file, or actually call the db to insert
  $output .= 'INSERT INTO Fixtures (weeks, HomeID, AwayID) Values (' . trim($line) . ')'; 
}

Upvotes: 1

Tom
Tom

Reputation: 22841

I'm assuming MySQL because you've tagged this with PHP, but most any database will allow multiple inserts in one query, so your query would look like:

INSERT INTO `Fixtures` (weeks, HomeID, AwayID) Values (1,81,32), (1,101,82), (2,60,65);

Or did you mean crafting the insert text via code instead of by hand. In that case, we'll need to know where the data is now.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

You can usually use INSERT...SELECT syntax.

This syntax varies by db, but is often something like:

INSERT INTO `Fixtures` (weeks, HomeID, AwayID) 
select 1,81,32
union all select 3,31,22

Another variation is:

INSERT INTO Fixtures
    (`weeks`, `HomeID`, `AwayID`)
VALUES
    (1, 81, 32),
    (3, 31, 22)
;

Upvotes: 2

Related Questions