SQL Server - inserting multiple records at once-sql server 2008

Asked By michael brown on 03-May-13 04:50 AM

How can i insert multiple records at once in sql server 2008 express. I have a table called enrolnment with three fields namely pupilcode,year,grade.Pupilcode field is bound using asp.net checkedlistbox conrol, year is bound using asp.net dropdownlist control and grade is bound using asp.net dropdownlist control. Note data is already prepopulated in the table.

PupilCode          Year   Grade
2010105350020004   2008    2
2010105350020005
2010105350020006
2010105350020007
2010105350020008
2010105350020009

How can I insert same year,same grade to the other records at a click of a button(multiple entry) if I have checked all in the checkedlistbox so that it becomes like below;

PupilCode             Year   Grade
2010105350020004      2008   2
2010105350020005      2008    2
2010105350020006      2008   2
2010105350020007      2008    2
2010105350020008      2008    2
2010105350020009      2008    2

When i use insert into enrolnment (pupilcode,year,grade)values(pupilcode,year,grade)it only insert the first record. how do i achieve this. I want this to help in batch process rather than users be updating a record by record.

john sat replied to michael brown on 03-May-13 06:51 AM

Hi,

Insert into Enrolnment (Pupilcode,Year,Grade )
select 2008,2,Pupilcode from TableWhichalreadyPopulatedValues

Hope this helps.

michael brown replied to john sat on 03-May-13 07:22 AM

Thanks
the prepopulated table is enrolnment itselt. what if i want to insert year 2009, grade 2 for the pupil codes above. In other word i should say i want to add a pupil history.
in 2008 the pupil was in grade 2
in 2009 the pupil was in grade 3
in 2010 the pupil was in grade 4 and so forth

john sat replied to michael brown on 03-May-13 07:59 AM

Hi,
I am not exactly understand what you are trying to say,
Is the pupil code in the table are repeated for each year ie:
If the table having 2 pupil code like
2010105350020004
2010105350020005

So, would you like to update the table as follows
2010105350020004 2008 2
2010105350020005 2008 2
2010105350020004 2009 3
2010105350020005 2009 3
2010105350020004 2010 4
2010105350020005 2010 4
??

michael brown replied to john sat on 03-May-13 12:12 PM

Thanks John,
You are very right john. The pupilcode can be repeated. the primary key is the year. your example is correct but i want this to be inserted at once

john sat replied to michael brown on 06-May-13 02:51 AM

Hi,

Sorry I was not available on the weekends.
From my understanding I have created the followings for the puplecode entry.

Create table #temp (pupilcode varchar(20), year int, grade int)

insert into #temp (pupilcode) values ('2010105350020001')
insert into #temp (pupilcode) values ('2010105350020002')
insert into #temp (pupilcode) values ('2010105350020003')
insert into #temp (pupilcode) values ('2010105350020004')
insert into #temp (pupilcode) values ('2010105350020005')

So, the table now contain 5 puplecodes, with year and grade as blank or null values.
I am not clear how you can populate the year and grade on the first entered puplecode records.

If this is the case for me, i can write the following update statement for the first inserted pupilcode records.

if exists(select 1 from #temp where year is null or grade is null)
update #temp set year=year(getdate()),grade=@grade where year is null or grade is null
else
insert into #temp (pupilcode,year,grade)
select pupilcode,year(getdate()),@grade from #temp

Actaully, this is the Insert query you required i believe.

Thanks,

michael brown replied to john sat on 06-May-13 03:43 AM

Thanks John,

I hope this might work, i will come back to you

michael brown replied to john sat on 07-May-13 11:49 AM

Thanks john

The idea you provided is almost working after tweaking it;here is the code am using for entering pupil code

Dim

i As Integer = 0

While i < ListBox1.Items.Count

SetSelection(

True)

commandString =

"INSERT INTO enrolnment (pupilCode) VALUES('"+listBox1.items.Tostring() +"' );"

con.ConnectionString =

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\simple.mdf;Integrated Security=True;User Instance=True"

con.Open()

cmd.Connection = con

cmd.CommandText = commandString

DisplayMessage(

"Records Added Successfully")

cmd.ExecuteNonQuery()

DisplayMessage(

"Records NOT Added")

con.Close()

System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
end while

This is able to add the exact records from the listbox into db but with a wrong format. am getting these system.web.ui.webcontrols.listbox in the database instead of the pupilcode. How do i get the pupilcode into db.using selecteditems am getting error, selecteditems is not a member of system.web.ui.webcontrol.listbox

john sat replied to michael brown on 09-May-13 01:55 AM

Hi,

Hope we need to give the index in the listBox1.items.Tostring().
Since its come under the while loop, give the index as listBox1.items[i].Tostring().

Thanks,

michael brown replied to john sat on 09-May-13 03:45 AM

John, Thanks for the information. It worked great. That is the core function of my application. Just replaced the brackets on index to () as [] didnt work.

Listbox1.items(i).Tostring()
Great