CODEWITHSUNDEEP
sqlsql-servert-sqlsql-insert
Brisingr
Brisingr

Reputation: 82

Weird Insert error

I got this error in production while executing the query I have given below

Column name or number of supplied values does not match table definition.

Query: 

BEGIN TRANSACTION
BEGIN TRY

 IF  EXISTS (SELECT * FROM Support.sys.objects WHERE object_id = OBJECT_ID(N'[Support]. [dbo].[ReportData]') AND type in (N'U'))
BEGIN
DROP TABLE SUPPORT.[dbo].ReportData
END

 SELECT *,CAST(NULL AS DATETIME) as LoadDate,CAST(NULL AS VARCHAR(100)) AS Action
 INTO Support.[dbo].[ReportData]
 FROM dbo.ReportMain
 WHERE 1 = 0;

UPDATE mso
SET  mso.ReportStatus = '00' 
OUTPUT DELETED.*,
             @LoadDate,
             'UPDATE'
        INTO Support.dbo.ReportData
FROM dbo.ReportMain mso
INNER JOIN dbo.ReportHdr HDR
ON mso.ReportId=HDR.ReportId
WHERE mso.ReportStatus IN ('02','29')

COMMIT;

END TRY
BEGIN CATCH

ROLLBACK;

PRINT 'FAILED !!!';

END CATCH

The table dbo.ReportMain had recently been dropped and re created in Production with some columns added and its order changed and this query was executing correctly before this modification.

Can anybody explain why this error occurred

Upvotes: 1

Views: 70

Answers (1)

Leo
Leo

Reputation: 14860

Because this script doesn't insert anything into the ReportMain table I'd be extremely suspicious of the use of the * wildcard selector, it's a bit reckless to do that when using select...into or output. As one of the comments state, because the columns of ReportMain table are not guarantee to have the same order now, use explicit column names to avoid the error, or even worse...inserting data into the wrong columns

Upvotes: 2

Related Questions