Error Creating a table from Fields

Question

Hello Community,

I'm trying to create a table using the following fields and data types, but I'm keep on getting the error:

Incorrect syntax near the keyword 'column'.

I'm quite sure there is simple mistake that I'm making but not sure, any thoughts?
CREATE TABLE #tmpTable (
column varchar(100),
type varchar(20))

INSERT #tmpTable VALUES
('D_WORK_ORDER_KEY','BIGINT'),
('EQUIP_NO','VARCHAR'),
('HIGHEST_TASK','VARCHAR'),
('LAST_MODIFICATION_DT','DATETIME'),
('LAST_MODIFICATION_USER','VARCHAR'),
('MAINTENANCE_TYPE','VARCHAR'),
('MAN_EFFORT','VARCHAR'),
('NO_OF_TASKS','VARCHAR'),
('ORIGINATOR_ID','VARCHAR'),
('PARENT_WORK_ORDER_KEY','BIGINT'),
('PLAN_FINISH_DT','DATETIME'),
('PLAN_START_DT','DATETIME'),
('PO_ITEM_NO','VARCHAR'),
('PO_NO','VARCHAR'),
('PROJECT_NO','VARCHAR'),
('RAISED_DT','DATETIME'),
('REQUIRED_BY_DT','DATETIME'),
('REQUIRED_FINISH_DATE','DATETIME'),
('REQUIRED_START_DT','DATETIME'),
('RESPONDED_DT','DATETIME'),
('STD_JOB_NO','VARCHAR'),
('UNIT_OF_WORK','VARCHAR'),
('UOW_RATE','DECIMAL'),
('WO_DESC','VARCHAR'),
('WO_STATUS_M','VARCHAR'),
('WO_STATUS_U','VARCHAR'),
('WO_TYPE','VARCHAR'),
('WORK_GROUP','VARCHAR'),
('WORK_ORDER','VARCHAR'),
('ACTUAL_FINISH_DT','DATETIME'),
('ACTUAL_START_DT','DATETIME'),
('ASSIGN_PERSON','VARCHAR'),
('AUTHORISED_BY','VARCHAR'),
('AUTHORISED_DT','DATETIME'),
('AUTHORISED_POSITION','VARCHAR'),
('CLOSED_COMMIT_DT','DATETIME'),
('CLOSED_DT','DATETIME'),
('CLOSED_STATUS','VARCHAR'),
('CLOSED_STATUS_DESC','VARCHAR'),
('COMPLETED_BY','VARCHAR'),
('COMPLETED_CODE','VARCHAR'),
('CREATION_DT','DATETIME'),
('CREATION_USER','VARCHAR'),
('CREW','VARCHAR'),
('CUST_NO','VARCHAR'),
('DM_INSERT_DT','DATETIME'),
('DM_UPDATE_DT','DATETIME'),
('SOURCE_SYSTEM_KEY','VARCHAR'),
('SOURCE_SYSTEM_NAME','VARCHAR'),
('SOURCE_SYSTEM_KEY_VALUE','VARCHAR')

SELECT * FROM #tmpTable

Answer 1

char(6) is a character string exactly six characters long. In opposition to varchar(6) which is of variable length.

decimal and numeric are the same thing in SQL Server. (I believe that the ANSI standard has some subtle difference between the two, but it is nothing that SQL Server cares about.

Answer 2

Thanks TomCooper,

On an unrelated question, can you let me know what char() represents and numeric() represents in the following

ACTUAL_START_TIME char(6)
ACT_COST_INV numeric(21, 2)

My guess it could be interpreted as

ACTUAL_START_TIME varchar(6)
ACT_COST_INV decimal(21, 2)

Is that correct?

Answer 3

Column is a reserved word. If you are going to use it as an identifier, you must enclosed it in [ ]. So declare your table as

CREATE TABLE #tmpTable (
[column] varchar(100),
type varchar(20))

Tom