Trouble With Basic Table To Table Insert

I'm a horrible noob everyone, so my apologies come up front.

I know Oracle allows me to just do an

INSERT INTO table
SELECT column1, column2
FROM table2

But I cannot get this to work in MS SQL 2000. (See question #3)



Here's my script that I'm trying to execute from Enterprise Manager:

From this table:

CREATE TABLE ISIS_DATA
(ISIS_STATUS_ID VARCHAR(15) CONSTRAINT ISIS_DATA_ISIS_ID_PK PRIMARY KEY,
ISIS_NAME VARCHAR(50),
ISIS_CLASS_EXPIR_DAT VARCHAR(20),
ISIS_SEX_BD_CAT_SCHL VARCHAR(20))

Where this is a sample row of data:

A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G

I want to run that data through some substrings etc. and dump it into another table like this:

//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM
//EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS.
CREATE TABLE ISIS_DATA_PREP
(ISIS_STATUS_ID VARCHAR(15),
STATUS VARCHAR(5),
STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY,
LAST_NAME VARCHAR(25),
FIRST_NAME VARCHAR(20),
MID_INIT CHAR(1),
CLASS NUMERIC(2),
EXPIR_DATE VARCHAR(10),
SEX CHAR(1),
BIRTHDAY VARCHAR(10),
CAT NUMERIC(3),
SCHOOL VARCHAR(5))


Using this script so far:


INSERT INTO ISIS_DATA_PREP
SELECT B.STATUS, B.STUDENT_ID, B.LAST_NAME, B.FIRST_NAME, B.MID_INIT,
B.CLASS, B.EXPIR_DATE,
B.SEX, B.BIRTHDAY, B.CAT, B.SCHOOL, A.ISIS_STATUS_ID
FROM ISIS_DATA A,
(SELECT SUBSTRING(ISIS_STATUS_ID, 1, 1) STATUS, SUBSTRING(ISIS_STATUS_ID, 2, 9) STUDENT_ID,
ISIS_NAME LAST_NAME, SUBSTRING(ISIS_NAME, 5, 1) FIRST_NAME, SUBSTRING(ISIS_NAME, 5, 1) MID_INIT,
SUBSTRING(ISIS_CLASS_EXPIR_DAT, 1, 1) CLASS, SUBSTRING(ISIS_CLASS_EXPIR_DAT, 2, 8) EXPIR_DATE,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 1,1) SEX, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 2, 8) BIRTHDAY,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 10, 1) CAT, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 11, 2) SCHOOL,
ISIS_STATUS_ID
FROM ISIS_DATA) B
WHERE A.ISIS_STATUS_ID = B.ISIS_STATUS_ID


I keep getting this error:


Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.


QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?




So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.


Now I get this message:


Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.



I look up this error on MS's knowledge base and get something along these lines:

...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....




QUESTION #2 -

What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.



QUESTION #3 -
My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.



Sorry for the absolutely massive post. : ) And thanks for any feedback.

Lance