CODEWITHSUNDEEP
sqlsql-servertemp-tables
L.Newell
L.Newell

Reputation: 112

How To Insert Data Into Temp Table From Different Databases

I am trying to insert data into a temp table from 2 tables from 2 different databases. This is because I need the data from one to create a unique identifier in the other.

This is what I currently have:

-- Create Temp table for data manipulation
IF OBJECT_ID('tempdb..#Staging') IS NOT NULL
    DROP TABLE #Staging

CREATE TABLE #Staging
(
    InitialChildValue nvarchar(4000),
    PropertyNumber nvarchar(10),
    NewYearValue nvarchar(2),
    NewChildValue nvarchar(4000)
)

--Insert initial data into temp table
INSERT INTO #Staging (InitialChildValue, PropertyNumber, NewYearValue, NewChildValue)
    SELECT combo.ChildValue, combo.TABLE_NO, '22' AS YEAR, ''
    FROM 
        (SELECT ChildValue, NULL AS TABLE_NO 
         FROM [DB1].[Version].[Token] 
         WHERE DeletedTransactionKey IS NULL
         UNION
         SELECT NULL AS ChildValue, TABLE_NO 
         FROM [DB2].[dbo].[Notice]
         -- WHERE NOTICE_RUN_CTR = @NoticeRunCTR
        ) combo

But this query returns something like this:

ChildValue TABLE_NO YEAR (empty)
1 22
2 22
3 22
a 22
b 22
c 22

What I'm trying to achieve is something like this:

ChildValue TABLE_NO YEAR (empty)
1 a 22
2 b 22
3 c 22

Further on in my stored procedure, I would then go on to create an ID like "FY1a22" and update the Token table in DB1 based on the initial ChildValue already stored in the temp table.

e.g. loosely written as:

UPDATE DB1.Notice
SET ChildValue = NewChildValue
WHERE ChildValue = #Staging.ChildValue

(I know that is not correct syntax, I'm just shorthand writing it)

There is unfortunately no link between the databases which is why I'm attempting to do it this way.

Any ideas or suggestions would be sorely appreciated.

Upvotes: 0

Views: 2105

Answers (1)

Squirrel
Squirrel

Reputation: 24803

you can use row_number() to generate a sequence number and use it to join the rows from Token with Notice

Seems like there is no relationship on how to link ChildValue with Table_No. Based on the expected result provided, I am assuming it will goes by it's value

SELECT t.ChildValue, n.TABLE_NO, '22' AS YEAR, ''
FROM 
(
    SELECT ChildValue, 
           RN = ROW_NUMBER() OVER (ORDER BY ChildValue)
    FROM   [DB1].[Version].[Token] 
    WHERE DeletedTransactionKey IS NULL
) t
INNER JOIN
(
    SELECT TABLE_NO,
           RN = ROW_NUMBER() OVER (ORDER BY TABLE_NO) 
    FROM [DB2].[dbo].[Notice]
) n
ON t.RN = n.RN

Upvotes: 1

Related Questions