SQL Server/T-SQL/Insert Delete Update/Batch Insert

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>