November 4, 2011 at 10:49 am
I have two tables an employee table and a training table. I know how to do a mass update but not really sure how to do a mass insert.
Table1
tblEmployee
EMPID
Table2
tblTraining
EMPID
CourseID
Status
Both Tables are linked by the EMPID. Basically I want to write either in SQL or VBA/SQL something like this: For every EMPID in tblEmployee, insert a row in Table 2 that contains the EMPID, a CourseID of 9750, and a Status of "NT".
I know how to update these rows once the row has been added like changing the Status to Q, but not how to add a completely new row.
Thanks!
November 4, 2011 at 10:55 am
Hi,
I've not tested this but try:
INSERT INTO tblTraining
SELECT EMPID, 9750, 'NT'
FROM tblEmployee
November 4, 2011 at 10:56 am
So would something like this do the trick?
Insert into tblTraining
(Select EMPID, 9750, 'NT' from tblEmployee)
November 4, 2011 at 12:04 pm
No those didnt work, just to clarify the 9750 and NT are actual values I am inserting into tblTraining for each of the employees in tblEmployee.
November 4, 2011 at 12:55 pm
Why did those suggestions not work? What values do you want in the second table? You have to give us a little information about what you want so we have a chance to help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 1:45 pm
I'm sorry, first time I've ever posted a help request.
Ok so I have 3 tables:
- a table of employee info (tblEmployee), like ID, firstname, lastname, department, etc.
- a table of training courses offered (tblCourseInfo) like courseID, coursename, coursehours, etc.
- and then a table that links those two together (tblTraining) that has ID, courseID, status of training like complete etc, and date trained.
What I am trying to do is do a mass insert to add a row of training for a class that everyone needs to take. So tblTraining would actually have multiple rows entered that looked like:
tblEmployee.ID at index 1, 9750 (which is the courseID num of the class), and the status NT for Needs Training So each row would look like:
EMP1, 9750, NT
EMP2, 9750, NT
EMP3, 9750, NT...etc.
The EMP1, EMP2, EMP3 would actually be various IDs of all the employees taken from the tblEmployee.
Hope that helps
November 4, 2011 at 1:53 pm
klc0000 (11/4/2011)
I'm sorry, first time I've ever posted a help request.Ok so I have 3 tables:
- a table of employee info (tblEmployee), like ID, firstname, lastname, department, etc.
- a table of training courses offered (tblCourseInfo) like courseID, coursename, coursehours, etc.
- and then a table that links those two together (tblTraining) that has ID, courseID, status of training like complete etc, and date trained.
What I am trying to do is do a mass insert to add a row of training for a class that everyone needs to take. So tblTraining would actually have multiple rows entered that looked like:
tblEmployee.ID at index 1, 9750 (which is the courseID num of the class), and the status NT for Needs Training So each row would look like:
EMP1, 9750, NT
EMP2, 9750, NT
EMP3, 9750, NT...etc.
The EMP1, EMP2, EMP3 would actually be various IDs of all the employees taken from the tblEmployee.
Hope that helps
No problem. Posting questions in such a way that others can help can be very difficult.
The sql given to you by the two previous posters would do exactly what you are asking. If you want to give it a spin just change the insert to a select and you will see.
select EMPID, 9750, 'NT' from Employee
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 2:35 pm
Thanks everyone for your help...I think I was putting paraenthesis in by accident. The final code to do the trick was:
INSERT INTO tblTraining(EMPID, CourseID, Status)
SELECT EUID, 9750, 'NT'
FROM tblEmployee;
Awesome first experience!!
November 4, 2011 at 2:37 pm
Cool. Glad you figured it out and thanks for letting us know and welcome!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply