How Create a table from existing Values

Carlton Patterson 741 Reputation points
2021-09-26T21:11:24.8+00:00

Hi Community,

Sorry for the purely worded title.

Basically, I would like to create a table with the following columns

USE MyDB
SET DATEFORMAT ymd

Insert into MDM_DEV.DIM_DATE (DATE_KEY,DAY_OF_WEEK,DAY_NUM_IN_MONTH,DAY_NUM_OVERALL,DAY_NAME,DAY_ABBREV,WEEKDAY_FLAG,WEEK_NUM_IN_YEAR,WEEK_NUM_OVERALL,WEEK_BEGIN_DATE,WEEK_BEGIN_DATE_KEY,MONTH,MONTH_NUM_OVERALL,MONTH_NAME,MONTH_ABBREV,MONTH_YEAR,QUARTER,YEAR,YEARMO,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR,LAST_DAY_IN_MONTH_FLAG,SAME_DAY_YEAR_AGO,WORKING_DAY_FLAG,DDMMYY_DATE,MONYY_DATE,YEAR_QUARTER,FISCAL_YEAR_QUARTER,LAST_DAY_IN_WEEK_FLAG,LAST_DAY_IN_QUARTER_FLAG,LAST_DAY_IN_FISCAL_YEAR_FLAG,FISCAL_WEEK_NUM,WORKING_DAY_SCOTLAND_FLAG) values (to_date('11-SEP-01','DD-MON-RR'),2,11,254,'Tuesday ','Tue','Y',37,null,to_date('10-SEP-01','DD-MON-RR'),20010910,9,null,'September','Sep','September 2001',3,2001,200109,6,2,2001,'N',to_date('11-SEP-00','DD-MON-RR'),'Y','11/09/01','Sep 01','2001-Q3','2001-Q2','N','N','N',24,null);

Basically, I'm trying to avoid having to write out each and every column. The above has all the columns I need plus sample data for each column. I would like create a table with the columns using the above.

Can someone help with that?

Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-09-27T03:14:31.827+00:00

    Hi @Carlton Patterson ，

    The data types of many columns in your table are not in the correct time/date format, so the error you see will be returned when inserting data.

    I modified the data type of your column, please refer to: 

    CREATE TABLE dimDate  
(DATE_KEY DATETIME NULL,DAY_OF_WEEK INT NULL,DAY_NUM_IN_MONTH INT NULL  
,DAY_NUM_OVERALL INT NULL,DAY_NAME CHAR(15) NULL,DAY_ABBREV CHAR(15) NULL  
,WEEKDAY_FLAG CHAR(15) NULL,WEEK_NUM_IN_YEAR INT NULL,WEEK_NUM_OVERALL DATETIME NULL  
,WEEK_BEGIN_DATE DATETIME NULL,WEEK_BEGIN_DATE_KEY DATETIME NULL,MONTH INT NULL  
,MONTH_NUM_OVERALL DATETIME NULL,MONTH_NAME CHAR(15) NULL,MONTH_ABBREV CHAR(15) NULL  
,MONTH_YEAR CHAR(15) NULL,QUARTER INT NULL,YEAR INT NULL  
,YEARMO DATETIME NULL,FISCAL_MONTH INT NULL,FISCAL_QUARTER INT NULL  
,FISCAL_YEAR INT NULL,LAST_DAY_IN_MONTH_FLAG CHAR(15) NULL,SAME_DAY_YEAR_AGO DATETIME NULL  
,WORKING_DAY_FLAG CHAR(15) NULL,DDMMYY_DATE DATETIME NULL,MONYY_DATE CHAR(15) NULL  
,YEAR_QUARTER CHAR(15) NULL,FISCAL_YEAR_QUARTER CHAR(15) NULL,LAST_DAY_IN_WEEK_FLAG CHAR(15) NULL  
,LAST_DAY_IN_QUARTER_FLAG CHAR(15) NULL,LAST_DAY_IN_FISCAL_YEAR_FLAG CHAR(15) NULL  
,FISCAL_WEEK_NUM INT NULL,WORKING_DAY_SCOTLAND_FLAG DATETIME NULL  
 ) ON [PRIMARY]  
 GO  
  
 Insert into dimDate values   
 (convert(DATE,'11-SEP-01',104),2,11,254,  
 'Tuesday  ','Tue','Y',37,null,  
  convert(DATE, '10-SEP-01',104),  
  convert(DATE,'20010910',104),9,null,  
  'September','Sep','September 2001',3,  
  2001,200109,6,2,2001,'N',  
  convert(DATE,'11-SEP-00',104),  
  'Y','11/09/01','Sep 01','2001-Q3',  
  '2001-Q2','N','N','N',24,null);  
  
