INSERT ... SELECT problem
-
Good afternoon!
How do I make this work?
I want to append rows in table dbo.Diagnosis_Billing_Codes. This table has an autonumber primary key and a timestamp column.
I build a temp table and then try to insert from the temp table.
If I leave the PK and timestamp columns out of the insert list I get an error:
Insert Error: Column name or number of supplied values does not match table definition.
I thought you shouldn't include autonumber and timestamp fields in the insert list or if you do use DEFAULT for the value. Niether seems to work in this case????????
--Build a temp table with records to be inserted
SELECT
REPLACE(Billing_Code,'.','') AS Billing_Code,
Diagnosis_Code_ID,
'29' AS Profile_ID
INTO
#temp
FROM
dbo.Diagnosis_Billing_Codes
WHERE
Profile_ID = 1
--Insert records from temp table
INSERT
dbo.Diagnosis_Billing_Codes
SELECT
Billing_Code,
Diagnosis_Code_ID,
Profile_ID
FROM
#temp
--Drop temp table
DROP TABLE #temp
Thanks!
jmatt
-
See BOL under INSERT. If you aren't inserting ALL columns, you need to specify a column list:
INSERT
dbo.Diagnosis_Billing_Codes
(BillingCode, Diagnosis_Code_ID, Profile_ID)
SELECT
Billing_Code,
Diagnosis_Code_ID,
Profile_ID
FROM
#temp
-
Thank you very much. Sometimes I miss the obvious!
jmatt
-
And not only that, even if you are selecting all, you should specify a field list always to account for the order of the columns yourself and not leave anything to fate
It is considered a best practice
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply