Reputation: 7122
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?
Upvotes: 0
Views: 63
Reputation: 13009
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;
Upvotes: 1
Reputation: 5165
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
Upvotes: 2