TSQL procedural issue and advice

Question

I am looking for advice on the best way to accomplish the following

I have a table in SQL Server that holds downloaded data from an external system. I need to use it to update another database. Some records will be inserts and others will be updates. There is a comment table and a main table to insert/update. Comments are linked by an ID created in the comments table and stored in a column of the main table record. (one to one relationship)

So insert into comment table and get a scope_identity return value and then use that as part of the insert statement for the main table.

The updates get the comment ID from the record in the main table and then update the comment table where necessary and also the main table where necessary

EG Table has 5 records

Get first record
If exists in database
get commentID column from comment table and update comment and main table
If not exists
insert into comment table and return comment ID and insert the record into the main table with that comment ID
get the next record

I'm struggling to figure out how to best do this in SQL Server. Can't find the right combination of cursor, while loops, stored procedure etc. Haven't done much by way of procedural work in SQL Server.

Any advice/help is greatly appreciated

Thanks

Habo. I appreciate the feedback. I do struggle to write a clear concise question. The linked page provides good advice. Hope this script below helps clarify.

Thanks again.

USE TEMPDB

--TABLE TO HOLD JOB RECORDS
create table tbl_jobs(
jobnumber varchar(16) primary key clustered,
jobdesc varchar(50),
commentID int
)
GO

INSERT INTO tbl_jobs VALUES ('Job1','Desc1', '1')
INSERT INTO tbl_jobs VALUES ('Job2','Desc2', '2') 
INSERT INTO tbl_jobs VALUES ('Job3','Desc3', '3') 

--TABLE TO HOLD JOB RECORD COMMENTS
create table tbl_jobComments(
commentID INT IDENTITY(1,1) NOT NULL,
comment text
)
GO
Insert into tbl_jobComments VALUES ('Comment1')
Insert into tbl_jobComments VALUES ('Comment2')
Insert into tbl_jobComments VALUES ('Comment3')


--TABLE TO HOLD RECORDS DOWNLOADED FROM EXTERNAL SYSTEM
create table tbl_updates(
jobnumber varchar(16) primary key clustered,
jobdesc varchar(50),
comment text
)
GO

INSERT INTO tbl_updates VALUES ('Job1','Desc1Modified', 'Comment1')
INSERT INTO tbl_updates VALUES ('Job2','Desc2', 'Comment2') 
INSERT INTO tbl_updates VALUES ('Job3','Desc3Modified', 'Comment3')  
INSERT INTO tbl_updates VALUES ('Job4','Desc4', 'Comment4') 
GO

--OUTPUT FROM tbl_Jobs
+-----------+---------+-----------+
| jobnumber | jobdesc | commentID |
+-----------+---------+-----------+
| Job1      | Desc1   |         1 |
| Job2      | Desc2   |         2 |
| Job3      | Desc3   |         3 |
+-----------+---------+-----------+

--OUTPUT FROM tbl_JobComments
+-----------+----------+
| commentID | comment  |
+-----------+----------+
|         1 | Comment1 |
|         2 | Comment2 |
|         3 | Comment3 |
+-----------+----------+

--OUTPUT FROM tbl_updates
+-----------+---------------+-----------+
| jobnumber |    jobdesc    | comment   |
+-----------+---------------+-----------+
| Job1      | Desc1Modified | Comment1  |
| Job2      | Desc2         | Comment2a |
| Job3      | Desc3Modified | Comment3  |
| Job4      | Desc4         | Comment4  |
+-----------+---------------+-----------+


--DESIRED RESULTS tbl_jobs
+-----------+-----------------+-----------+
| jobnumber | jobdesc         | commentID |
+-----------+-----------------+-----------+
| Job1      | Desc1Modified   |         1 |
| Job2      | Desc2           |         2 |
| Job3      | Desc3Modified   |         3 |
| Job4      | Desc4           |         4 |
+-----------+---------+-------------------+

--DESIRED RESULTS tbl_jobs_comments
+-----------+-----------+
| commentID | comment   |
+-----------+-----------+
|         1 | Comment1  |
|         2 | Comment2a |
|         3 | Comment3  |
|         4 | Comment4  |
+-----------+-----------+

Why not do an update on the main table and the comment via use of a join when the objects match, then do an INSERT INTO on the rest (if it does not)? — Robert Sievers, Jul 11 '16 at 17:37
You might not need to be loopy. You can use an [`OUTPUT`](https://msdn.microsoft.com/en-us/library/ms177564.aspx) clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. `OUTPUT` can be used with `INSERT`, `UPDATE`, `DELETE` and `MERGE` and provides access to both _before_ and _after_ values in the case of `UPDATE`. A tool well worth having in your pocket. — HABO, Jul 11 '16 at 18:01
Thanks for the answer, Habo, your answer has gotten me closer. I can do a merge on the maintable from the updatetable and I get the new records and the updates. I can't yet figure out how I can insert into commenttable, output the commentID and use it in the INSERT portion of the merge statement. Is this even possible or do I have to do this in multiple statements? — Gork, Jul 12 '16 at 15:28
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. It's still rather vague to me, but I get the feeling that it comes down to a couple of statements, e.g. an `INSERT` for the `Comments` table with an `OUTPUT` clause and perhaps a `MERGE` from joined tables to handle the `Main` table. — HABO, Jul 12 '16 at 15:59
Do not consider doing such a thing in a loop or cursor. Your model is odd, normally you would insert to the main table first (and you should have a PK FK relationship to comments) then the child table of comments. If you have a one to many relationship, then you definitely need to do that. Right now you are wiping out any relationship to any original comments which is generally a data integrity nightmare. — HLGEM, Jul 12 '16 at 20:45
HLGEM, The design is not mine. it is an accounting system and I have no control over the tables. I can only insert a comment and link it to the job record with the id produced by the insert in the comment table. The relationship between comment and job is one to one as noted in my original post. — Gork, Jul 12 '16 at 20:57

score 0 · Answer 1 · answered Jul 11 '16 at 19:53

0

You can break this into 2 statements, an update and an insert query

(This assumes there is only 1 comment per ID)

UPDATE maintable
SET Comment=upd.comment
FROM maintable mt
JOIN updatestable upd
ON mt.id=upd.id

then insert what is missing:

INSERT INTO maintable (id,comment)
SELECT id, comment
FROM updatestable
WHERE id NOT IN (SELECT id FROM maintable)

answered Jul 11 '16 at 19:53

SeanC

15,695
5
45
66

Seanc, my updates table does not have the Comment ID, it has the comment only. The requirement is insert new into commenttable and return commentID (scope_identity) insert new into maintable including commentID Update existing in commenttable and maintable – Gork Jul 12 '16 at 15:29

TSQL procedural issue and advice

1 Answers1