WHY ? insert error column name or number.....

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

General SQL Server Forums

New to SQL Server Programming

WHY ? insert error column name or number.....

Author

Topic

volyn
Starting Member

30 Posts

Posted - 2011-01-02 : 06:05:57

Hello, I dont understand why I get error in last row

insert error column name or number of supplied values does not match table definition

DECLARE @MMSTA TABLE
(matnr varchar(20) not null,
Datum datetime not null,
mmsta_new varchar(2),
mmsta_old varchar(2),
poradi int not null
)

INSERT @MMSTA
select matnr,dbo.dd_mm_yyyy_to_datetime_udf(Datum),mmsta_new,mmsta_old,
RANK() OVER (PARTITION BY matnr ORDER BY dbo.dd_mm_yyyy_to_datetime_udf(Datum),Cas)
from MMSTA

DROP TABLE MMSTA

CREATE TABLE MMSTA
(matnr varchar(20) not null,
Datum datetime not null,
mmsta_new varchar(2),
mmsta_old varchar(2),
poradi int not null,
CONSTRAINT MMSTA_pk PRIMARY KEY (matnr,datum,poradi)
)

/* Variable @MMSTA and table MMSTA have same structure here */

INSERT INTO MMSTA /*this cause error*/
select matnr,Datum,mmsta_new,mmsta_old,poradi from @MMSTA

Thanks for help

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 06:20:07

It looks fine to me. One issue MIGHT be if the table currently exists with DIFFERENT columns - the syntax parser MAY use the existing table to decide on the correct columns for your final INSERT statement, rather than the DROP / (new) CREATE TABLE in your code.

If you change the code to:


INSERT INTO MMSTA(matnr,Datum,mmsta_new,mmsta_old,poradi)
select matnr,Datum,mmsta_new,mmsta_old,poradi from @MMSTA

does that help?

If not I think you will have to CREATE TABLE MMSTA2, make the INSERT to that table, and THEN rename the table back to MMSTA

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 06:24:47

P.S. If the table is small (i.e. space in the database is NOT an issue) then just insert into MMSTA2 - WITHOUT using a temporary table - and then DROP the old MMSTA table and rename MMSTA2 to MMSTA - then you don't have to do the DROP until you know the insert has worked - whereas, with your code, you run the risk that the insert to @TempTable works, and also the DROP ... but then something goes wrong with the CREATE or INSERT and you wind up with no data - and have to restore.

Also, if you are using @TempTable because MMSTA is large, and thus you need to prevent duplicating the storage requirement for the table, then I think you should use #TempTable instead of @TempTable

volyn
Starting Member

30 Posts

Posted - 2011-01-04 : 18:25:21

REd solution is working. I also tried to solve that with temp tables which are comfortable too. Anyway I thank you for you kind help.

Best regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources