| Author |
 Topic  |
|
|
volyn
Starting Member
30 Posts |
 Posted - 01/02/2011 : 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
United Kingdom
22191 Posts |
Posted - 01/02/2011 : 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
United Kingdom
22191 Posts |
Posted - 01/02/2011 : 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 - 01/04/2011 : 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 |
|
|
| |
Topic |
|
|
|
WHY ? insert error column name or number.....
