| Author |
Topic |
|
Blue_In_Face
Starting Member
4 Posts |
 Posted - 2009-06-30 : 06:10:54
|
| I'm trying to execute ALTER TABLE ADD column01 varchar(50) and after that insert data into this table. All of the code is inside stored procedure and since I have to add 2 columns I'm using cursor to create and execute dynamic sql 'alter table add '+@col+' varchar(50)' twice to add columns.Few lines after, when I try to insert or update recently added columns I got a message that there is no one of these new columns. But, when I skip insert or update and simply ends stored procedure with a simple seclect statement, output is empty table with two new columns.Is there any sp limitation regarding such issue? |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Blue_In_Face
Starting Member
4 Posts |
Posted - 2009-06-30 : 16:24:22
|
| Well, I just want to add two or three columns to existing table and insert few rows into table with added columns. All of this logic should be placed into stored procedure. It is no problem to make it in t-sql batches separated with GO, it works perfect, but writing this in store procedure's body makes some trouble.I just don't understand why everything works fine if I avoid using of cursor, but this is the way I can add only one column. If I need more then one, I have to use cursor. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Blue_In_Face
Starting Member
4 Posts |
Posted - 2009-06-30 : 16:53:28
|
| Here is a part of code. CREATE PROCEDURE xTest AS IF EXISTS (SELECT name FROM sysobjects WHERE name = N'xTABLE' AND type = 'U') DROP TABLE xTABLE CREATE TABLE xTABLE(kol1 varchar(10),kol2 varchar(10)) IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE ID = OBJECT_ID('#T') AND type = 'U') DROP TABLE #T CREATE TABLE #T(k varchar(10)) INSERT INTO #T SELECT 'kol3' UNION SELECT 'kol4' DECLARE @VAR varchar(200) DECLARE @k varchar(10) DECLARE C CURSOR FOR SELECT k FROM #T OPEN C FETCH NEXT FROM C INTO @k WHILE(@@FETCH_STATUS=0) BEGIN SET @VAR='ALTER TABLE xTABLE ADD '+@k+' varchar(10)' EXEC(@VAR) FETCH NEXT FROM C INTO @k END CLOSE C DEALLOCATE C -- WITHOUT INSERT BELOW EVERYTHING WORKS FINE, WITH INSERT THERE IS AN ERROR -- Insert Error: Column name or number of supplied values does not match table definition. -- Or -- Invalid column name 'kol3'. INSERT INTO xTABLE(kol1,kol2,kol3,kol4) VALUES ('A','B','C','D')GO |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-30 : 17:39:26
|
Because at compilation time, xTABLE does not have those columns!Change the insert values to insert selectINSERT INTO xTABLE select 'A','B','C','D'
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Blue_In_Face
Starting Member
4 Posts |
Posted - 2009-07-01 : 02:00:18
|
| It doesn't help, I've done it before, both of cases, ...VALUES('A',...) and SELECT 'A','B'... and output error is mostly the same. INSERT just can't find first added column. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-01 : 03:02:16
|
It's because it's net there when the compile of procedure is made.Try to make the last insert statement dynamic too. That way, it's evaluated first at run time.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
adding columns and insert table in same stored pro