|
ra_pa_ta
Starting Member
13 Posts |
 Posted - 2011-10-18 : 11:00:03
|
| My extract file looks like this id name loc strtdtD001 M LV 1/1/2010 CREATE TABLE [dbo].[D1]( [id] [varchar](7) NULL, [Name] [varchar](60) NULL, [Loc] [varchar](50) NULL, [startdt] datetime) GOinsert D1([id],[Name],[Loc],[startdt]) values('D001','M','LV','1/1/2010')insert D1([id],[Name],[Loc],[startdt]) values('D004','C','WA','1/1/2011')insert D1([id],[Name],[Loc],[startdt]) values('D001','M','WA','1/1/2011')The destination table looks like this id Name Loc startdt Updt D001 M Atlanta 1/1/2002 1/1/2008 CREATE TABLE [dbo].[D]( [id] [varchar](7) NULL, [Name] [varchar](60) NULL, [Loc] [varchar](50) NULL, [startdt]datetime null, Enddt datetime NULL, Updt datetime) GOinsert D([id],[Name],[Loc],[startdt],Updt) values('D001','M','Atlanta','1/1/2002','1/1/2008')insert D([id],[Name],[Loc],[startdt],Updt) values('D002','Aa','NJ','1/1/2004','1/1/2005')insert D([id],[Name],[Loc],[startdt],Updt) values('D003','An','WA','1/1/2010','1/1/2010')insert D([id],[Name],[Loc],[startdt],Updt) values('D001','M','Denver','1/1/2008','1/1/2009')I wrote the following queries to update and insert but they don't update correctly if two records of the same id comes in one extract file with different start dates . how should I change my query?Insert Into D Select T.*,dtmEnd = NULL,Updt= getdate() From D1 as T Left Outer Join D as A on T.id = A.id and T.startdt = A.startdt Where A.id is null Update A set A.Enddt = T.Startdt select * From D1 as T Join D as A on T.id = A.id where Enddt is null |
|
Update and insert into table storing history