December 5, 2003 at 1:16 am
I created a tabel like this:
Create table tableA(
order identity(int,1,1),
a varchar(20),
b varchar(20))
I have a tableB with 2 columns a and b.
When I run this query below:
Insert into tableA
select * from table B
I get the error below:
==========================================
Server: Msg 213, Level 16, State 4, Procedure extdata, Line 222
Insert Error: Column name or number of supplied values does not match table definition.
===========================================
Microsoft web site says this is a bug in SQL Server 6.5 and 7.0 but I am runnibg SQL Server 2000.
============================================
Does any one know a work around to this bug.
Thanks.
December 5, 2003 at 1:24 am
Don't use select *. Select the actual column names from table b.
Jeremy
December 5, 2003 at 2:35 am
And I would also suggest specifying which columns you are inserting, like this:
INSERT INTO tableA (a, b)
SELECT a, b FROM tableB
It is always a good thing to be explicit when writing code.
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Co-author of Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)
Articles: http://www.sqlservercentral.com/columnists/chedgate/
December 5, 2003 at 5:35 am
What do you do if you have 50 columns in a table?. There must be a better way of doing this.
by the way this query is contained in a stored proc.
December 5, 2003 at 6:01 am
I don't think u can do this other than giving the names in INSERT INTO command
My Blog:
December 5, 2003 at 7:09 am
quote:
What do you do if you have 50 columns in a table?. There must be a better way of doing this.
if you are using QA to create the query all you have to do is to DRAG the COLUMNS Folder from under the table on the Object Browser and it will create a comma separated list of ALL columns for you. It doesn't get any better than that!!
HTH
* Noel
December 6, 2003 at 1:30 pm
Thanks for the tip about dragging the columns, it's a nice one.
I have however resolved the bug problem by using exec('insert into tableA select * from tableB'). It worked just as expected.
Many thanks to you all for your help.
December 8, 2003 at 7:20 pm
While your resolution may work I would still suggest putting your field names explicitly in the query as was suggested by Chris and Jeremy. You are taking sloppily written bug prone code and finding a slower way to execute it instead of writing it correctly in the first place. One of the main NO-NO's in a stored procedure is the use of SELECT *. Using SELECT * in QA on an adhoc query is fine. Doing it in a stored procedure is just asking for a bug to occur down the road.
If you are too lazy to type out the select statement try looking for the sp_select stored procedure in the download section of this site. You can even modify it and make one to do insert, update, delete, and declare statements. They are very handy utilities to have and can be modified to do the statements with your style of formatting.
Another option is to simply right click on the table int QA and select "script to new window as " and let it script out the field names for you. Then copy what you want into your SP code.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply