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

Question

Post reply

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

DBA_Learner

SSCarpal Tunnel

Points: 4228
More actions
December 17, 2013 at 11:14 am

#301473

Can anyone help me why I get the error, while executing below query after creating the table. Why I can't able to insert default timestamp value in the table while doing insert statement. Please provide your info. Thanks.
create table X
(
DBName varchar(20),
NumberOfConnectionstoDB INT,
Loginame VARCHAR(50),
HostName VARCHAR(50),
[program_name] varchar (200),
cmd varchar(200),
Logintime datetime default CURRENT_TIMESTAMP
)
insert into X
SELECT DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnectionstoDB,
loginame as LoginName,
hostname,[program_name],cmd
FROM
sys.sysprocesses
WHERE
DB_NAME(dbid) in ('master','msdb')
GROUP BY
dbid, loginame,hostname,[Program_name],cmd

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

seven columns in the table, only six in the query;

your table has the column name Logintime , but your query does not.

the implied column list, for when you say INSERT INTO X always gets all the columns for the target table.

you either need to name the columns to insert into explicitly, or have the query return getdate() AS Logintime

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 2

Lowell,

Thanks, for some reason, i just got diverted. Re queried BY ADDING getdate() into my select query and was able to get my result. THANKS

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 3

I have always made it a practice that when I'm inserting into or selecting out of a table I always specify the columns. In your case when you are performing your insert, you are not specifying the columns you are populating.

The syntax I use is:

insert into <some table> (col1, col2, col3...coln)

select col1, col2, col3...coln from <some other table>

where <some where clause>

There are many reasons for doing this. If your tables change by adding additional columns and you are not specifying the column list then your code will break.

Just a word to the wise.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

Kurt W. Zimmerman (12/17/2013)
I have always made it a practice that when I'm inserting into or selecting out of a table I always specify the columns. In your case when you are performing your insert, you are not specifying the columns you are populating.
The syntax I use is:
insert into <some table> (col1, col2, col3...coln)
select col1, col2, col3...coln from <some other table>
where <some where clause>
There are many reasons for doing this. If your tables change by adding additional columns and you are not specifying the column list then your code will break.
Just a word to the wise.
Kurt

+1000!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)