How to Use a SQL for Loop to Insert Rows into Database

How to use a SQL for loop to insert rows into database?

Hopefully I've understood what you need (tested on 8.2):

INSERT INTO articles (id, name)
SELECT x.id, 'article #' || x.id
FROM generate_series(1,10000000) AS x(id);

How to use for loop for insert in Oracle?

Don't do it that way, especially don't COMMIT within a loop.

Use a row generator; there are many techniques, one of them being this one:

SQL> create table test (snb number, real_exch varchar2(20));

Table created.

SQL> insert into test (snb, real_exch)
2 select 385000000 + level - 1, 'GSMB'
3 from dual
4 connect by level <= 10; --> you'd put a million here

10 rows created.

SQL> select * from test;

SNB REAL_EXCH
---------- --------------------
385000000 GSMB
385000001 GSMB
385000002 GSMB
385000003 GSMB
385000004 GSMB
385000005 GSMB
385000006 GSMB
385000007 GSMB
385000008 GSMB
385000009 GSMB

10 rows selected.

SQL>

How can INSERT INTO a table 300 times within a loop in SQL?

You may try it like this:

DECLARE @i int = 0
WHILE @i < 300
BEGIN
SET @i = @i + 1
/* your code*/
END

How to use a for loop to insert multiple records in a database table

Here is how you can do that.

for ($x = 1; $x <= 10; $x++) {

$sql_abc = "INSERT INTO $tbl_abc VALUES ($x)";

//this query will execute for each value of $x
mysqli_query($conn, $sql_abc);
}

Loop with DB names inserting

If all of the tables are identical, you could use sp_MSforeachdb along with an IF EXISTS to go through all of your databases, then insert the table if found into a temp table. You would just need to change the column names from col1, col2, ... to whatever your actual table schema is. Although you'd need to exclude any databases that contain a Table1 table that you don't want included in your data set.

DROP TABLE IF EXISTS #insertTable (col1 varchar(100), col2 varchar(100),...)

DECLARE @command varchar(1000)
SELECT @command = 'USE ?
EXEC(''IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''Table1'''')
SELECT col1, col2, ... FROM Table1'')'

INSERT INTO #insertTable (col1, col2, ...)
EXEC sp_MSforeachdb @command


Related Topics



Leave a reply



Submit