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
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)
Insert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)
This only inserts the last line in the "oltpLocalization" table.
O the other hand, like this:
create proc aaaa
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)
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)
insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)
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!
How about something like:
idAp NOT IN (select idAp from dimLocalization)