update and delete from a flat file
-
Hello Everyone,
I'm trying to convert a flat access table file into a normalized database with three tables. my flat table contains duplicate SS#'s that I need to filter out in a query before they are inserted because the sql server table has a unique index that requires only one ss#. Does anyone know how to incorporate this into an insert or update statement?
Thanks,
Chieko
-
Why don't you first import the complete table into a temp table and then insert/update into the real tables
-
I agree, put the info in a temp table. Then use a "GROUP BY" select statement to figure out how has more than one SSN (the same).
DECLARE curData CURSOR FAST_FORWARD FOR Select distinct datacolumn2 from tblWorkingImportTable where datacolumn2 IN (
Select datacolumn2 from tblWorkingImportTable
GROUP BY datacolumn2
HAVING Count(datacolumn2) >= 2
)
thats my "figure out more than one SSN" where datacolumn2 is the SSN container column.
OPEN curData
-- Perform the first fetch.
fetch curData into @strUniqueID
select @firstQueryFETCHSTATUS = @@FETCH_STATUS
IF @firstQueryFETCHSTATUS <> 0
begin
select @msg = '<<No Duplicates In Import File/Data.>>'
print @msg
end
WHILE @firstQueryFETCHSTATUS = 0
BEGIN
--SELECT @msg = ' Duplicate ID (' + @strUniqueColumnName + ') = ' + convert(varchar(10), @strUniqueID)
--PRINT @msg
select @intCounter = 0
DECLARE @entryid int
DECLARE cursorInside CURSOR FAST_FORWARD FOR Select entryid from tblWorkingImportTable where datacolumn2 = @strUniqueID
OPEN cursorInside
-- Perform the first fetch.
fetch cursorInside into @entryid
IF @@FETCH_STATUS <> 0
begin
select @msg = '<<No data.>>'
--print @msg
end
WHILE @@FETCH_STATUS = 0
BEGIN
select @intCounter = @intCounter + 1
--SELECT @msg = ' One of the duplicates has an entryid = ' + convert(varchar(10), @entryid)
--PRINT @msg
--SELECT @msg = ' @intCounter = ' + convert(varchar(10), @intCounter)
--PRINT @msg
if @intCounter > 1
BEGIN
-- This is a duplicate
UPDATE tblWorkingImportTable
SET rowstatus = 'D'
WHERE entryid = @entryid
--select @msg = 'Updated status to D on entryid = ' + convert(varchar(10), @entryid)
--print @msg
END
FETCH NEXT FROM cursorInside INTO @entryid
END
CLOSE cursorInside
DEALLOCATE cursorInside
--Select rowstatus from tblWorkingImportTable where datacolumn2 = @strUniqueID
FETCH NEXT FROM curData INTO @strUniqueID
select @firstQueryFETCHSTATUS = @@FETCH_STATUS
END
CLOSE curData
DEALLOCATE curData
set nocount off
something like that
datacolumn2 is a varchar(128) which holds the "SSN" (in your case)
rowstatus is a char(1) which I put a D in for duplicates
entryid is a ... IDENDITY column i stick in there, just so i have some unique distinctino between rows.
-
Super simple to do this...there's really no need for any complicated code.
1) Create table that exactly mimics your text file, except that it has an identity column.
2) Import all the data (with DTS, setting up step 1 and 2 will take you 10 minutes)
3) Delete the records you do not want. For example, to delete all the last record imported, run the following:
delete i1
From ImportTable i1 (nolock)
JOIN ImportTable i2 (nolock) on i1.SSN = i2.SSN
Where i1.Identity < i2.Identity
4) Import data for Import table to live
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply