Insert job failed - getting message id 213 severity 16

Question

Post reply

Insert job failed - getting message id 213 severity 16

vince.tran

Old Hand

Points: 347
More actions
September 28, 2009 at 6:00 pm

#135953

Hello,
For some odd reason, my insert job has been failing for the past 3 days and I'm not sure on what needs to be done on resolving it. I have a job that does an insert of one database (dataminer) to another (dataminer_366). Here's the code:
USE [Dataminer_366]
GO
TRUNCATE TABLE [Dataminer_366].[dbo].[patient_transactions]
GO
USE DATAMINER
GO
INSERT INTO [Dataminer_366].[dbo].[patient_transactions]
SELECT *
-- SELECT COUNT(*)
FROM [DATAMINER].[DBO].[patient_transactions]
WHERE date_posted > (GETDATE()-366)
GO
Both databases are healthy and I do not see anything unusual in the logs. I'm also attaching the full error message:
Date9/28/2009 1:50:26 AM
LogJob History (Copy and Refresh DataMiner on MYSQLSERVER)
Step ID5
ServerMYSQLSERVER
Job NameCopy and Refresh DataMiner on MYSQLSERVER
Step NameINSERT INTO Dataminer_366
Duration00:00:00
Sql Severity16
Sql Message ID213
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: BND\SQLSERVICES. Insert Error: Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213). The step failed.
Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Richard M. SSCertifiable Points: 7401 More actions · Answer 1

Looking at the table structure of both tables should give you the clue.

Someone must have added/removed a column.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Steve Jones - SSC Editor SSC Guru Points: 736592 More actions · Answer 2

select * is resolved into the actual column names at compile time. If you have changed columns, then it might start to fail. This is why you never use SELECT * in production code. If you take the 4 minutes to put the column names in there (insert and select statements), this wouldn't happen. Even if columns changed.

you might be missing data from new columns, but then you know what is missing.

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 3

If you take the 4 minutes to put the column names in there (insert and select statements), this wouldn't happen. Even if columns changed.
you might be missing data from new columns, but then you know what is missing.

The following helps with the 4 minutes 😉

Run the following against your target database, and change the WHERE clause for the table you're wanting to INSERT INTO. Then, copy the resulting column list into the code window where you're writing your INSERT statement, stored procedure, etc.

MJM

SELECT

' ' + COLUMN_NAME + ','

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE

c.TABLE_NAME = 'UserTableNameHere'

ORDER BY

c.ORDINAL_POSITION

Steve Jones - SSC Editor SSC Guru Points: 736592 More actions · Answer 4

Or right click the table in SSMS, script a select or insert to the query window. Then copy the fields to the insert and the select.

vince.tran Old Hand Points: 347 More actions · Answer 5

Thanks guys. Mark, when I ran the query that you had posted, I noticed in the result that the date_posted column does appear on both databases. However, there are 10 more columns from the original then to the source. I'm not sure if that's always been the case but I suspect not. The question, is what is the best way to handle a situation like this? Do I manually insert the missing columns or are there other means? By the way that I pose this question, ya'll should know that I'm a newbie so be gentle with me 😉

Steve Jones - SSC Editor SSC Guru Points: 736592 More actions · Answer 6

It's hard to say what you should do. Is the data needed there?

You have 2 choices.

1. Put the columns that are in the source in the list and then use defaults of some sort for other columns.

2. Add the columns to the table.

Can't tell you which is better. It depends on what the data is, what impact a table change has on other apps, if you have defaults, etc.

vince.tran Old Hand Points: 347 More actions · Answer 7

Thanks Steve. The data, to my knowledge, is a repository archive that is still needed for historical lookups.

Both options sound good. What is the most efficient method and would anybody know how to code this?

Thanks,

Steve Jones - SSC Editor SSC Guru Points: 736592 More actions · Answer 8

Can you post the DDL for both tables? Basically you'd

insert Destination (col1, col2, col3, col4)

select col1, col2, default_for_3, default_for_4

from SourceTable

vince.tran Old Hand Points: 347 More actions · Answer 9

Pardon my ignorance, but what is a DDL and how would I go about in retrieving it? Wasn't kidding about the newbie part..