need code : TSQL

Question

hi

i have two tables as below.

table1

DAY name
1 aaa
2 bbb
3 cccc
4 ddd
1 eee
1 bbb
1 ccc
2 ddd
3 aaa

table2
name
aaa
bbb
ccc
ddd
eee

what i need to is add name to table1 which is available table2 but in table1

for example, for 1

it should add

1 ddd
2 aaa
2 ccc
2 eee

for each day.

Answer 1

Hi @coool sweet ，

Please check if this is your expected output, if not, please provide a minimal example and your expected output：

    --Create test data  
    create table table1([DAY] int,[name] char(15))  
    insert into table1 values(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(1,'eee'),  
                              (1,'bbb'),(1,'ccc'),(2,'ddd'),(3,'aaa'),(1,'aaa')  
    create table table2 ([name] char(15))  
    insert into table2 values('aaa'),('bbb'),('ccc'),('ddd'),('eee')  
      
    select * from table1  
      
    --Find data that exists in table2 but not in table1 for each DAY  
    ;with cte  
    as(select 1 DAY,name from table2  
    where name not in (select name from table1 where DAY=1)   
    union all  
    select 2 DAY,name from table2  
    where name not in (select name from table1 where DAY=2)  
    union all  
    select 3 DAY,name from table2  
    where name not in (select name from table1 where DAY=3)   
    union all  
    select 4 DAY,name from table2  
    where name not in (select name from table1 where DAY=4))  
      
    insert into table1 --Insert the found data into table1  
    select * from cte;  
      
    --Final result  
    select * from table1 order by DAY,name  
      
    drop table table1   
    drop table table2   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Hi @coool sweet ，

I am guessing that you need a multiplication between two tables.

Check it out how to implement it via CROSS JOIN, Table Value Constructor, and set based EXCEPT operator.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE ([day] INT, [name] CHAR(3));  
INSERT INTO @tbl ([day], [name]) VALUES  
(1,'aaa'),  
(2,'bbb'),  
(3,'ccc'),  
(4,'ddd'),  
(1,'eee'),  
(1,'bbb'),  
(1,'ccc'),  
(2,'ddd'),  
(3,'aaa');  
  
DECLARE @tbl_names TABLE ([name] CHAR(3));  
INSERT INTO @tbl_names ([name]) VALUES  
('aaa'),  
('bbb'),  
('ccc'),  
('ddd'),  
('eee');  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
 SELECT * FROM @tbl_names  
 CROSS JOIN (VALUES (1),(2),(3),(4),(5)) AS t(seq) -- as many days as needed  
)  
INSERT INTO @tbl ([day], [name])  
SELECT seq, [name] FROM rs  
EXCEPT  
SELECT [day], [name] FROM @tbl;  
  
-- test  
SELECT * FROM @tbl  
ORDER BY [day], [name];