bulk insert - 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 2005 Forums

Transact-SQL (2005)

bulk insert

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2010-03-11 : 12:29:37

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources