May 15, 2009 at 4:54 am
Hi,
I am trying to get an INSERT INTO statement to work incorporating a join.
insert into laptop.dbo.addnotes
select * from addnotes
join customer on customer.cus_id=addnotes.id
where customer.status = 'Active'
The reponse I get is:
Insert Error: Column name or number of supplied values does not match table definition.
Am I trying the impossible, or am I doing something wrong.
Any help greatly appreciated.
Matt
May 15, 2009 at 4:57 am
with the * notation you get columns from addnotes AND customer
change your query to this:
insert into laptop.dbo.addnotes
select addnotes.* from addnotes
join customer on customer.cus_id=addnotes.id
where customer.status = 'Active'
May 15, 2009 at 6:48 am
Thanks alot, worked fine and I learned something new !
May 15, 2009 at 6:52 am
no probs
really you shouldn't use the * notation, it will lead to frustration and chasing weird errors when someone makes a modification to the schema.
if you can't be bothered typing out column names try this script:
declare @table_name as varchar(1000)
set @table_name = 'addnotes'
select '['+column_name + '],'
from information_schema.columns
where table_name = @table_name
and table_schema = 'dbo'
order by ordinal_position
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply