''Invalid column name'' error after ALTER TABLE
-
I need to import users from one table into another, along with generating the appropriate userId value. When I execute the following code as one block, I get the error
Invalid column name 'userIdInt'
But if I execute the code in 2 separate blocks consecutively (and hardcode the value for @HospId in the 2nd block) it works fine. Anybody know why?
----------------------------------------------------------
DECLARE @idStart int
(code to assign @idStart and @hospId values…)
declare @stmt varchar(200)
set @stmt='ALTER TABLE UsersImport ADD userIdInt int IDENTITY (' + convert(varchar(10),@idStart) + ',1)'
exec (@stmt)
-------------------------------------------------------------------------------------------------------
INSERT INTO Users (
User_Id,
User_Name
)
SELECT DISTINCT
CAST(UI.userIdInt as varchar),
UI.[last name]
FROM UsersImport UI
-
Yep... if you try to execute the whole thing... it's a single "batch". During "compile" time, the whole thing is evaluated and since the column doesn't actually exist, yet, the second part fails. You WON'T be able to turn this into a stored proc but you could execute it as a script by adding the batch separator "GO" between the two sections.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That makes sense, but then I lose my variable values. But I can either put that in my table too or re-calculate it after the GO. Thanks!
-
For what it's worth,
This should also work (and your variable contents will be preserved):
DECLARE @idStart INT DECLARE @hospId VARCHAR(4) --(code to assign @idStart and @hospId values…) DECLARE @stmt VARCHAR(200) SET @stmt='ALTER TABLE UsersImport ADD userIdInt int IDENTITY (' + CONVERT(VARCHAR(10),@idStart) + ',1)' EXEC (@stmt) ------------------------------------------------------------------------------------------------------- INSERT INTO Users ( USER_ID, Hosp_Id, USER_NAME ) EXEC('SELECT DISTINCT CAST(UI.userIdInt as varchar), ''' + @HospId + ''', UI.[last name] FROM dbo.UsersImport UI')By using EXEC() for your DML after your DDL, SQL server will compile that statement seperatly and it will take into account the changes to UsersImport. This is a workaround for when you can't (or don't want to) use GO (for whatever reason)
And, incidentially, I don't think the DISTINCT is neccessary in your SELECT statement. Because you've added an IDENTITY column, every row will already be distinct.
SQL guy and Houston Magician
-
Cool... nice work around, Robert.
Stef, I gotta ask... why are you adding a column to a permanent table? Why doesn't the table already have that column? I'm obviously missing something here...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good question. Here's the scenario.
I need a re-useable script to import an excel spreadsheet of users, import them into a Users table (creating the proper user id and password along the way), then export a new spreadsheet which includes the new user id and password. The "permanant" table UsersImport is actually the table created from the excel import. I use that altered table to create the excel export. The table is deleted each time the script is run (on the DTS import).
-----------------------------------------------------------------------
--Step 1: Run DTS package (creates UsersImport table from Excel spreadsheet)
DECLARE @dtsSuccess int
EXEC @dtsSuccess = master.dbo.xp_cmdshell 'dtsrun /Smaddevnet /E /NpkgImportRiskQIUsers'
IF (@dtsSuccess = 0)
PRINT 'DTS import successful'
ELSE
PRINT 'DTS import failed'
----------------------------------------------------------------------
--Step 2: Insert new records into Users table from UsersImport table.
DECLARE @hospId int
DECLARE @hospId2 int
DECLARE @idStart int
SET @hospId = CAST((SELECT top 1 hospId FROM UsersImport) as int) --from .xls importprint @hospId
SET @hospId2 = CAST((SELECT DISTINCT Hosp_id FROM Users where Hosp_id = @hospId) as int) --from Quantifi Users table
IF @hospId = @hospId2 --then existing site
SET @idStart = (SELECT MAX(cast(right(user_id, 5)as integer)) FROM users WHERE left(user_id, 4) = @hospId and user_id <> hosp_id) + 1
ELSE --new site
SET @idStart = 1
--Add columns to UsersImport table
--(note: have to do this dynamically because IDENTITY won't directly take a variable as the seed value (@idStart))
declare @stmt varchar(200)
set @stmt='ALTER TABLE UsersImport ADD UserId varchar(15),Password varchar(50), intId int IDENTITY (' + convert(varchar(10),@idStart) + ',1)'
exec (@stmt)
GO
--Build new userId and save to imported table....
UPDATE UsersImport --Convert id into varchar format with hospId
SET UserId = RIGHT('0000' + CAST(hospId as varchar), 4) + RIGHT('00000' + CAST(intId as varchar), 5)
--Insert new user records into Quantifi Users table
INSERT INTO Users (
User_Id,
Hosp_Id,
Auth_Code,
User_Name,
Access_allow_Hours,
Security_Level_ID,
Device_Indicator,
Has_Device,
Active
 
SELECT DISTINCT
UI.UserId,
UI.hospId,
'11111',
UI.[last Name],
24,
6,
0,
0,
1
FROM UsersImport UI
--Now create password (can't do this above because it's obtained from a field generated in the above stmt)
UPDATE Users
SET Password = LEFT(rowguid, 8)
WHERE User_id IN
(SELECT UserId FROM UsersImport)
--Now push password back to import table....
UPDATE UsersImport
SET Password = U.Password
FROM Users U
WHERE UserId = U.User_id
-----------------------------------------------------------------------
--Step 3: Convert UsersImport table to new spreadsheet with user_id and password
DECLARE @dtsSuccess int
EXEC @dtsSuccess = master.dbo.xp_cmdshell 'dtsrun /Smaddevnet /E /NpkgExportRiskQIUsers'
IF (@dtsSuccess = 0)
PRINT 'DTS export successful'
ELSE
PRINT 'DTS export failed'
-----------------------------------------------------------------------
-
Then, don't add columns to an existing table... make a new table that has all of the necessary columns (I'm thinking temp table here) and copy the data from the import table to the temp table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply