SQLTeam.com | Weblogs | Forums

Query to Insert data with logic


#1

Hi,

Here is the Logic. I am sure we should Use CURSOR or not but here is logic.
I want to Insert the record into #Table2_Desti table from #Table1_Source
First Step, Group by #Table1_Source.Link_ID if we receive two record and one record Location is EAST and second
record Location is WEST so exclude WEST record from INSERT statement
Second, If Group By #Table1_Source.Link_ID and if we receive only one record doesn't matter the location is EAST OR WEST
insert into #Table2_Desti TABLE.

Below is the sample code SQL

Please let me know if my question is not clear or need more info. Thank You.

CREATE TABLE #Table1_Source
(
id INT IDENTITY(1,1)
,Link_ID INT
,City VARCHAR(20)
,Phone VARCHAR(20)
,Location VARCHAR(20)

)

CREATE TABLE #Table2_Desti
(
id INT IDENTITY(1,1)
,City VARCHAR(20)
,Phone VARCHAR(20)
,Link_ID INT
)

------ Insert sample data
INSERT INTO #Table1_Source ( Link_ID, City, Phone, Location )
SELECT 1234, 'Chicago', '687-234-9980','East'
UNION all
SELECT 1234, 'New York', '454-233-9077', 'West'
UNION all
SELECT 7868, 'Los Angles', '21-556-0909','East'
UNION all
SELECT 9877,'Central','234-000-9878','West'

SELECT * FROM #Table1_Source
SELECT * FROM #Table2_Desti

Here IS the END RESULT that I want in #Table2_Desti

ID,City,Phone,Link_ID
1,Chicago,687-234-9980,East
3,Los Angles,21-556-0909,East
4,Central,234-000-9878,West


#2

I think you want something like:

INSERT INTO #Destination d (LinkID, City, Phone, Location)
SELECT * FROM #Source s
    WHERE s.City <> d.City

Somebody can fix my syntax, but I think that approach will work.


#3

Hi Andrewbb, Thank You for your reply.
I don't think that syntax will work. That syntax is good for may be sample data that I provided. However, the logic would be If the group by count > 1 and Location is West & East then don't INSERT WEST record in the Dest table.
If Group By = 1 and Location is West Or East, INSERT the record.
Thank You.


#4

I come up with below CURSOR and it is work with attached sample data. Please advise any better method than CURSOR. While loop could be the second Option. Thanks in advance.

DECLARE @LID as INT;
DECLARE @LRowCount AS int;
DECLARE @LocRowCount AS INT;

DECLARE CitCursor CURSOR FOR

Select
Link_ID
,Count(*) --as LinkRowCout
,COUNT(Distinct Location)--as LocationRowCount
from #Table1_Source
group by Link_ID

OPEN CitCursor;
FETCH NEXT FROM CitCursor INTO @LID,@LRowCount,@LocRowCount;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @LRowCount;
print @LocRowCount;
print @LID;
IF @LRowCount > 1 and @LocRowCount > 1
BEGIN
INSERT INTO #Table2_Desti ( City,Phone,Link_ID)
SELECT City,Phone,Link_ID fROM #Table1_Source
WHERE Link_ID = @LID --@LID
AND UPPER(Location) <> 'WEST'
END
ELSE
INSERT INTO #Table2_Desti ( City,Phone,Link_ID)
SELECT City,Phone,Link_ID fROM #Table1_Source
WHERE Link_ID = @LID

FETCH NEXT FROM CitCursor INTO @LID,@LRowCount,@LocRowCount;
END

PRINT 'Complete'
CLOSE CitCursor;
DEALLOCATE CitCursor;


#5

Try this:

insert into #Table2_Desti(city,phone,link_id)
select top(1) with ties
       city
      ,phone
      ,link_id
  from #Table1_Source
 order by row_number() over(partition by link_id order by [location])
;