CODEWITHSUNDEEP
c#sqlsql-serverdatabasesql-insert
jaspernorth
jaspernorth

Reputation: 415

Modify query to use INSERT INTO

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!

Upvotes: 0

Views: 94

Answers (3)

Jack
Jack

Reputation: 235

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 ";

Upvotes: 0

Sonam
Sonam

Reputation: 3466

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

Upvotes: 0

Claudio Redi
Claudio Redi

Reputation: 68400

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

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

Upvotes: 1

Related Questions