Modify query to use INSERT INTO

Question

I have developed a pretty complicated query, and now have hard time modifying it to use INSERT INTO statement.

The query (VS syntax):

           @"SELECT s.TagID, se.SessionID, " +
           " @todaysDate " +
           " AS ScanningTime " +
           " FROM (((Student s " +
           " LEFT JOIN [CourseID-ModuleID] cm ON s.CourseID = cm.CourseID) " +
           " LEFT JOIN [ModuleID-SessionID] ms ON cm.ModuleID = ms.ModuleID) " +
           " LEFT JOIN [Session] se ON ms.SessionID = se.SessionID) " +
           " WHERE s.TagID = @tagNo " +
           " AND se.SessionDate = cast(getdate() as date) " +
           " AND se.SessionTimeStart <= @Plus30Min " +
           " AND se.SessionTimeEnd >= @Plus30Min ";

The query runs perfectly, but I would like to place the result of the query into the empty table Attendance, which already exists in the DB.

Attendance table data types

TagID - int
SessionID - varchar
ScanningTime - datetime.

Please note that data types for Attendance table match with other tables data types.

I have tried placing "INSERT INTO Attendance (s.TagID, se.SessionID, ScanningTime)" just before the SELECT statement, but then my query does not produce any results.

What is a proper way to modify my query to include INSERT INTO statement? Thanks

EDIT:

All your answers and suggestions where very helpful. Thank you!

Try prepending with what you have without the qualifiers that make no sense in the Attendance table; INSERT INTO Attendance (TagID, SessionID, ScanningTime) — Joachim Isaksson
– Joachim Isaksson, Commented Aug 13, 2013 at 13:38

Claudio Redi · Accepted Answer · 2013-08-13 13:38:43Z

1

Remove (s.TagID, se.SessionID, ScanningTime) and just add INSERT INTO Attendance before your select

@"INSERT INTO Attendance
 SELECT s.TagID, se.SessionID, "
 etc...

answered Aug 13, 2013 at 13:38

Claudio Redi

68.6k15 gold badges138 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sonam · Accepted Answer · 2013-08-13 13:38:47Z

0

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

answered Aug 13, 2013 at 13:38

Sonam

3,4861 gold badge14 silver badges25 bronze badges

Comments

Jack · Accepted Answer · 2013-08-13 13:46:32Z

0

You can refer the below Code :

@"INSERT INTO Attendance(
   TagID , 
   SessionID,
   ScanningTime )
SELECT s.TagID, se.SessionID, " +
           " @todaysDate " +
           " AS ScanningTime " +
           " FROM (((Student s " +
           " LEFT JOIN [CourseID-ModuleID] cm ON s.CourseID = cm.CourseID) " +
           " LEFT JOIN [ModuleID-SessionID] ms ON cm.ModuleID = ms.ModuleID) " +
           " LEFT JOIN [Session] se ON ms.SessionID = se.SessionID) " +
           " WHERE s.TagID = @tagNo " +
           " AND se.SessionDate = cast(getdate() as date) " +
           " AND se.SessionTimeStart <= @Plus30Min " +
           " AND se.SessionTimeEnd >= @Plus30Min ";

answered Aug 13, 2013 at 13:46

Jack

2353 silver badges8 bronze badges

Collectives™ on Stack Overflow

Modify query to use INSERT INTO

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related