Inserting rows from one database table into two different tables in another database

Question

I need to add about 600 records from one database to another one.

The first part inserts from a select like this:

INSERT INTO RelayMapper.dbo.radioSignals(CstarID, StarName, SystemName, StarSystemCount, SuperNova, DateCreated)
SELECT NEWID(), startName, systemName, 1, 1,  getDate() 
FROM AISourceMapper.dbo.radioSignals 
WHERE rangeICW = 5

This is where it gets tricky and I don't know how to do it.

So for each row inserted above, I need to also insert related data into another table.

The NEWID() above would be used to insert a row and then I'd need to insert the starCoordinates as well from AISourceMapper.dbo.radioSignals and it would look something like this:

INSERT INTO RelayMapper.dbo.radioSources(CstarID, starCoordinates,  isVerified)
VALUES('1150C651-5D9A-4C13-9BE7-EF4AZ2549112', 'R.A. 13h 27m, DEC. -47deg, 29m', 1)

starCoordinates is also from the same table and row that I'm SELECTing from(AISourceMapper.dbo.radioSignals)

Is there a way to do something like this?

It sounds like a job for the [`output` clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15). You can add it to the first `insert` to capture the assigned id's. If you need to capture both the source and destination table id's to correlate the data then you can use a `merge` rather than `insert` for the first operation.See [this](https://stackoverflow.com/a/41184461/92546) answer for more information. — HABO, Mar 29 '20 at 16:56
@HABO So I would do the merge on RelayMapper.dbo.radioSignals then an insert on RelayMapper.dbo.radioSources? — SkyeBoniwell, Mar 29 '20 at 20:27
`INSERT` and `UPFATE` also support `OUTPUT` clause, so unless you need to do an actual merge you do not necessarily have to do one. — Alex, Mar 29 '20 at 22:00
@Alex I just need to update two different tables from the same select query and using the same NEWID(). I'm not quite sure how to do that. thanks — SkyeBoniwell, Mar 29 '20 at 23:43
MSDN: https://dba.stackexchange.com/questions/124847/best-way-to-get-last-identity-inserted-in-a-table Stack Overflow: https://dba.stackexchange.com/questions/124847/best-way-to-get-last-identity-inserted-in-a-table , — Alex, Mar 30 '20 at 01:10
@SkyeBoniwell If you use an `insert` to copy data into `radioSignals` then you can `output` any of the columns that you inserted into another table, e.g. a table variable. You can use that to get the new `CstarID` for the `insert` into `radioSources`. If you need to get `radioSources` from the other database then you'll need to be able to match the source database `CstarID` values with the newly assigned ones. Replacing the initial `insert` with a `merge` will let you `output` inserted columns and other source columns, e.g. the original `CstarID` values. That gives you the old to new mapping. — HABO, Mar 30 '20 at 03:23
@Alex The issue with `insert` and `output` is that you can only output columns that you insert. If you need to create a mapping table from source `CstarID` values to destination values assigned by `NewId()` then you need to output the source value which is not being inserted. Using `merge` allows `output` access to source fields that are not inserted, You could even `join` the source tables (_assumption_) and use `merge`/`output` to produce the new `radioSources` rows with the _old_ to _new_ mapping in a table variable or temporary table, then just `insert` them with the new `CstarID`. — HABO, Mar 30 '20 at 03:29
Another option is to copy all data from `AISourceMapper.dbo.radioSignals` into a temp table and at the same time assign GUIDS and then insert from this table into your destination tables. This way you already have all of the mapping data available. — Alex, Mar 30 '20 at 03:59

Alex · Answer 1 · 2020-03-30T10:22:34.617

One option is to copy all data from AISourceMapper.dbo.radioSignals into a temp table and at the same time assign GUIDS and then insert from this table into your destination tables.

SELECT NEWID() AS CstarID, *
INTO #TempTable
FROM AISourceMapper.dbo.radioSignals 
WHERE rangeICW = 5

INSERT INTO RelayMapper.dbo.radioSignals( CstarID, StarName, SystemName, StarSystemCount, SuperNova, DateCreated )
SELECT CstarID, startName, systemName, 1, 1,  getDate() 
FROM #TempTable

INSERT INTO RelayMapper.dbo.radioSources( CstarID, starCoordinates,  isVerified )
SELECT CstarID, starCoordinates,  isVerified
FROM #TempTable

Venkataraman R · Accepted Answer · 2020-03-30T12:23:19.393

1

You can use OUTPUT clause to get the inserted values and then use them to insert into another table.

DECLARE @insertedId TABLE(CStartID UNIQUEIDENTIFIER)

INSERT INTO RelayMapper.dbo.radioSignals(CstarID, StarName, SystemName, StarSystemCount, SuperNova, DateCreated)
OUTPUT inserted.CStarID INTO @insertedId
SELECT NEWID(), startName, systemName, 1, 1,  getDate() 
FROM AISourceMapper.dbo.radioSignals 
WHERE rangeICW = 5;

--with values clause
INSERT INTO RelayMapper.dbo.radioSources(CstarID, starCoordinates,  isVerified)
SELECT CStarId
'R.A. 13h 27m, DEC. -47deg, 29m', 1
FROM @insertedId;

--WITH select clause
INSERT INTO RelayMapper.dbo.radioSources(CstarID, starCoordinates,  isVerified)
SELECT i.CStarId, rs.starCoordinates, 1
FROM AISourceMapper.dbo.radioSignals AS rs
CROSS JOIN  @insertedId AS i
WHERE rs.rangeICW = 5;

edited Mar 30 '20 at 12:23

answered Mar 30 '20 at 04:16

Venkataraman R

12,181
2
31
58

Thanks, will this work on multiple rows at the same time? For example if I needed to insert a few hundred records into RelayMapper.dbo.radioSignals, would it also insert the CStarId values into RelayMapper.dbo.radioSources? Thanks! – SkyeBoniwell Mar 30 '20 at 11:40
1

Yes. It will work as it is a table variable and can hold many rows. I have updated my answer – Venkataraman R Mar 30 '20 at 12:09

Inserting rows from one database table into two different tables in another database

2 Answers2