[SOLUTION] INSERT INTO using just created ID

INSERT INTO using just created ID

I was asked to INSERT records into a couple of tables. Here is what I am trying to do.

I need to take [SQL3].MattersQACCTNOTES.QBILLINGNOTES and depending on the length of the input I need to split it up and INSERT it into two different fields in [ADR_LIVE].[dbo].TBM_TEXT.

Once I do this I need that newly created TEXT_ID and INSERT it into [SQL3].MattersQACCTNOTES.Nar_Text_ID.

1) Am I using SUBSTRING correctly? Is there a better way to do the same thing?
2) Once I INSERT into the TBM_TEXT table how do I get that ID and INSERT it into [SQL3].MattersQACCTNOTES.Nar_Text_ID? I would like to use this same query if possible.

Please note that there are two different DB's.

INSERT INTO [ADR_LIVE].[dbo].TBM_TEXT
(TEXT_ID, TXT1, TXT2, LAST_MODIFIED)

SELECT NEWID(),  SUBSTRING(mqa.QBILLINGNOTES, 1, 250), SUBSTRING(mqa.QBILLINGNOTES, 251, 250), GETDATE()
FROM [ADR_LIVE].[dbo].HBM_MATTER hm
LEFT JOIN [SQL3].MattersQACCTNOTES mqa ON hm._ProLaw_FK = mqa.Matters
JOIN [ADR_LIVE].[dbo].TBM_MATTER tm ON hm.MATTER_UNO = tm.MATTER_UNO
JOIN [ADR_LIVE].[dbo].TBM_TEXT tt ON tm.NAR_TEXT_ID = tt.TEXT_ID 
WHERE ((mqa.QBILLINGNOTES IS NOT NULL OR mqa.QBILLINGNOTES <> '') AND tt.txt1 IS NULL)
OR (tt.txt1 + tt.txt2)  <> mqa.QBILLINGNOTES)

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 67

Jim HornMicrosoft SQL Server Data DudeCommented: 2015-10-21

A before and after data mockup would be helpful, as experts here cannot connect to your data source or see the data, so it's hard to conceptualize from just the T-SQL.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

huerita37Author Commented: 2015-10-21

Here is a some data.

BEFORE:
Table mqa:
mqa.Matters Nar_Text_ID QBillingNotes
555DDD23ILI NULL Client will make payments
22DDD555ILI NULL Bill the family
1222233IIILLI NULL The don't accept bills

Table tt does not have any data for the above

AFTER:

table tt:
tt.TEXT_ID tt.txt1
251 Client will make payments
252 Bill the family
253 The don't accept bills

Table mqa:
mqa.Matters Nar_Text_ID QBillingNotes
555DDD23ILI 251 Client will make payments
22DDD555ILI 252 Bill the family
1222233IIILLI 253 The don't accept bills

This question doesn't need to be answered. I found out the fields are not the fields we use.

This doesn't need to be answered.

INSERT INTO using just created ID

Who is Participating?