INSERT INTO using just created ID

huerita37
huerita37 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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.
huerita37

Author

Commented:
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
huerita37

Author

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

Author

Commented:
This doesn't need to be answered.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial