(5.6)
drop table tab1;
create table tab1(col1 int,col2 varchar(100),col3 timestamp);
alter table tab1 add constraint tab1pk primary key(col1);
: > a.sql
echo "truncate table tab1;" >> a.sql
echo "insert into tab1 values " >> a.sql
echo "(1, 'test1', now() + interval 1 second)" >> a.sql
for i in `seq 2 100000` ;do
echo ",(${i}, 'test${i}', now() + interval ${i} second)" >> a.sql
done
echo ";" >> a.sql
head a.sql
show variables like 'max_allowed_packet';
set global max_allowed_packet=8388608;
show variables like 'max_allowed_packet';
※再ログインしてから実行
select count(*) from tab1;
select * from tab1 limit 10;
------
: > b.sql
echo "truncate table tab1;" >> b.sql
for i in `seq 1 100000` ;do
echo "insert into tab1 values (${i}, 'test${i}', now() + interval ${i} second);" >> b.sql
done
head b.sql
show variables like 'max_allowed_packet';
set global max_allowed_packet=8388608;
show variables like 'max_allowed_packet';
※再ログインしてから実行
select count(*) from tab1;
select * from tab1 limit 10;
(12cR1)
https://blog.ricemountainer.net/posts/2018/460909004/
drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(100),col3 date);
create index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind1;
create or replace procedure proc1(param1 in number)
as
type t_tab1 is table of tab1%rowtype index by pls_integer;
r_tab1 t_tab1;
begin
execute immediate 'truncate table tab1';
for i in 1..param1 loop
r_tab1(i).col1 := i;
r_tab1(i).col2 := 'test' || to_char(i);
r_tab1(i).col3 := sysdate + i/24/3600;
end loop;
forall i in 1..param1
insert into tab1 values r_tab1(i);
commit;
end;
/
create or replace procedure proc2(param1 in number)
as
type t_tab1 is table of tab1%rowtype index by pls_integer;
r_tab1 t_tab1;
begin
execute immediate 'truncate table tab1';
for i in 1..param1 loop
r_tab1(i).col1 := i;
r_tab1(i).col2 := 'test' || to_char(i);
r_tab1(i).col3 := sysdate + i/24/3600;
end loop;
for i in 1..param1 loop
insert into tab1 values r_tab1(i);
end loop;
commit;
end;
/
set time on
set timing on
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
exec proc1(100000);
exec proc2(100000);
select count(*) from tab1;
select * from tab1 where rownum < 10;
(9.4)
drop table tab1;
create table tab1(col1 int,col2 varchar(100),col3 timestamp);
alter table tab1 add constraint tab1pk primary key(col1);
: > a.sql
echo "truncate table tab1;" >> a.sql
echo "insert into tab1 values " >> a.sql
echo "(1, 'test1', clock_timestamp()::timestamp(0) + interval '1 second')" >> a.sql
for i in `seq 2 100000` ;do
echo ",(${i}, 'test${i}', clock_timestamp()::timestamp(0) + interval '${i} second')" >> a.sql
done
echo ";" >> a.sql
head a.sql
time psql test < a.sql > /dev/null
select count(*) from tab1;
select * from tab1 limit 10;
------
: > b.sql
echo "truncate table tab1;" >> b.sql
for i in `seq 1 100000` ;do
echo "insert into tab1 values (${i}, 'test${i}', clock_timestamp()::timestamp(0) + interval '${i} second');" >> b.sql
done
head b.sql
time psql test < b.sql > /dev/null
select count(*) from tab1;
select * from tab1 limit 10;
(2014)
https://lightgauge.net/database/sqlserver/962/
drop table tab1;
create table tab1(col1 int not null,col2 varchar(100),col3 datetime2);
alter table tab1 add constraint tab1pk primary key(col1);
notepad a.bat
@echo off
@setlocal enabledelayedexpansion
type nul > a.sql
echo truncate table tab1; >> a.sql
for /l %%i in (1,1000,100000) do (
echo insert into tab1 values^(%%i,'test%%i',dateadd^(second, %%i, cast^(getdate^(^) as datetime2^(0^) ^) ^) ^) >> a.sql
call :setvalues
echo ; >> a.sql
)
exit /b 0
:setvalues
for /l %%k in (1,1,999) do (
set /a m=%%i+%%k
echo ,^(!m!,'test!m!',dateadd^(second, !m!, cast^(getdate^(^) as datetime2^(0^) ^) ^) ^) >> a.sql
)
※行値式の数は1000が上限
powershell -command "& {Get-Content a.sql -TotalCount 10}"
echo %TIME%
sqlcmd -d test -b -i a.sql > nul
echo %TIME%
select count(*) from tab1;
select top 10 * from tab1;
------
notepad b.bat
@echo off
type nul > b.sql
echo truncate table tab1; >> b.sql
for /l %%n in (1,1,100000) do (
echo insert into tab1 values^(%%n,'test%%n',dateadd^(second, %%n, cast^(getdate^(^) as datetime2^(0^) ^) ^) ^); >> b.sql
)
exit /b 0
powershell -command "& {Get-Content b.sql -TotalCount 10}"
echo %TIME%
sqlcmd -d test -b -i b.sql > nul
echo %TIME%
select count(*) from tab1;
select top 10 * from tab1;
