CODEWITHSUNDEEP
sqlsql-serverinner-joinsql-insert
Darlisa Cardiff
Darlisa Cardiff

Reputation: 33

Insert into table with Inner Join

I've been trying to execute insert into a table with an inner join with another table. I tried to use inner join as below but it didn't works. I'm not very sure which is more suitable whether to use INNER JOIN or LEFT JOIN

INSERT INTO ticketChangeSet (Comments, createdBy, createdDateTime)
VALUES ('Test', 'system', CURRENT_TIMESTAMP)
INNER JOIN tickets ON ticketChangeSet.ticket_id = tickets.id
WHERE tickets.id BETWEEN '3' AND '5'

Sample data:

tickets table

id  comment  createdDateTime       closeDateTime          createdBy
2   NULL     2022-07-05 15:36:20   2022-07-05 16:21:03    system
3   NULL     2022-07-05 15:36:20   2022-07-05 16:21:03    system
4   NULL     2022-07-05 15:36:20   2022-07-05 16:21:03    system
5   NULL     2022-07-05 15:36:20   2022-07-05 16:21:03    system

ticketChangeSet table

id  comments               createdBy   createdDateTime        ticket_id
1   Ticket not resolved    system      2022-07-05 15:59:01    2

Basically, I want to insert this value ('Ticket not resolved', 'system', '2022-07-05 15:59:01') into the ticketChangeSet table for ticket_id 3 to 5 from ticket table

Upvotes: 1

Views: 1659

Answers (1)

SMor
SMor

Reputation: 2882

Just select the rows directly from differIssue (or maybe from Tickets - not certain) and supply your constants as the column values.

insert dbo.differIssue (Comments, createdby, dateTime) -- why the strange casing?
select 'Test', 'system', CURRENT_TIMESTAMP 
from dbo.differIssue where Tickets_id between 89 and 100 -- why underscores
;

Notice the statement terminator and the use of schema name - both best practices. I also assumed that the ID column is numeric and removed the string delimiters around those filter values. I left out the join because it did not seem required. Presumably the relationship between differIssue and Tickets is 1:1 so an inner join does nothing useful. But perhaps you need to include rows from Tickets for that range of ID values but which might not exist in differIssue? So try

insert dbo.differIssue (Comments, createdby, dateTime) 
select 'Test', 'system', CURRENT_TIMESTAMP 
from dbo.Tickets where id between 89 and 100 
;

But this all seems highly suspicious. I think there is at least one key column missing from the logic - and perhaps more than one.

Update. Now you've changed the table names, added more columns, and changed the filter. You still use string constants for a numeric column - a bad habit.

insert dbo.ticketChangeSet (...) 
select ... 
from dbo.Tickets as TKT
where not exists (select * from dbo.ticketChangeSet as CHG where CHG.ticket_id = TKT.id)
;

I leave it to you to fill in the missing bits.

Upvotes: 2

Related Questions