CODEWITHSUNDEEP
sqlsql-serverstored-procedures
GeoVIP
GeoVIP

Reputation: 1564

Insert one table values to query

Have table myTbl with values 

ID  Number
1    12458
2    45896  
3    12458
4    87456

Have query like :

SELECT TOP 1 n.abonent_name , ad.address
FROM dbo.names n 
INNER JOIN dbo.addresses ad on n.address_id = ad.id
WHERE n.number = (every number from myTbl)

Want insert into query Where clause each number from myTbl and then insert response for each number to virtual table #tbl for example insert into #tbl abonent_name and address where number = 12458 , after this insert ,write in where clause another number 45896 then insert into #tbl and etc.

CREATE TABLE #tbl
(
   id identity(1,1),
   abonent_name varchar(20),
   address varchar(50)
)

Want write it without cursor

Upvotes: 0

Views: 49

Answers (2)

Sandr
Sandr

Reputation: 776

You don't need to use a cursor. If there can be more that 1 row per name in the addresses table, you can use the following pattern:

INSERT INTO #tbl (abonent_name, address)
SELECT x.abonent_name, x.address
from
(
    SELECT
      ROW_NUMBER() OVER (PARTITION BY n.abonent_name
                         ORDER BY n.abonent_name, x.address) AS RowNumber,
      n.abonent_name,
      ad.address
    FROM dbo.names n 
    INNER JOIN myTbl tbl on n.number = tbl.number
    INNER JOIN dbo.addresses ad on n.address_id = ad.id
) x
where x.RowNumber = 1
order by x.RowNumber;

This code will insert into the #tbl table the first (alphabetical) address for each name which code exists in the myTbl table. Names will be sorted in alphabetical order ascending. Condition x.RowNumber=1 will filter the first address in case of multiple matches.

Upvotes: 0

Fuzzy
Fuzzy

Reputation: 3810

SELECT TOP 1 n.abonent_name , ad.address
FROM dbo.names n 
INNER JOIN dbo.addresses ad on n.address_id = ad.id
WHERE n.number IN (SELECT number from myTbl)

try above. But you can kill two birds with one stone by doing the following 

SELECT  ROW_NUMBER() OVER (order by n.abonent_name) AS ID, n.abonent_name , ad.address
INTO #tbl
FROM dbo.names n 
INNER JOIN dbo.addresses ad on n.address_id = ad.id
WHERE n.number IN (SELECT number from myTbl)

Upvotes: 1

Related Questions