有段oracle代码,想转成mssql,熟练的大侠帮帮手--Insert 数据
Insert Into W_DAY_D
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
When TO_CHAR(TAB.A2, 'mm') < 7 Then
1
Else
2
End
From DUAL) CAL_HALF,
TO_NUMBER(EXTRACT(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(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
When TO_CHAR(TAB.A2, 'mm') < 7 Then
'上半年'
Else
'下半年'
End
From DUAL) PER_NAME_HALF,
EXTRACT(Month From TAB.A2) || '月' PER_NAME_MONTH,
'第' || TO_CHAR(TAB.A2, 'Q') || '季度' PER_NAME_QTR,
--to_char(TAB.A2,'DY') PER_NAME_WEEK,
'第' || TO_CHAR(TAB.A2, 'WW') || '周' PER_NAME_WEEK,
EXTRACT(Year From TAB.A2) || '年' PER_NAME_YEAR,
Sysdate W_INSERT_DT,
TO_NUMBER(TO_CHAR(TAB.A2, 'yyyymmdd')) INTEGRATION_ID,
Sysdate W_UPDATE_DT
From (Select ROWNUM As A1,
TO_DATE('20071231', 'yyyy-mm-dd,hh24:mi:ss') + ROWNUM As A2
From DUAL
Connect By ROWNUM <= 8036) TAB;
Insert Into W_DAY_D
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
When TO_CHAR(TAB.A2, 'mm') < 7 Then
1
Else
2
End
From DUAL) CAL_HALF,
TO_NUMBER(EXTRACT(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(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
When TO_CHAR(TAB.A2, 'mm') < 7 Then
'上半年'
Else
'下半年'
End
From DUAL) PER_NAME_HALF,
EXTRACT(Month From TAB.A2) || '月' PER_NAME_MONTH,
'第' || TO_CHAR(TAB.A2, 'Q') || '季度' PER_NAME_QTR,
--to_char(TAB.A2,'DY') PER_NAME_WEEK,
'第' || TO_CHAR(TAB.A2, 'WW') || '周' PER_NAME_WEEK,
EXTRACT(Year From TAB.A2) || '年' PER_NAME_YEAR,
Sysdate W_INSERT_DT,
TO_NUMBER(TO_CHAR(TAB.A2, 'yyyymmdd')) INTEGRATION_ID,
Sysdate W_UPDATE_DT
From (Select ROWNUM As A1,
TO_DATE('20071231', 'yyyy-mm-dd,hh24:mi:ss') + ROWNUM As A2
From DUAL
Connect By ROWNUM <= 8036) TAB;
下面是表结构
CREATE TABLE [dbo].[W_DAY_D](
[ROW_WID] [decimal](10, 0) NOT NULL,
[MONTH_WID] [decimal](10, 0) NULL,
[QUARTER_WID] [decimal](10, 0) NULL,
[YEAR_WID] [decimal](10, 0) NULL,
[CALENDAR_DATE] [date] NULL,
[CAL_HALF] [decimal](2, 0) NULL,
[CAL_MONTH] [decimal](2, 0) NULL,
[CAL_QTR] [decimal](1, 0) NULL,
[CAL_WEEK] [decimal](2, 0) NULL,
[CAL_YEAR] [decimal](4, 0) NULL,
[DAY_AGO_DT] [date] NULL,
[DAY_AGO_WID] [decimal](10, 0) NULL,
[DAY_NAME] [varchar](30) NULL,
[DAY_OF_MONTH] [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] [decimal](10, 0) NULL,
[MONTH_NAME] [varchar](30) NULL,
[QUARTER_AGO_DT] [date] NULL,
[QUARTER_AGO_WID] [decimal](10, 0) NULL,
[WEEK_AGO_DT] [date] NULL,
[WEEK_AGO_WID] [decimal](10, 0) NULL,
[YEAR_AGO_DT] [date] NULL,
[YEAR_AGO_WID] [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 [PRIMARY]