Hi all
I am busy converting data from one DB to another.
Part of the process is to create a record in the NEW table for each record that has existed in the OLD table (even if that record has been deleted from the old table) [Weird I know - don't ask!]
the OLD table has a STUDID field that is a decimal (8,0) (SQL 2005)
the NEW table is using an integer for the ID_Student field. (SQL 2012)
In order to ensure that a record is created for each OLD student, I am trying to assign the value of MAX(StudentID) from the OLD table and loop through an insert cycle to create a dummy record for each student. (True data is updated by a series of update queries later)
So to start the loop, I have tried
DECLARE @intFlag INT
SET @intFlag = 'SELECT CAST(MAX(STUDID) AS integer) FROM OLD_TBL_STUDENT'
-- check that a value has been assigned
PRINT 'INTFLAG = ' + @IntFlag
The 'SELECT CAST(MAX(STUDID) AS integer) FROM OLD_TBL_STUDENT' part runs just fine as a stand-alone command, but I get errors
So after numerous tests, I eventually tried
DECLARE @MyVal INTEGER
set @MyVal = 1
print 'MyVal = ' + @MyVal
and found that the first 2 lines work OK....HOWEVER, when I try to include the PRINT statement, I run into a
'Conversion failed when converting the varchar value 'MyVal = ' to data type int.' issue
This error implies that even though I declared the variable as an integer, the system is assigning it as type varchar. So what's happening here?
How can I assign the value to my @intFlag variable so that I can loop through the process accordingly.
Thanks