- Insert data
Insert Into W_DAY_D
The Select TO_NUMBER (TO_CHAR (TAB) A2, 'yyyymmdd')) ROW_WID,
TO_NUMBER (SUBSTR (TO_CHAR (TAB. A2, 'yyyymmdd'), 1, 6)) MONTH_WID,
TO_NUMBER (EXTRACT (Year From TAB. A2)) | | TO_NUMBER (TO_CHAR (TAB) A2, 'Q')) QUARTER_WID,
TO_NUMBER (EXTRACT (Year From TAB. A2)) YEAR_WID,
TAB. A2 CALENDAR_DATE,
(Select Case
The When TO_CHAR (TAB. A2, 'mm) & lt; 7 Then
1
The Else
2
End
The From DUAL) CAL_HALF,
TO_NUMBER (EXTRACT (the Month From TAB. A2)) CAL_MONTH,
TO_NUMBER (TO_CHAR (TAB) A2, 'Q')) CAL_QTR,
TO_NUMBER (TO_CHAR (TAB) A2, 'WW)) CAL_WEEK,
TO_NUMBER (EXTRACT (Year From TAB. A2)) CAL_YEAR,
TAB. A2-1 DAY_AGO_DT,
TO_NUMBER (TO_CHAR ((TAB. A2-1), 'yyyymmdd')) DAY_AGO_WID,
, to_char (TAB. A2) DAY_NAME,
TO_CHAR (TAB. A2, 'DY) DAY_NAME,
TO_NUMBER (TO_CHAR (TAB) A2, 'dd')) DAY_OF_MONTH,
TO_NUMBER (TO_CHAR (TAB) A2, 'D')) DAY_OF_WEEK,
TO_NUMBER (TO_CHAR (TAB) A2, 'DDD)) DAY_OF_YEAR,
ADD_MONTHS (TAB. A2-1) MONTH_AGO_DT,
TO_NUMBER (TO_CHAR (ADD_MONTHS (TAB. A2, 1), 'yyyymmdd')) MONTH_AGO_WID,
TO_CHAR (EXTRACT (the Month From TAB. A2)) MONTH_NAME,
ADD_MONTHS (TAB. A2, - 3) QUARTER_AGO_DT,
TO_NUMBER (TO_CHAR (ADD_MONTHS (TAB. A2, - 3), 'yyyymmdd')) QUARTER_AGO_WID,
TAB. A2-7 WEEK_AGO_DT,
TO_NUMBER (TO_CHAR (TAB. A2-7, 'yyyymmdd')) WEEK_AGO_WID,
ADD_MONTHS (TAB. A2-12) YEAR_AGO_DT,
TO_NUMBER (TO_CHAR (ADD_MONTHS (TAB) A2, - 12), 'yyyymmdd')) YEAR_AGO_WID,
(Select Case
The When TO_CHAR (TAB. A2, 'mm) & lt; 7 Then
'in the first half of the'
The Else
'in the second half of the'
End
The From DUAL) PER_NAME_HALF,
EXTRACT (the Month From TAB. A2) | | 'Month' PER_NAME_MONTH,
'the first' | | TO_CHAR (TAB. A2, 'Q') | | 'quarter' PER_NAME_QTR,
, to_char (TAB. A2, 'DY) PER_NAME_WEEK,
'the first' | | TO_CHAR (TAB. A2, "WW") | | 'week' PER_NAME_WEEK,
EXTRACT (Year From TAB. A2) | | 'years' PER_NAME_YEAR,
Sysdate W_INSERT_DT,
TO_NUMBER (TO_CHAR (TAB) A2, 'yyyymmdd')) INTEGRATION_ID,
Sysdate W_UPDATE_DT
the From (Select ROWNUM As A1,
TO_DATE (' 20071231 ', '- dd yyyy - mm, hh24: mi: ss') + ROWNUM As A2
The From DUAL
Connect By ROWNUM & lt;=8036) TAB.
CodePudding user response:
Suggest the original poster of table structure was also involved in the post;Also, where are you encounter problems, which can be specific to one;
CodePudding user response:
Hello, the first question is code TO_DATE (' 20071231 ', '- dd yyyy - mm, hh24: mi: ss') + ROWNUM As A2 in A2 in MSSQL how? Oracle has dual table, can be directly put the rownum combined with other characters, try to use in the MSSQL IDENTITY to solve, but it must be combined with the select,,, into,,,, as a result, other problems, don't know how to achieve the result of the A2, or IDENTITY still have what way?Below is a table structure
The CREATE TABLE [dbo] [W_DAY_D] (
[ROW_WID] [a decimal] (10, 0) NOT NULL,
[MONTH_WID] [a decimal] (10, 0) NULL,
[QUARTER_WID] [a decimal] (10, 0) NULL,
[YEAR_WID] [a decimal] (10, 0) NULL,
[CALENDAR_DATE] [date] NULL,
[CAL_HALF] [a decimal] (2, 0) NULL,
[CAL_MONTH] [a decimal] (2, 0) NULL,
[CAL_QTR] (decimal) (1, 0), NULL,
[CAL_WEEK] [a decimal] (2, 0) NULL,
[CAL_YEAR] [a decimal] (4, 0) NULL,
[DAY_AGO_DT] [date] NULL,
[DAY_AGO_WID] [a decimal] (10, 0) NULL,
[DAY_NAME] [varchar] (30) NULL,
[DAY_OF_MONTH] [a decimal] (2, 0) NULL,
[DAY_OF_WEEK] (decimal) (1, 0), NULL,
[DAY_OF_YEAR] (decimal) (3, 0) NULL,
[MONTH_AGO_DT] [date] NULL,
[MONTH_AGO_WID] [a decimal] (10, 0) NULL,
[MONTH_NAME] [varchar] (30) NULL,
[QUARTER_AGO_DT] [date] NULL,
[QUARTER_AGO_WID] [a decimal] (10, 0) NULL,
[WEEK_AGO_DT] [date] NULL,
[WEEK_AGO_WID] [a decimal] (10, 0) NULL,
[YEAR_AGO_DT] [date] NULL,
[YEAR_AGO_WID] [a decimal] (10, 0) NULL,
[PER_NAME_HALF] [varchar] (50) NULL,
[PER_NAME_MONTH] [varchar] (50) NULL,
[PER_NAME_QTR] [varchar] (50) NULL,
[PER_NAME_WEEK] [varchar] (50) NULL,
[PER_NAME_YEAR] [varchar] (50) NULL,
[INSERT_DT] [datetime] NULL,
[INTEGRATION_ID] [varchar] (30) NULL,
[UPDATE_DT] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ROW_WID] ASC
) WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON (PRIMARY)
) ON the (PRIMARY)