January 14, 2005 at 8:57 am
Can anyone tell me why example #1 fails, but example 2 and 3 will run?
--Example 1: DOES NOT WORK
DECLARE @tmp1 TABLE (RID INT IDENTITY (1,1) NOT NULL, EmpID CHAR(9), PubID CHAR(4), Amt1 DECIMAL(9,2), Amt2 DECIMAL(9,2), f1 CHAR(2), Pct1 DECIMAL(9,2), Pct2 DECIMAL(9,2))
INSERT @tmp1 SELECT e.emp_id, t.pub_id, 0, 0, 'A1', 0, 0
FROM employee e
INNER JOIN titles t ON e.pub_id = t.pub_id
GROUP BY e.emp_id, t.pub_id
SELECT * FROM @tmp1 ORDER BY EmpID
--Example 2: WORKS BUT LEAVES Pct2 COLUMN NULL
DECLARE @tmp1 TABLE (RID INT IDENTITY (1,1) NOT NULL, EmpID CHAR(9), PubID CHAR(4), Amt1 DECIMAL(9,2), Amt2 DECIMAL(9,2), f1 CHAR(2), Pct1 DECIMAL(9,2), Pct2 DECIMAL(9,2))
INSERT @tmp1 SELECT e.emp_id, t.pub_id, 0, 0, 'A1', 0
FROM employee e
INNER JOIN titles t ON e.pub_id = t.pub_id
GROUP BY e.emp_id, t.pub_id
SELECT * FROM @tmp1 ORDER BY EmpID
--Example 3: WORKS CORRECTLY
DECLARE @tmp1 TABLE (RID INT IDENTITY (1,1) NOT NULL, EmpID CHAR(9), PubID CHAR(4), Amt1 DECIMAL(9,2), Amt2 DECIMAL(9,2), f1 CHAR(2), Pct1 DECIMAL(9,2), Pct2 DECIMAL(9,2))
INSERT @tmp1 (EmpID, PubID, Amt1, Amt2, f1, Pct1, Pct2) SELECT e.emp_id, t.pub_id, 0, 0, 'A1', 0, 0
FROM employee e
INNER JOIN titles t ON e.pub_id = t.pub_id
GROUP BY e.emp_id, t.pub_id
SELECT * FROM @tmp1 ORDER BY EmpID
January 14, 2005 at 9:51 am
Example 1:
When you use a SELECT statement in an INSERT the two tables must have compatible structures. If all of the columns are compatible in the same order that they appear in the CREATE TABLE statements you do not need to specify column names in either table. If the tables are not compatible and in the same order you need to use either the insert or the select clause to re-order them. Because there is an identity column in your temp table there is one more column than in the select statement.
Example 2:
You only have seven columns in the SELECT statement. The column pct2 would be NULL because there is nothing entered in that one.
Example 3:
You have explicitly listed the columns in both INSERT and SELECT. SQL knows where to put the information.
Quand on parle du loup, on en voit la queue
January 14, 2005 at 10:00 am
At a guess I think it has something to do with the identity column.
Check what the 'set identity_insert' is
January 14, 2005 at 10:44 am
Look again, I have 7 columns in example 1, which should insert correctly since the temp table has 8 columns with the identity. For Example 2, I simply dropped the last value from the insert which leaves 6 values to be inserted into the 8 columns leaving the last column NULL.
Since neither example 1 or example 2 are trying to insert a value into the identity column, they should both work correctly and not just example 2.
(These will run against the standard pubs database if you would like to try them yourself)
January 14, 2005 at 11:59 am
Example 1 AND 2
ARE WRONG. You should specify the field list ALWAYS!
It has been documented and you should follow the practice. the Fact that the second case worked was an unfotunate case of good luck!
HTH
* Noel
January 14, 2005 at 12:15 pm
Wayne,
noeld is right, you should always specify a field list in the INSERT clause.
However, having said that, I ran your first query in Query Analyzer, substituting literal strings for your e.emp_id and t.pub_id, and commenting out the FROM and GROUP BY clauses. It ran just fine. I returned data from the @tmp1 table.
What error message or messages are you getting?
January 14, 2005 at 12:48 pm
Some relevant points:
1. I always specify columns in my production code. I wrote this code to further examine what appears to be a bug.
2. If you substitute literal strings the 1st example will work.
3. If you remove the group by clause the 1st example will also work
When you run the 1st example, you should get Error Msg: 8101 "An explicit value for the identity column in table '@tmp1' can only be specified when a column list is used and IDENTITY_INSERT is ON."
This message is incorrect since the insert is not attempting to put a value into the identity column. If it was, then example 2 would have the same error message.
January 14, 2005 at 2:19 pm
Wayne,
For example one you have to explicitly state your insert fields since you are not inserting into the identity column. Simply adding the insert fields makes it work.
DECLARE @tmp1 TABLE (RID INT IDENTITY (1,1) NOT NULL
, EmpID CHAR(9)
, PubID CHAR(4)
, Amt1 DECIMAL(9,2)
, Amt2 DECIMAL(9,2)
, f1 CHAR(2)
, Pct1 DECIMAL(9,2)
, Pct2 DECIMAL(9,2))
INSERT @tmp1 (EmpID, PubID, Amt1, Amt2, f1, Pct1, Pct2)
SELECT e.emp_id, t.pub_id, 0, 0, 'A1', 0, 0
FROM employee e
INNER JOIN titles t ON e.pub_id = t.pub_id
GROUP BY e.emp_id, t.pub_id
SELECT * FROM @tmp1 ORDER BY EmpID
Ryan
January 14, 2005 at 2:38 pm
Ryan,
That is exaclty what example 3 does.
While I agree with everyone that you should specify the columns when doing an insert, that is NOT a requirement of the insert statement whether there is an identity column or not (Read your BOL). As example #2 shows, the insert works correctly without specifying columns.
January 14, 2005 at 2:49 pm
Wayne, maybe my Friday afternoon reading is getting a bit lazy - if so please forgive...
Anyway, BOL states: "If the values in the VALUES list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value."
Example 1 does not have values in the same order (the IDENTITY is ommitted) nor does it have all the values listed (again, the IDENTITY is ommitted). This is why I suggested the need for the value list (your example number 2)
Do you disagree that Example 1 should have a field list?
Ryan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply