asked on

How do I figure out column or supplied values not matching error

I copied a table (that has no triggers, keys, etc.) two different ways, enterprise manager and script. After each copy, I created the following insert script. For some reason I keep getting the error: "Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.". the oas_bases1temp is a clean copy of the oas_bases1. Can someone help me identify what I'm missing? Thanks.

Mahonek

<SCRIPT>
INSERT oas_bases1
SELECT      cmpcode,
      el1,
      el2,
      full_value,
      ActAmt_Base,
      BudAmt_Base,
      LYAmt_Base,
      ActYtd_Base,
      BudYTD_Base,
      LYYTD_Base
      balcode,
      yr,
      period,
      repbasis,
      basegrp
FROM       oas_bases1temp

jamesgu

how did you create the temp table ?

can you list the results for these two statements?

sp_help oas_bases1temp

sp_help oas_bases1

you may try this statement to create the temp table to make sure the two tables have the same schema.

select * into oas_bases1temp from oas_bases1 where 1 = 2

Mahonek

ASKER

I did the copy but I did not use the where clause. What does that do?

Here is the result of
sp_help oas_bases1

Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
oas_bases1 dbo user table 2008-10-14 15:08:46.503

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- -----------------------------------
cmpcode varchar no 12 no no no
el1 varchar no 72 no no no
el2 varchar no 72 no no no
full_value money no 8 19 4 no (n/a) (n/a)
ActAmt_Base money no 8 19 4 no (n/a) (n/a)
BudAmt_Base money no 8 19 4 no (n/a) (n/a)
LYAmt_Base money no 8 19 4 no (n/a) (n/a)
ActYtd_Base money no 8 19 4 no (n/a) (n/a)
BudYTD_Base money no 8 19 4 no (n/a) (n/a)
LYYTD_Base money no 8 19 4 no (n/a) (n/a)
balcode varchar no 12 no no no
yr smallint no 2 5 0 no (n/a) (n/a)
period smallint no 2 5 0 no (n/a) (n/a)
repbasis smallint no 2 5 0 no (n/a) (n/a)
basegrp varchar no 255 no no no

Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.

Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY

The object does not have any indexes.

No constraints have been defined for this object.

No foreign keys reference this table.

Here is the result of
sp_help oas_bases1temp

Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
oas_bases1temp dbo user table 2008-10-22 17:03:09.700

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- -----------------------------------
cmpcode varchar no 12 no no no
el1 varchar no 72 no no no
el2 varchar no 72 no no no
full_value money no 8 19 4 no (n/a) (n/a)
ActAmt_Base money no 8 19 4 no (n/a) (n/a)
BudAmt_Base money no 8 19 4 no (n/a) (n/a)
LYAmt_Base money no 8 19 4 no (n/a) (n/a)
ActYtd_Base money no 8 19 4 no (n/a) (n/a)
BudYTD_Base money no 8 19 4 no (n/a) (n/a)
LYYTD_Base money no 8 19 4 no (n/a) (n/a)
balcode varchar no 12 no no no
yr smallint no 2 5 0 no (n/a) (n/a)
period smallint no 2 5 0 no (n/a) (n/a)
repbasis smallint no 2 5 0 no (n/a) (n/a)
basegrp varchar no 255 no no no

Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.

Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY

The object does not have any indexes.

No constraints have been defined for this object.

No foreign keys reference this table.

ASKER CERTIFIED SOLUTION

jfmador

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jamesgu

by using the where clause (where 1 = 2), you will get an empty table with the same table schema