Inserting distinct data from one table in another table, how?!?Really urgent and needing help!!!

 Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code:

 

create proc insertLocalization

AS
declare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)

select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)
select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalization

if (@idAp <> null)
begin
Insert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)
end
GO

 

This only inserts the last line in the "oltpLocalization" table.

 

O the other hand, like this:

create proc aaaa
as
declare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)
while exists (select distinct(idAp) from OLTPLocalization)
begin
    select @idAp =idAp from OLTPLocalization  where idAp not in (select idAp from dimLocalization)
    select @macAp = macAp from OLTPLocalization where idAp = @idAp
    select @building = building from OLTPLocalization where idAp = @idAp
    select @department = department from OLTPLocalization where idAP = @idAp
if (@idAp <> null)
begin
    insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)
end
end
go

 

this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table?

 

I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table.

 

Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!


Best regards,
David Ribeiro
0
dvd
5/24/2007 11:07:59 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
677 Views

Similar Articles

[PageSpeed] 31

How about something like: 

INSERT INTO
    dimLocalization
    (
        idAp,
        macAp,
        floorAp,
        building,
        department
    )
SELECT
    idAp,
    macAp,
    floorAp,
    building,
    department
FROM
    OLTPLocalization
WHERE
    idAp NOT IN (select idAp from dimLocalization)

0
Sohnee
5/25/2007 2:21:07 PM
Reply: