procedure add column to tbl and then update notWrk

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

SQL Server 2000 Forums

Transact-SQL (2000)

procedure add column to tbl and then update notWrk

Author

Topic

DinoY
Starting Member

3 Posts

Posted - 2011-04-05 : 05:36:13

the procedure below
I drop field iteid
bulk insert some staff
I add column iteid in table NOE_autoStTkng
and then then want to run the update statment
UPDATE NOE_autoStTkng SET iteID=ID FROM NOE_autoStTkng N,MATERIAL M WHERE N.CODE=M.CODE

BUT it gives me the message field iteid does not exists
if i take out the update statment all works fine
droping field and adding it

what am i doing wrong????
thank you in advance

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC NOE_AUTOsTK
@sdate VARCHAR(10),
@store integer
AS
declare @update as int
set @update=0
--exec NOE_AUTOsTK '2011-12-31','1'

delete from NOE_autoStTkng

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NOE_autoStTkng'
AND COLUMN_NAME = 'iteID')
begin
print 'droping ITEID'
alter table NOE_autoStTkng drop column [iteID]
BULK INSERT NOE_autoStTkng FROM 'c:\MSSQL\BAC\totalqtys.txt' WITH (DATAFILETYPE = 'CHAR', FIELDTERMINATOR=',', ROWTERMINATOR='\n')
end

IF not EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NOE_autoStTkng'
AND COLUMN_NAME = 'iteID')
begin
alter table NOE_autoStTkng add [iteID] int
print 'Created iteid'
set @update=1
end

print @update
if (@update=1)
begin
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NOE_autoStTkng'
AND COLUMN_NAME = 'iteID')
begin
print 'updating ITEID'
UPDATE NOE_autoStTkng SET iteID=ID FROM NOE_autoStTkng N,MATERIAL M WHERE N.CODE=M.CODE

end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 09:16:24

I stared at your code for 10 minutes, and I can't find anything wrong - it looks correct to me. Can you post the exact error message it is printing out?

Also not that this will help your problem, but something about dropping the column, doing the bulk-insert and then adding the column back bothers me. Can't put my finger on why that doesn't look very appealing. I don't know if it is because I think that if there were column level permissions or something those might get affected, or whether it is just my misguided intuition that makes me nervous about it.

If I had to do this, I would bulk insert into a staging table that does not have the iteID column and then insert from that table into the NOE_autoStTkng table.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 09:23:00

Ditto.

quote:
Originally posted by sunitabeck

...
If I had to do this, I would bulk insert into a staging table that does not have the iteID column and then insert from that table into the NOE_autoStTkng table.

Corey

I Has Returned!!

llinares
Starting Member

3 Posts

Posted - 2011-04-05 : 10:49:48

Does the code work if you take it out of the stored procedure?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 12:31:29

Your add the column statement lives in an IF clause, so SQL Server dosn't know of it's existence. It may or may not exist, depending on the out come of the IF statement.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 12:57:05

Dino's IF statement is checking whether the column already exists. If it is not there, then the column is added. Jim, did you mean something like the parser error


Msg 2714, Level 16, State 1, Line 5
There is already an object named '#tmp' in the database.

that the following would give?

create table #tmp (id int);

drop table #tmp;

create table #tmp (id int);

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 13:10:01

No, I understand what his IF statement is doing, SQL just doesn't know what the outcome of it is. When SQL compiles the code that says
set iteID = , that iteID doesn't exist, so it gives an error, even though his code ensures that it will exist by that time. Staging tables are still the way to go, though.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 13:51:59

I think you're example is a good one for demonstrating this issue. I hope the gurus will chime in on this one.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 14:00:38

run this CREATE TABLE t1(col1 int)

and then run this twice
IF NOT EXISTS(select * from INFORMATION_SCHEMA.COLUMNS WHERE table_name = N't1' and column_name = N't2')

ALTER TABLE t1
ADD col2 int

Jim

3 unanswered posts in a row, way to pad that post count!

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 14:11:23

quote:

IF NOT EXISTS(select * from INFORMATION_SCHEMA.COLUMNS WHERE table_name = N't1' and column_name = N't2col2')

ALTER TABLE t1
ADD col2 int

Did you mean to use col2 in the IF statement?

quote:

3 unanswered posts in a row, way to pad that post count!

Nah! I can't speak for others, but that thought did not even cross my mind, Jim! You would never do that!!

But, RobVolk is trying to figure out how to fix the Snitz code so we can add to our post count each time we preview a posting. See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158599

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 14:13:25

Doh!

Jim

Everyday I learn something that somebody else already knew

DinoY
Starting Member

3 Posts

Posted - 2011-04-08 : 04:56:30

quote:
Originally posted by llinares

Does the code work if you take it out of the stored procedure?

no it still gives me the error
but
If I run the statments individually
every thing runs ok

DinoY
Starting Member

3 Posts

Posted - 2011-04-08 : 05:01:21

Its like the server is not informed of the creation of the new column and it tries to run the update statement.
on the other hand the table does drop the column if i select * from NOE_autoStTkng, but the new column is not created
If i run the procedure taking out"UPDATE NOE_autoStTkng SET iteID=ID FROM NOE_autoStTkng N,MATERIAL M WHERE N.CODE=M.CODE"
everything runs fine, dropping and adding the column.
BTW its Sql2000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 08:01:44

May be it is time to use the staging table idea. The process is really simple. First create the staging table.


select top 0 * into NOE_autoStTkng_Staging from NOE_autoStTkng

Now you have a new table NOE_autoStTkng_Staging. Remove the iteID column from this staging table.

alter table NOE_autoStTkng_Staging drop column iteID

Both of these need to be done just once. Now you have the staging table ready.

Now bulk insert into this staging table.

BULK INSERT NOE_autoStTkng_Staging FROM 'c:\MSSQL\BAC\totalqtys.txt' WITH (DATAFILETYPE = 'CHAR', FIELDTERMINATOR=',', ROWTERMINATOR='\n')

Then from the staging table insert into your real table

insert into NOE_autoStTkng select *,null as iteID from NOE_autoStTkng_Staging -- you should change this to explicitly list the columns in the correct order in the select statement.

Now run your update statement on NOE_autoStTkng to set the correct value for iteID.

Finally get rid of the staging table data.

truncate table NOE_autoStTkng_Staging

Dropping and adding the column is not really a good practice. While it would work most of the time, consider these:
1. for the interval between dropping and recreating the column, it leaves any other stored procedures or scripts that depend on that column in a bad state.
2. When you drop and recreate the column, if there were any permissions denied or granted on that column, those would get wiped out.
3. Although it may not be an issue in this case, you will not be able to add this column as a foreign key in any other table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources