Inserting Multiple Rows in a table with sql

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

Old Forums

CLOSED - General SQL Server

Inserting Multiple Rows in a table with sql

Author

Topic

SQLScrub79
Starting Member

7 Posts

Posted - 2005-07-28 : 16:32:09

I am developing a webform using ASP. My users will be filling out approximately 6 cells, and are allowed to add 40 records containing six cells each. I want to know if there is any sample code to help me code for them to hit the save button and all 40 records be inserted into one sql table.

Anyone with advice, tips, suggestions please respond.

Thx,
Stressed Out Programmer

jhermiz

3564 Posts

Posted - 2005-07-28 : 16:43:44

40 records, are these the same records? Are their multiple forms? How are you getting the data if its only 6 cells.
You will need to produce a mass insert but we need more info from you. Give us more details on how the data is entered (the UI) and give us the DDL (table structures) and sample DML (the inserts)

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

SQLScrub79
Starting Member

7 Posts

Posted - 2005-07-28 : 16:47:52

Its an online form where the user fills out type of test, subject area, score, credit awarded, comments. They can fill out upto 40 lines with different information such as different tests, subject area, etc. What I am trying to do is connect the ASP page to a SQL database, and have the information submit with one click, where the user hits the save button once and the data they filled out goes into 40 rows in the table. Does that make better sense? I'm a new asp programmer.

SQLScrub79
Starting Member

7 Posts

Posted - 2005-07-28 : 16:49:48

this is my code so far, but I know this is for just one row insert at a time.

<%
REPYEAR=COLLECTIONREPYEAR

Test = REQUEST("LBTest")
Subject_Area = REQUEST("LBSUBJECT_AREA")
SCORES = REQUEST("LBSCORES")
CREDIT_AWARDED = REQUEST("LBCREDIT_AWARDED")
COMMENTS = REQUEST("LBCOMMENTS")
RECNO = REQUEST("LBRECNO")

SqlInsert = "INSERT INTO InstitutionManagement.dbo.b10_partC " & _
"(unitid, repyear,test, subject_area, scores, credit_awarded, comments, recno)" & _
"VALUES " & _
" ('" & UNITID & "', " & _
" '" & REPYEAR & "', " & _
" '" & Test & "', " & _
" '" & Subject_Area & "', " & _
" '" & Scores & "', " & _
" '" & Credit_Awarded & "', " & _
" '" & Comments & "', " & _
" '" & RecNo & "') "

response.Write sqlInsert
'connx.Execute sqlInsert
'Response.Redirect "B10_PARTCStocked.asp"

%>

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-28 : 17:10:51

Change the format of your INSERT statement to insert from a SELECT UNION ALL with one select for each row.

INSERT INTO InstitutionManagement.dbo.b10_partC 
	(unitid, 
	repyear,
	test,
	subject_area,
	scores,
	credit_awarded,
	comments,
	recno)
select
	unitid = 'data1a', 
	repyear = 'data1b', 
	test = 'data1c', 
	subject_area = 'data1d', 
	scores = 'data1e', 
	credit_awarded = 'data1f', 
	comments = 'data1g', 
	recno = 'data1h'
union all
select
	unitid = 'data2a', 
	repyear = 'data2b', 
	test = 'data2c', 
	subject_area = 'data2d', 
	scores = 'data2e', 
	credit_awarded = 'data2f', 
	comments = 'data2g', 
	recno = 'data2h'
union all
select
	unitid = 'data3a', 
	repyear = 'data3b', 
	test = 'data3c', 
	subject_area = 'data3d', 
	scores = 'data3e', 
	credit_awarded = 'data3f', 
	comments = 'data3g', 
	recno = 'data3h'
... And so on...

You could also use a stored procedure, and pass all of the values in input parameters.

CODO ERGO SUM

jhermiz

3564 Posts

Posted - 2005-07-28 : 17:16:06

Try putting the insert into a stored procedure and calling it from your asp page you can call it as many times as you need to.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

SQLScrub79
Starting Member

7 Posts

Posted - 2005-07-29 : 07:32:13

Thanks guys! I really appreciate your wisdom and knowledge on this topic!

keda_re
Starting Member

5 Posts

Posted - 2005-07-29 : 14:58:03

only select query can insert multiple rows

Committed to you...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources