в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:20:26Z

1 версия

Новая страница

==Inserts 12000 rows into a table==



<source lang="sql">

CREATE TABLE works_on (emp_no INTEGER NOT NULL,
project_no CHAR(4) NOT NULL,
job CHAR (15) NULL,
enter_date DATETIME NULL)
insert into works_on values (1, "p1", "analyst", "1997.10.1")
insert into works_on values (1, "p3", "manager", "1999.1.1")
insert into works_on values (2, "p2", "clerk", "1998.2.15")
insert into works_on values (2, "p2", NULL, "1998.6.1")
insert into works_on values (3, "p2", NULL, "1997.12.15")
insert into works_on values (4, "p3", "analyst", "1998.10.15")
insert into works_on values (5, "p1", "manager", "1998.4.15")
insert into works_on values (6, "p1", NULL, "1998.8.1")
insert into works_on values (7, "p2", "clerk", "1999.2.1")
insert into works_on values (8, "p3", "clerk", "1997.11.15")
insert into works_on values (7, "p1", "clerk", "1998.1.4")
select * from works_on
GO
-- This procedure inserts 12000 row in the table works_on
declare @i integer, @j integer
declare @job char(20), @enter_date datetime
declare @project_no char(4)
declare @dept_no char(4)
set @i = 1
set @j = 1
set @job = "Analyst"
set @enter_date = GETDATE()
set @dept_no = "d1"
while @i < 3001
begin
while @j < 5
begin
if (@j = 1) set @dept_no = "d1"
else if (@j = 2) set @dept_no = "d2"
else if (@j = 3) set @dept_no = "d3"
else set @dept_no = "d4"
insert into works_on
values (@i, @dept_no, @job, @enter_date)
set @j = @j+1
end
set @i = @i+1
set @j = 1
end
drop table works_on
GO

</source>

==Inserts 3000 row into a table==



<source lang="sql">

CREATE TABLE employee (emp_no INTEGER NOT NULL,
emp_fname CHAR(20) NOT NULL,
emp_lname CHAR(20) NOT NULL,
dept_no CHAR(4) NULL)
insert into employee values(1, "Matthew", "Smith", "d3")
insert into employee values(2, "Ann", "Jones", "d3")
insert into employee values(3, "John", "Barrimore","d1")
insert into employee values(4, "James", "James", "d2")
insert into employee values(5, "Elsa", "Bertoni", "d2")
insert into employee values(6, "Elke", "Hansel", "d2")
insert into employee values(7, "Sybill", "Moser", "d1")
select * from employee
GO

-- This procedure inserts 3000 row in the table employee
SET SHOWPLAN_TEXT OFF
GO
declare @i integer, @emp_no integer
declare @emp_Iname char(20), @emp_fname char(20)
declare @dept_no char(4)
set @i = 1
set @emp_Iname = "Smith"
set @emp_fname = "Jane"
set @dept_no = "d1"
while @i < 3001
begin
insert into employee
values (@i, @emp_fname, @emp_Iname, @dept_no)
set @i = @i+1
end
select count(*) from employee
GO
drop table employee
GO

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/Insert Delete Update/Batch Insert - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия