Using Stored Procedures to load data

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

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

Please note that bulk loading is always 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