Well, I am really sorry because my explanation was so poor. Thank you for all the answers.

I will explain better what should be the output and what is my question.

So, first of I have an array of tagCodes like ('code0','code1','code2'). Then I have a table that contains Codes and TagTypeId.

I would like to get into a temporary table all the codes I passed in the array with their TagTypeId. So a table like:

So my attempt was this one:

SELECT Tags.Code AS tagCode, Tags.TagTypeId, TagTypes.Code AS tagType 
INTO #TempTable
FROM Tags JOIN TagTypes ON Tags.TagTypeId = TagTypes.Id 
WHERE Tags.Code IN ('903420012408181609019A18','90341808151313061101E938', 'TestCodeNull')
SELECT * FROM #TempTable;

But I dont get the codes that are not in the Tag table.

I did this an it seems to be working as intended:

CREATE TABLE #TestTable (tagCode NVARCHAR(25) NOT NULL, TagTypeId INT NULL, tagType NVARCHAR(MAX))
INSERT INTO #TestTable (tagCode) VALUES ('903420012408181609019A18'),('00007E08190D0A34E1F524D0'),('00007E08190D0B25E1F5A98B')
UPDATE #TestTable SET TagTypeId = Tags.TagTypeId, tagType = TagTypes.Code FROM #TestTable
LEFT JOIN Tags ON (#TestTable.tagCode = Tags.Code)
LEFT JOIN TagTypes ON (Tags.TagTypeId = TagTypes.Id)
SELECT * FROM #TestTable;

CodePudding user response：

I think what you mean that 'TestCodeNull' does not exist in tags so you want to show null for 'TestCodeNull' in which case a join may be more appropriate. for example

SELECT S.CODE,Tags.Code AS tagCode, Tags.TagTypeId, 
       TagTypes.Code AS tagType 
INTO #TempTable
FROM (select '903420012408181609019A18' code 
      union all select '90341808151313061101E938'
      union all select 'TestCodeNull') s
left join Tags on tags.code = s.code 
left JOIN TagTypes ON Tags.TagTypeId = TagTypes.Id 
 
SELECT * FROM #TempTable;