SELECT * FROM  dimDate

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Carlton Patterson 741 Reputation points
    2021-09-26T21:39:02.1+00:00

    I managed to figure out how to create a table using the columns above. Thanks anyway. However, I'm now getting the following error when I try to add values to fields

    Conversion failed when converting date and/or time from character string

    The SQL code is: 

    USE MyTestDB
SET DATEFORMAT ymd

Insert into MDM_DEV.dimDate (DATE_KEY,DAY_OF_WEEK,DAY_NUM_IN_MONTH,DAY_NUM_OVERALL,DAY_NAME,DAY_ABBREV,WEEKDAY_FLAG,WEEK_NUM_IN_YEAR,WEEK_NUM_OVERALL,WEEK_BEGIN_DATE,WEEK_BEGIN_DATE_KEY,MONTH,MONTH_NUM_OVERALL,MONTH_NAME,MONTH_ABBREV,MONTH_YEAR,QUARTER,YEAR,YEARMO,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR,LAST_DAY_IN_MONTH_FLAG,SAME_DAY_YEAR_AGO,WORKING_DAY_FLAG,DDMMYY_DATE,MONYY_DATE,YEAR_QUARTER,FISCAL_YEAR_QUARTER,LAST_DAY_IN_WEEK_FLAG,LAST_DAY_IN_QUARTER_FLAG,LAST_DAY_IN_FISCAL_YEAR_FLAG,FISCAL_WEEK_NUM,WORKING_DAY_SCOTLAND_FLAG) values (convert(DATE,'11-SEP-01',104),2,11,254,'Tuesday  ','Tue','Y',37,null,convert(DATE, '10-SEP-01',104),20010910,9,null,'September','Sep','September 2001',3,2001,200109,6,2,2001,'N',convert(DATE,'11-SEP-00',104),'Y','11/09/01','Sep 01','2001-Q3','2001-Q2','N','N','N',24,null);

    And the table is as follows: 

    CREATE TABLE HMCTS.MDM_DEV.dimDate (
  DATE_KEY DATETIME NULL
 ,DAY_OF_WEEK DATETIME NULL
 ,DAY_NUM_IN_MONTH DATETIME NULL
 ,DAY_NUM_OVERALL DATETIME NULL
 ,DAY_NAME DATETIME NULL
 ,DAY_ABBREV DATETIME NULL
 ,WEEKDAY_FLAG DATETIME NULL
 ,WEEK_NUM_IN_YEAR DATETIME NULL
 ,WEEK_NUM_OVERALL DATETIME NULL
 ,WEEK_BEGIN_DATE DATETIME NULL
 ,WEEK_BEGIN_DATE_KEY DATETIME NULL
 ,MONTH DATETIME NULL
 ,MONTH_NUM_OVERALL DATETIME NULL
 ,MONTH_NAME DATETIME NULL
 ,MONTH_ABBREV DATETIME NULL
 ,MONTH_YEAR DATETIME NULL
 ,QUARTER DATETIME NULL
 ,YEAR DATETIME NULL
 ,YEARMO DATETIME NULL
 ,FISCAL_MONTH DATETIME NULL
 ,FISCAL_QUARTER DATETIME NULL
 ,FISCAL_YEAR DATETIME NULL
 ,LAST_DAY_IN_MONTH_FLAG DATETIME NULL
 ,SAME_DAY_YEAR_AGO DATETIME NULL
 ,WORKING_DAY_FLAG DATETIME NULL
 ,DDMMYY_DATE DATETIME NULL
 ,MONYY_DATE DATETIME NULL
 ,YEAR_QUARTER DATETIME NULL
 ,FISCAL_YEAR_QUARTER DATETIME NULL
 ,LAST_DAY_IN_WEEK_FLAG DATETIME NULL
 ,LAST_DAY_IN_QUARTER_FLAG DATETIME NULL
 ,LAST_DAY_IN_FISCAL_YEAR_FLAG DATETIME NULL
 ,FISCAL_WEEK_NUM DATETIME NULL
 ,WORKING_DAY_SCOTLAND_FLAG DATETIME NULL
) ON [PRIMARY]
GO

    Any thoughts greatly appreciated.