Insert Error: Column name or number of supplied..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Error: Column name or number of supplied..

Author

Topic

estanuk
Starting Member

3 Posts

Posted - 2008-10-30 : 12:39:52

hi all,

having trouble inserting the records returned from a select join query into a table on another DB.

Basically i have created a new DB with 8 table structures from an existing database, i am using a join query to return results that are relevant to records from another table. If i run the query it returns the correct results fine.

So im trying to put these results into my new table on my other database, however its returning the error:

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

if i specify a couple column names it works fine, but trying to put the whole record in without specfying all the column names 1 by 1 returns that error. here is my sql code:

INSERT INTO TP select * from MR2.dbo.TP left JOIN MR2.dbo.TX on MR2.dbo.TP.audcon=MR2.dbo.TX.conno where MR2.dbo.TX.condateinput < '01/01/2003'

any ideas?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 12:45:25

Yes, don't be lazy and qualify the columns.

This will improve readability and protect you from schema changes to the table.

Regards,

do


INSERT INTO x (
   cola
   , colb
   , colc
   )
SELECT
   a
   , b
   , c
FROM
   ....

instead of using *

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:23:03

and cause for the error is that you've more columns returned from join than that in target table as it includes columns from both tables . thats why its always best to explicitly use column names rather than * which is exactly what Charlie suggested.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 03:28:35

Also always speicy column names with proper alias table name during JOINs

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-31 : 06:40:16

Hey! Nothing wrong with beeing lazy!!

This code will get a comma separated list of all natching column names between two tables in the same database ->

DECLARE 
  @columns varchar(max),
  @table1 varchar(200),
  @table2 varchar(200)

SET @columns = ''
SET @table1 = 'tablename 1'
SET @table2 = 'tablename 2'

SELECT @columns = @columns + a.column_name + ', '
FROM information_schema.columns a
  INNER JOIN information_schema.columns b
    ON a.column_name = b.column_name
WHERE a.Table_name = @table1
  AND b.Table_name = @table2

SELECT @Columns

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources