1

I have a table called rent on MySQL

id, int (5), autoincrement PRIMARY KEY

dNo, int (5)

pRent, varchar (5)

status, varchar (10)

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
methuselah
  • 12,766
  • 47
  • 165
  • 315

5 Answers5

1

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`
J V
  • 11,402
  • 10
  • 52
  • 72
  • it works great but how do i insert 300 at once. should i copy and paste? – methuselah Nov 17 '10 at 09:35
  • 1
    Lol, assuming you have these values stored somewhere you could write a script or something to load them into an insert statement, otherwise you will have to type it all in manually... The way I've given you should only need to say `INSERT INTO rent (id,pRent,status) VALUES ((NULL,0,"vacant"),(NULL,0,"vacant"))` but you will need something to generate the statement as no-one wants to write that all out... – J V Nov 17 '10 at 09:40
0

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".

Filipe YaBa Polido
  • 1,656
  • 1
  • 17
  • 39
  • I agree, that's what I suggested first, and seeing as this seems to be a one-time query, that's what he should do ;) – J V Nov 17 '10 at 10:11
0

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
methuselah
  • 12,766
  • 47
  • 165
  • 315
  • after doing this i used the sql command "update rent set dNo=id" – methuselah Nov 17 '10 at 09:52
  • 1
    yes, this is a way to generate sequences in mysql; there are other ways, see http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql. however this does not really relate to your question. – Unreason Nov 17 '10 at 09:56
0

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

Unreason
  • 12,556
  • 2
  • 34
  • 50
0

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!
tk_1
  • 1
  • 2