You cannot use 4-part naming to reference a column:
There are 2 ways to get around this
- Create synonyms for each table - and use the synonym in your query
- Set an alias for each table in the query and use the alias to reference every column
First - we need to reformat your query so we can read it:
Insert Into [newdb].tychedb.dbo.JOURNAL (
id
, user_id
, second_user_id
, journal_type_id
, start_time
, end_time
, status
, amount
, currency
, counting_error
, message_note
, terminal_id
, ciamanager_id
, master_device_id
)
Select id
, user_id
, second_user_id
, journal_type_id
, start_time
, end_time
, status
, amount
, currency
, counting_error
, (Select Top (1)
message_data
From [olddb].oldversion.dbo.JOURNAL_DETAIL
Where [olddb].oldversion.dbo.JOURNAL_DETAIL.journal_id = [olddb].oldversion.dbo.JOURNAL.id)
, terminal_id
, NULL
, (Select Top (1)
device_id
From [olddb].oldversion.dbo.DEVICE_TRANSACTION
Where id = (Select Top (1)
device_transaction_id
From [olddb].oldversion.dbo.JOURNAL_DETAIL
Where device_transaction_id <> 0
And journal_id = [olddb].oldversion.dbo.JOURNAL.id))
From [olddb].oldversion.dbo.JOURNAL;
Now that we can actually see what is going on - we can immediately see a new problem. You have 3 subqueries using TOP 1 without an order by. This is problematic because we don't know which row SQL will return since it can return any row that meets the requirements.
The other problem is that you are using the same subquery - twice. We can reduce that one and ensure that we get the correct data using CROSS/OUTER APPLY:
Here is an updated version using aliases and outer apply:
Insert Into [newdb].tychedb.dbo.JOURNAL (
id
, user_id
, second_user_id
, journal_type_id
, start_time
, end_time
, status
, amount
, currency
, counting_error
, message_note
, terminal_id
, ciamanager_id
, master_device_id
)
Select j.id
, j.user_id
, j.second_user_id
, j.journal_type_id
, j.start_time
, j.end_time
, j.status
, j.amount
, j.currency
, j.counting_error
, dtl.message_data
, j.terminal_id
, ciamanager_id = Null
, dtl.device_id
From [olddb].oldversion.dbo.JOURNAL As j
Outer Apply (Select Top (1)
jd.message_data
, dt.device_id
From [olddb].oldversion.dbo.JOURNAL_DETAIL As jd
Inner Join [olddb].oldversion.dbo.DEVICE_TRANSACTION As dt On dt.id = jd.device_transaction_id
Where jd.journal_id = j.id
And dt.device_transaction_id <> 0
Order By
<<ordering columns here>> desc
) As dtl;
Replace <> with one or more columns to define the order. Generally a date column in descending order to get the latest version.
I combine the subqueries into a single query - you need to validate the results, but based on what you have shown it seems to be the correct logic. If you don't have related data then message_data and device_id will be null - if those need a default value you can then wrap the columns in COALESCE or ISNULL.
Note: this probably won't be very efficient and could take a long time to run. That is because you are pulling from a linked server and inserting across a linked server.