Inserts 12000 rows into a table : Batch Insert « Insert Delete Update « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Insert Delete Update
3. Batch Insert

Inserts 12000 rows into a table

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


           
       

Related examples in the same category