question

CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 asked CarltonPatterson-0401 commented

How Create a table from existing Values

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

People often think they have a good solution to a fundamental problem and then ask for help with the solution. In this case, the fundamental problem is loading some data into SQL Server from someplace else. If you can explain what the format of the data is that you already have (before formatting into a SQL insert statement) then it is likely someone can suggest an easier solution. If the data can be exported or formatted as a CSV or as JSON or XML then that would be a good format to use.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered CarltonPatterson-0401 commented

Hi @CarltonPatterson-0401

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Perfect Echo.
Thank you very much.

0 Votes 0 ·
CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well, everything is nullable, which is suspect. Keep in mind that NULL is a special value that needs extra handling. It you permit NULL for a column, you also need to have an understanding of what NULL means for that particular column.

Yes, NULL, is "unknown value", but in practice NULL has a more specific meaning, depending on the column.

This appears to be a calendar table - I can see why any of the columns should be NULL.

Also, there is a lot of datetime. Are you sure that this is the correct data type for all columns? Or for that matter any column? Take DAY_OF_WEEK? I would expect something like char(3) here (for Mon, Tue, Wed etc) or tinyint for 1 to 7.

0 Votes 0 ·