SQL4 Data loading(Select Into, Insert Into, Merge)--SELECT INTO: select * into emp_backup from emp --INSERT INTO: insert into emp_backup select * from emp what is the difference between select into & insert into? select into creates the table specified after into Obviously insert into requires that the table already exist Data Change Command MERGE Command MERGE Command Introduction MERGE command can perform insertions, updates, and deletions in a single statement. The MERGE command can react appropriately to the existence or nonexistence of rows. The following code creates new tables in order to demonstrate MERGE command. Demo SQL> SQL> drop table delta_tab; Table dropped.-- w w w . j av a 2s . c om SQL> SQL> create table delta_tab 2 ( pid number, 3 sales number, 4 status varchar2(6)); Table created. SQL> SQL> drop table master_tab; Table dropped. SQL> SQL> create table master_tab 2 (pid number, sales number, status varchar2(6)); Table created. SQL> SQL> insert into master_tab values(1,12,'CURR'); SQL> insert into master_tab values(2,13,'NEW' ); SQL> insert into master_tab values(3,15,'CURR'); SQL> insert into delta_tab values(2,24,'CURR'); SQL> insert into delta_tab values(3, 0,'OBS' ); SQL> insert into delta_tab values(4,42,'CURR'); SQL> SQL> select * from master_tab; PID SALES STATUS ---------- ---------- ------ 1 12 CURR 2 13 NEW 3 15 CURR SQL> select PID,SALES,STATUS from delta_tab; PID SALES STATUS ---------- ---------- ------ 2 24 CURR 3 0 OBS 4 42 CURR SQL> SQL> merge into master_tab m 2 using delta_tab d 3 on (m.pid = d.pid) 4 when matched 5 then update set m.sales = m.sales+d.sales 6 , m.status = d.status 7 delete where m.status = 'OBS' 8 when not matched 9 then insert values (d.pid,d.sales,'NEW'); 3 rows merged. SQL> SQL> select * from master_tab; PID SALES STATUS ---------- ---------- ------ 1 12 CURR 2 37 CURR 4 42 NEW CREATE TABLE [dbo].[StgEmp]( [empno] [int] NULL, [empname] [varchar](50) NULL, [title] [varchar](50) NULL, [deptname] [varchar](50) NULL, [maritulstatus] [char](1) NULL ) empno empname title deptname maritulstatus 121 kunal developer IT M 122 ragava asst.manager sales S 123 arman manager IT M CREATE TABLE [dbo].[DimEmp]( [empno] [int] NULL, [empname] [varchar](50) NULL, [title] [varchar](50) NULL, [deptname] [varchar](50) NULL, [maritulstatus] [char](1) NULL, [UpdateDate] [date] NULL, [EndDate] [date] NULL, [Indicator] [char](10) NULL, [EmpKey] [int] IDENTITY(1,1) NOT NULL ) empno empname title deptname maritulstatus UpdateDate EndDate Indicator EmpKey 121 kunal developer IT M NULL NULL NULL 1 --use SCD Type 1 MERGE INTO [dbo].[DimEmp] Dest USING [dbo].[StgEmp] Src ON Dest.[EmpNo] = Src.[EmpNo] WHEN MATCHED AND (Dest.[MaritulStatus] != Src.[MaritulStatus]) THEN UPDATE SET Dest.[MaritulStatus] = Src.[MaritulStatus]; GO select * from StgEmp select * from DimEmp --USE SCD TYPE 2 -- This inserts another record to the dimension for SCD Type changes INSERT INTO [dbo].[DimEmp] ([empno],[empname], [title], [maritulstatus], [UpdateDate], [EndDate], [Indicator]) SELECT [empno],[empname], [title], [maritulstatus],[UpdateDate], [EndDate], [Indicator] FROM ( MERGE [dbo].[DimEmp] dest USING [dbo].[StgEmp] src ON (dest.[empno] = src.[empno]) -- This inserts new records in the dimension table WHEN NOT MATCHED THEN INSERT ([empno],[empname], [title], [maritulstatus], [UpdateDate], [EndDate], [Indicator]) VALUES ([empno],[empname], [title], [maritulstatus], '01/01/1900', NULL, 'Y') --This marks the older record to be outdated for SCD Type 2 WHEN MATCHED AND dest.[Indicator] = 'Y' AND (ISNULL(dest.[Title], '') != ISNULL(src.[Title], '')) THEN UPDATE SET dest.[Indicator] = 'N', src.[EndDate] = GETDATE() - 1 OUTPUT $Action Action_Taken, src.[EmpNo], src.[empname], src.[Title], src.[maritulstatus], GETDATE() AS [UpdateDate], NULL AS [EndDate], 'Y' AS [Indicator] ) AS MERGE_OUT WHERE MERGE_OUT.Action_Taken = 'UPDATE'; GO Cleaner Graphics |
|