CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
SkyeBoniwell
SkyeBoniwell

Reputation: 7122

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

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

Answers (2)

Venkataraman R
Venkataraman R

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

Alex
Alex

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

Related Questions