SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WHY ? insert error column name or number.....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

volyn
Starting Member

30 Posts

Posted - 01/02/2011 :  06:05:57  Show Profile  Reply with Quote
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
22403 Posts

Posted - 01/02/2011 :  06:20:07  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/02/2011 :  06:24:47  Show Profile  Reply with Quote
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
Go to Top of Page

volyn
Starting Member

30 Posts

Posted - 01/04/2011 :  18:25:21  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000