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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-16 : 09:36:05
|
| Ron writes "I have a stored procedure inserting data into a main table and multiple records into child tables. SQL 7 Win2k on development machine. I've read your dynamic SQL articles but still struggling with this.All works fine inserting into the main and child tables except for one child table.This is for a college intranet and basically users add records and can select multiple areas, multiple categories and multiple courses. But I can't get the courses insert to work. The course data is held in a table where two fields Aos + Session form the unique course ID. Because these are char fields (Can't be changed) we normally trim any extra spaces and insert a backslash between the two values. e.g. Result=TH1234/PA In this example Aos=TH1234 Session=PAI am trying to pass multiple values e.g. TH1234/PA, TH5678/PA, TH4321/PB via a form and insert wherever Aos + / + Session combined is in this set of vars. The vars are being sent correctly but the insert isn't happening at all. I suspect this is something to do with the syntax of joining the twofields in the SP but can't seem to resolve it. I tried using a view to resolve it by calling the joined values CourseID but without success. I'm not sure if a view can be used instead of a table in this instance.The rest of the SP works fine including inserting multiple rows into other child tables but these are all single ID fields.I've including two attempts at resolving the problem below - can you help with what's wrong or suggest a solution?ThanksRon---------------------------------------------------------attempt 1 referring to a viewDeclare @NewsCoursesInsert varchar(2000)SET @NewsCoursesInsert = 'INSERT INTO NewsCourses ( NewsID, CourseID)SELECT ' + CONVERT(varchar, @NewsID) + ', CourseID From viewCourses WhereCourseID IN (' + @CourseID + ')'Exec(@NewsCoursesInsert)----------------------------------------------------------attempt 2 joining the fields and using trim - probably the syntax is wrongDeclare @NewsCoursesInsert varchar(2000)SET @NewsCoursesInsert = 'INSERT INTO NewsCourses ( NewsID, CourseID)SELECT ' + CONVERT(varchar, @NewsID) + ',('+RTRIM(Aos)+'/'+RTRIM(Session)+') From AoSPlanning Where('+RTRIM(Aos)+'/'+RTRIM(Session)+') IN (' + @CourseID + ')'Exec(@NewsCoursesInsert)" |
|
|
|
|
|
inserting multiple records into child tables