Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
11
Erster Beitrag:
vor 5 Jahren, 8 Monaten
Letzter Beitrag:
vor 4 Jahren, 10 Monaten
Beteiligte Autoren:
Marc De Swert, David Martin, RAUL2, Allard, issah, Charles Jolly, Al, Yogi Yang, Alexander S., Alexander S.pcs.crosspost

How to insert (SQL) many records to the table a time?

Startbeitrag von Alexander S.pcs.crosspost am 20.08.2012 15:09

In WinDev, the INSERT statement is single-tuple: It only adds one record at a time.

The following query IS NOT accepted:
INSERT INTO CUSTOMER VALUES
(1, 'Doe', 'John')
(2, 'Wolff', 'Walter')
(3, 'Black', 'Roger')

But how to insert many records to the table a time? I think to insert one by one will be very slow, if we have about 15000 records.

Message forwarded from pcsoft.us.windev

Antworten:

Hello Alexander

If by "table" you mean a memory table then you can use FileToMemoryTable()

Regards
Al

von Al - am 21.08.2012 01:23
Never tried but I think this should work

INSERT INTO CUSTOMER VALUES(1, 'Doe', 'John');
INSERT INTO CUSTOMER VALUES(2, 'Wolff', 'Walter');
INSERT INTO CUSTOMER VALUES(3, 'Black', 'Roger');

So what you do is build a string with SQL query and execute this query!

HTH

Yogi Yang

von Yogi Yang - am 21.08.2012 07:20
Quote
Yogi Yang

INSERT INTO CUSTOMER VALUES(1, 'Doe', 'John');
INSERT INTO CUSTOMER VALUES(2, 'Wolff', 'Walter');
INSERT INTO CUSTOMER VALUES(3, 'Black', 'Roger');


Thank you, I will try. It need to test a speed of this...

von Alexander S. - am 21.08.2012 14:55
Like a mysql backup process (see below) , you disable all keys, then insert all records and reenable all keys again, but if you have to validate duplicate records this method wont work.

_____________________________________________________________
DROP TABLE IF EXISTS `diccionario`;
CREATE TABLE `diccionario` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`palabras` varchar(50) NOT NULL,
`colocar_en` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `word_unico` (`palabras`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `diccionario`
--

/*!40000 ALTER TABLE `diccionario` DISABLE KEYS */;
INSERT INTO `diccionario` (`id`,`palabras`,`colocar_en`) VALUES
(1,'Marketing','*NO TIENE*'),
(2,'Ventas','*NO TIENE*'),
(3,'Ayudante Bodega','*NO TIENE*');
/*!40000 ALTER TABLE `diccionario` ENABLE KEYS */;
___________________________________________________________________

von Charles Jolly - am 21.08.2012 18:49
Would be great if we knew the source and destination of the data..

issah

von issah - am 22.08.2012 08:06
hai Alexander

processing in one time
is selecting all the needed rows and put them in the destination table

insert into destiantion_table
SELECT source_col1, source_col2...
from some_table, some_table_to ...
.oc oc where cdlcoc

von Marc De Swert - am 22.08.2012 12:17
complete the code:

hai Alexander

processing in one time
is selecting all the needed rows and put them in the destination table

insert into destination_table
SELECT source_col1, source_col2...
from table(s)
joins table(s)
where criteria(s)

Marc :cool:

von Marc De Swert - am 22.08.2012 12:19
Quote
Marc De Swert
insert into destination_table
SELECT source_col1, source_col2...
from table(s)
joins table(s)
where criteria(s)


This is what I was going to recommend also, as it is how I have done it with other SQL DBs, but my first quick look at the WD help file showed this:

Quote
The INSERT command cannot be used to copy data from a HyperFileSQL file to another HyperFileSQL file.


But when taking a second look I see that right below that it says:

Quote
INSERT with selection

The INSERT statement can also be used to insert a selection of records made by a SELECT query. Its use format becomes:

INSERT INTO FileName [(NameOfItems)] SELECT ...



So, am I reading the help file wrong or are those two statements saying the opposite thing?

von David Martin - am 22.08.2012 14:36
I confirm that INSERT ... SELECT... to make a bulk insert works fine in WD15.
Regards.

von RAUL2 - am 17.06.2013 16:10
// you want all the records of a table
For all selected rows of table_blabla.......

// specifi what is what

file.discription = table.colum

// save the file
Hadd(table )


regards
Allard

von Allard - am 17.06.2013 17:07
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.