what is wrong in this ?

Question

what is wrong in this ?

Best-330874

SSCrazy

Points: 2350
More actions
September 28, 2007 at 9:28 am

#121719

i get the following error when i run this
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
insert into Table_1
SELECT b.last_name,
b.first_name,
b.City
FROM Table_2 b
WHERE NOT EXISTS(SELECT 1 FROM Table_1 a
where a.lastname = b.last_name
and a.firstname = b.first_name
and a.City = b.City)
------------------------------------------------------------------------------
CREATE TABLE [dbo].[Table_1](
[b]lastname[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]firstname[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]city[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]phone[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
----------------------------------------------------------------------------
CREATE TABLE [dbo].[Table_2](
[b]first_name[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]last_name[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]city[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[b]state[/b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
----------------------------------------------------------------------------

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

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

Andrew in WV SSCommitted Points: 1655 More actions · Answer 1

Table_1 is defined with 4 columns, but your select only includes 3. You need to either explicitly state which columns you are inserting into or make sure the number of columns in the select statement matches the number of columns in the table you are inserting into.

Andrew

--Andrew

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

As Andrew mentioned, you need one of these:

insert into Table_1

SELECT b.last_name,

b.first_name,

b.City

, null

OR

insert into Table_1 (last_name, first_name, city)

SELECT b.last_name,

b.first_name,

b.City

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Best practice : ALWAYS mention the columns in the insert statment. You'll save yourself a lot of troubles.

Andrew in WV SSCommitted Points: 1655 More actions · Answer 4

Ninja's_RGR'us (9/28/2007)
Best practice : ALWAYS mention the columns in the insert statment. You'll save yourself a lot of troubles.

Agreed. 🙂

--Andrew