How to insert 200 new records?

Asked

Viewed 130 times

2

I need to insert new data into a table, but there are many. I did so:

insert into pessoafisica (coluna1, coluna2, coluna 30) select (dado1 dado2 dado30)

But there are many, can not do 1 by 1. Someone could help me?

sql select sql-insert

  • Anyway it will be like this. You can use php, with arrays declared with values, then a for to go through the arrays and select.

  • 2

    Without knowing what kind of data they are, and the schema of the tables involved, it becomes difficult to help. Be more specific in your problem, add the schema of the tables. If it is from one table to others, it is possible using store Procedure or Function.

  • Actual or test data?

  • I think you need to elaborate a little more on your problem.

  • 1

    I think you can loop, like: Begin for R in( select dado1, dado2, dado30 from table ) loop Insert into table2 values(R.dado1, R.dado2, R.dado30); end loop; end;

  • I already know how it is. In select I put the name of the columns and it already takes all the data from that column at once.

  • 1

    You would need to specify where the data is coming from whether it is from a table, system or Excel ....

  • i imported from excel to database

Show 3 more comments

2 answers

3

Two possibilities:

  • Sequential inserts

You can concatenate instructions INSERT just the way:

INSERT INTO pessoafisica (coluna1, coluna2, coluna30) VALUES (1, 2, 3);
INSERT INTO pessoafisica (coluna1, coluna2, coluna30) VALUES (4, 5, 6);
INSERT INTO pessoafisica (coluna1, coluna2, coluna30) VALUES (7, 8, 9);

At the end of sequence preparation, you can run all the Inserts with a single request.

  • Collection via UNION

Concaten all the values to be inserted in a collection in memory, via UNION, to then insert them in the target table:

INSERT INTO pessoafisica (coluna1, coluna2, coluna30) 
SELECT * FROM 
     (SELECT 1, 2, 3 UNION
      SELECT 4, 5, 6 UNION
      SELECT 7, 8, 9);

Depending on the technology (MS SQL Server, Oracle, etc.) you also have at your disposal methods BULK INSERT (mass insertion).

  • Downvoter, feel free to point out the problem in my answer; thank you.

  • Thanks for your help

  • Always happy to help, @Laiane.

0

Insertion containing multiple values:

insert into nome_tabela VALUES
('foo 1', 'f1'),
('foo 2', 'f2'),
('foo 3', 'f3')

This equals running the Insert 3 times:

insert into nome_tabela VALUES ('foo 1', 'f1');
insert into nome_tabela VALUES ('foo 2', 'f2');
insert into nome_tabela VALUES ('foo 3', 'f3');

The difference is that the first form is a single statement. In the second form are 3, which consume more processes.

  • The table I imported from excel to the database had 1000 rows, I could not insert 1 by 1. Thanks for the help

Browser other questions tagged sql select sql-insert

You are not signed in. Login or sign up in order to post.