March 11, 2010 at 12:29 pm
Greetings all,
I am using .net to perform a bulk insert.
I am doing this by creating a bunch of empty cells, and let the user fill up as many cells as possible with just 4 fieldnames.
Here is where I am running into problems.
Here is the table structure:
Table1
reportgroupID PK identityseed
ReportName nvarchar(50)
StartDate DateTime
EndDate DateTime
Table2
QuestionID PK identitySeed
Reportname nvarchar(50) (more like fk) from table1
reportTitle nvarchar(50)
ReportOrder int
IsQuestion bit
Looking at the 2 tables above,
I have 2 issues.
I want the fieldname called ReportOrder to be inserted automatically starting from 1 to N, each time a new ReportName is selected.
For instance, there could be 10 different records inserted at same time.
After the user hits the insert button, sample data should look similar to this:
ReportName ReportTitle ReportOrder IsQuestion
Aflac Aflac Comm 1 Yes
Aflac Reed Medical Center 2 No
Aflac Auto Insurance 3 No
Notice 2 things: Anytime there is an insert statement, ReportName is the same, regardless of how many cells are filled up. The example shows 3 cells but could be up to 30.
My first question is how to do I grab reportname and have same report name go into any cells that are filled up?
Given the example above where 3 cells are filled up, how do I grab reportname called Aflac and have it automatically fill up the 3 rows that are used up above?
The second question is that each insert has its own report Order which usually begins with first row being filled up till the last row.
Again, given the example above where only 3 rows of data are filled up, Report Order is 1 2, 3.
Even if we select the same Report new for new insert (not an update), we want the reportOrder to start again from number 1 to however many rows are filled up.
How do I handle this?
I am hoping that I didn't confuse you too much.
If so, please ask me for further clarifications.
Below is the stored proc I am trying to use for the insert statement.
Thanks for your assistance in advance.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertQuestion]
(
@ReportName nvarchar(50),
@ReportTitle nvarchar(50),
@ReportOrder integer,
@IsQuestion(bit)
)
AS
BEGIN
INSERT INTO CUSTOMER (ReportName,ReportTitle,ReportOrder,IsQuestion)
VALUES(@ReportName,@ReportTitle,@ReportOrder,@IsQuestion)
END
March 15, 2010 at 2:24 pm
Found your post a bit confusing, [what . net technology are you referring to?] but as I understand, you're trying to populate a table from a .net form using something similar to a grid component, and need the reportOrder to be incremented for each line in the form, whilst initialising the counter for each new report name / description irrespective of what might already be in the table ?
from the S_proc you use to populate the table I deduce that sql is processing one row at a time any case.
If I understand you correctly, then the incrementing and initialisation of your counters should easiest be handled by the .net form instead of SQL backend.
if there's only one report entry in the form per submission, it should simply be a case of manipulating the grid rowcounters to be sent to the s_proc.
for multiple reports, you might need to sort and code some standard control-breaks.
If however your counters are dependant on the content of the sql table, instead of the form, then you might want to look at using a count or max +1 in your s_proc to populate.
all above will have quite a performance hit.
Question one's answer would depend on the layout of your .net form
If on the other hand you're atempting some sort of data extract, using one of the .net technologies, then staging during your ETL might be the right consideration...
In short - I guess your answer would depend on a closer inspection of your requirements, and input source design.
please let me know if I'm way off track, or add some more background of your design to maybe make things a bit clearer ?
March 15, 2010 at 4:46 pm
thanks Daniel.
I apologize if my questionis a bit confusing.
What I wanted to do is reset the ReportOrder.
For instance, let's assume that I just populated a table with 10 records for ReportName called Esquire.
I can get the ReportOrder to insert numbers 1 through 10 for Esquire.
If I want to populate a new set of records,say 8 records this time,
I wouldn't want ReportOrder to increment the counter from 10 to 18.
Rather, I would reset the counter and start count again from 1 to 8.
Net batch inserts will start again from 1 to N ReportOrder numbers.
Is this a bit clearer?
BTW: I am using .net of vb flavor.
Thanks a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply