Bulk Record Creation Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bulk Record Creation Question

Author

Topic

ewade
Starting Member

36 Posts

Posted - 2002-02-07 : 14:27:45

Here I am with another bizarre question...

Is it possible to create a stored procedure that will perform bulk automated record creation based on parameters supplied at run-time?

I've created a table that will be used during a data merge process in an external application. Certain fields are incremental integer fields, and some are nvchar fields. I'd like to be able to trigger a stored procedure on the server that will ask the user how many new records to create and the value to supply the nvchar fields for each of these new records.

More specifically, I'm trying to build some automation (using Teleform) into a survey process. Each survey packet has a unique numerical indentifier (used to associate sheets from the same packet in case they are shuffled). The survey will be conducted in batches, and each batch will have some common information pre-filled during the merge process.

My end goal is to create a front-end in Access that will trigger this stored procedure and supply the parameters from a form. Is this feasible?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-02-07 : 16:02:35

yes

ewade
Starting Member

36 Posts

Posted - 2002-02-08 : 11:54:05

Okay, follow up question, how do I accomplish this?

I'm an Access deveoper who is learning SQL Server as I go...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 11:57:38

Can you provide more detail on your table structure, along with some sample data, and also some sample input that you are importing?

You could write a SQL stored procedure that accepts an integer as a paramter, and it can use this to create as many rows as needed, but without more detail (such as your current VB code) it'll be hard for us to go any further.

ewade
Starting Member

36 Posts

Posted - 2002-02-09 : 21:13:19

Since I wasn't sure how to begin, I hadn't even gotten that far. Let's say that each row in my master table contains seven fields (PacketID, ProgramCode, CourseCode, SectionCode, Remote_Sta, Remote_Uid, MergeType)

PacketID - incremental integer field used as primary key

Remote_Sta, Remote_Uid, MergeType- required fields for print merging to third-pary software. Default to appropriate values when new records are created.

ProgramCode, CourseCode, SectionCode- These three fields are what I want to use values specified by the user when they're generating new rows. For example, Mary opens the appropriate dialog box in the MS-Access front-end to create new records. She specifies to create 30 new rows, and then types in the ProgramCode, CourseCode and SectionCode values to use for these 30 new rows. Clicking the "OK" button triggers the stored procedure and "fills down" the values that Mary specified.

Nazim
A custom title

1408 Posts

Posted - 2002-02-09 : 23:46:07

Hi ,

Am curious about your process. will like to know why is your system this way. might be someone here can give you a better option.

Anywayz this Stored procedure should do what you are looking for.

create procedure AddNRecordsSP(@noofrec int,@PCode varchar(10),@CCode varchar(10), @SCode varchar(10))
As
Set nocount on
Declare @incr int
begin tran
set @incr=0

While @incr < @noofrec
begin
insert into table( ProgramCode, CourseCode, SectionCode) values(@pCode,@cCode,@sCode)
set @incr=@incr+1
end

IF (@@ERROR <> 0)
BEGIN
RAISERROR ('Error in Insert', 16, 1)
ROLLBACK TRANSACTION
END

commit tran

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 02/10/2002 02:58:34

ewade
Starting Member

36 Posts

Posted - 2002-02-10 : 21:01:56

Thanks... that makes sense, now that I look at it. I know it's a bizarre way of doing things, but I work for a public agency. My goal was to create a semi-automated survey process without buying any additional hardware or software. I wanted to build in some flexiblility so that I wouldn't have to always be the one called when they needed to generate new data for the merge tables (each survey has a unique 2-D barcode that contains control data used to track when the surveys were printed, what funding source the surveys were associated with, and which pages from a packet belonged together. The obvious solution in a parameter query that creates records and supplies default values to fields based on user variables. I could have done this in Access, but I wanted to put it on our SQL server both as a learning exercise and to make the inevitable migration to our intranet easier in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources