Using stored procedures to load data

Here is an example of how to load data using the stored procedure.

Using this approach gives the user the ability to perform complex transformations inside the database, for example, the user may nave a look table that is just too big to pull out or the user may want to reuse existing code.

Please note that bulk loading is aways faster

Table Creation Script

CREATE TABLE [dbo].[orders](
[customerid] [char](5) NOT NULL,
  [orderno] [decimal](20,
  5) NOT NULL,
  [orderdate] [smalldatetime] NOT NULL,
  [amount] [decimal](16,
  2) NOT NULL
)
ON [PRIMARY]

Stored Procedure Script

CREATE PROCEDURE [dbo].[p_Populate_Data]
(
@customerid char(5),
  @orderno decimal(20,
  5),
  @orderdate smalldatetime,
  @amount decimal(16,
  2)
)
AS
begin
set nocount
on
insert into orders (customerid,
  orderno,
  orderdate,
  amount)
values(@customerid,
  @orderno,
  @orderdate,
  @amount)
end

Stored Procedure1