MySQL query to insert 300 rows at once

I have a table called rent on MySQL

id, int (5), autoincrement PRIMARY KEY

dNo, int (5)

pRent, varchar (5)

status, varchar (10)
开发者_Python百科

I need to insert 300 rows into this table

id and dNo need to match

so in the end we will have id/dNo up until 300. How can this be done?

pRent will have a default value of 0

status will have a default value of vacant

What SQL query should I use to insert all 300 rows in at once with id/dNo autoincrement up to 300?

Assuming the id is set to auto increment you can just insert the lot then do an update table set dNo=id

I'm not sure if you can set dNo=id during insert as the id would be NULL

You might be able to set the dNo default value to id

Edit:

INSERT INTO `test` (`id`, `id2`) VALUES ('2', `id`)

Works fine, but not if I set id=NULL.

Looks like you'll have to user triggers

CREATE TRIGGER set_nDo AFTER INSERT ON test FOR EACH ROW SET `nDo`=`id`

i found a great way to insert 1000

   INSERT INTO
    myTable
    (
    nr
    )
SELECT
    SEQ.SeqValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ

Good point "JV". But he can also set a default value, and the do an UPDATE after, so he can use the auto-increment value at "id".

300 records is not a lot, but depending on the format you have the data in you can possibly use LOAD DATA

you can put your data into a CSV file and then import it into MySQL using MySQL Workbench. I think that would be a great way to go about!

Steps:

1. create an empty table with the columns you want to have
2. Under Navigator Column on the left hand side of the MySQL Workbench, go to Schemas
3. Now, you'd be able to see the different datasets (tables) which you've created
4. Right click on the particular dataset where you want your data to be filled
5. Click on Table Import Wizard
6. Put in the path of the .CSV file and you'd be good to go!