How to insert 200 new records? [closed]

Question

How to insert 200 new records? [closed]

Navigation

#1 by (3 votes)
#2 by (0 votes)

2

I need to insert new data into one table, but there are many. I did like this:

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

But there are many, you can not do 1 by 1. Could anyone help me?

sql select sql-insert

asked by anonymous 13.01.2016 / 15:02

2 answers

How to highlight a row in the query result php + sql Retrieving the name of a button on the Swift sender

score 3 · Answer 1

Two possibilities:

Sequential inserts

You can concatenate instructions INSERT as follows:

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 the sequence preparation, you can execute all inserts with a single request.

Collection via UNION

Concatenate all values to be inserted in a collection in memory, via UNION, to insert them into 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 methods of BULK INSERT (bulk insertion).

score 0 · Answer 2

Insert containing multiple values:

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

This is equivalent to executing 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.