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 |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2002-07-01 : 15:28:41
|
| What is wrong w/ this query?:INSERT INTO TestResults(StudentID,TestFormID,TestID,Passed,TestDate,FormatCodeID,ExaminerID) VALUES((1),(SELECT TestFormID FROM TestForm WHERE TestForm='A'),(SELECT TestID FROM Test WHERE Test='Test 1'),(),(5-5-02),(SELECT FormatCodeID FROM FormatCode WHERE FormatCode='E'),(SELECT ExaminerID FROM Examiner WHERE Name='Cordelia Martinez')) |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-01 : 15:31:02
|
| Your date value needs to be in single quotes... '5-5-02' |
 |
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2002-07-01 : 15:43:44
|
| it still bombs |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-07-01 : 15:53:44
|
| I don't think you can have the empty (), put NULL or '' inside like this (null)/('') depending on what you're trying to accomplish. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-01 : 15:56:40
|
| You don't need parentheses around literal values. If you are trying to insert a NULL value, you need to say so.And I don't think you can use subqueries to look up values if you are using INSERT/VALUES syntax... You can with INSERT/SELECT though(but make sure your subqueries return only a single row).Try something like: INSERT INTO TestResults( StudentID, TestFormID, TestID, Passed, TestDate, FormatCodeID, ExaminerID) SELECT 1, (SELECT TOP 1 TestFormID FROM TestForm WHERE TestForm='A'), (SELECT TOP 1 TestID FROM Test WHERE Test='Test 1'), NULL, 5-5-02, (SELECT TOP 1 FormatCodeID FROM FormatCode WHERE FormatCode='E'), (SELECT TOP 1 ExaminerID FROM Examiner WHERE Name='Cordelia Martinez') TOP 1 clauses are optional if you are positive there is never going to be more than one row returned from a subquery. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2002-07-01 : 16:09:23
|
| thanx, that worked good. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-01 : 18:56:39
|
quote:
And I don't think you can use subqueries to look up values if you are using INSERT/VALUES syntax... You can with INSERT/SELECT though
Oops! Good catch Ilya! |
|
|
|
|
|
|
|
|
subqueries