IF OBJECT_ID('tempdb..#meterKey') IS NOT NULL DROP TABLE #meterKey
SELECT '' AssetKey INTO #meterKey WHERE 1 IS NULL

-- List of meters to export
UNION SELECT 'MAR'
UNION SELECT 'DEB'

IF OBJECT_ID('tempdb..#meter') IS NOT NULL DROP TABLE #meter;SELECT * INTO #meter FROM Meter WHERE AssetKey IN (SELECT * FROM #meterKey)
IF OBJECT_ID('tempdb..#meterLocation') IS NOT NULL DROP TABLE #meterLocation;SELECT * INTO #meterLocation FROM MeterLocation WHERE ID IN (SELECT MeterLocationID FROM #meter)
IF OBJECT_ID('tempdb..#meterLine') IS NOT NULL DROP TABLE #meterLine;SELECT * INTO #meterLine FROM MeterLine WHERE MeterID IN (SELECT ID FROM #meter)
IF OBJECT_ID('tempdb..#line') IS NOT NULL DROP TABLE #line;SELECT * INTO #line FROM Line WHERE ID IN (SELECT LineID FROM #meterLine)
IF OBJECT_ID('tempdb..#lineImpedance') IS NOT NULL DROP TABLE #lineImpedance;SELECT * INTO #lineImpedance FROM LineImpedance WHERE LineID IN (SELECT ID FROM #line)
IF OBJECT_ID('tempdb..#meterLocationLine') IS NOT NULL DROP TABLE #meterLocationLine;SELECT * INTO #meterLocationLine FROM MeterLocationLine WHERE MeterLocationID IN (SELECT ID FROM #meterLocation) AND LineID IN (SELECT ID FROM #line)
IF OBJECT_ID('tempdb..#channel') IS NOT NULL DROP TABLE #channel;SELECT * INTO #channel FROM Channel WHERE MeterID IN (SELECT ID FROM #meter)
IF OBJECT_ID('tempdb..#series') IS NOT NULL DROP TABLE #series;SELECT * INTO #series FROM Series WHERE ChannelID IN (SELECT ID FROM #channel)
IF OBJECT_ID('tempdb..#measurementType') IS NOT NULL DROP TABLE #measurementType;SELECT * INTO #measurementType FROM MeasurementType WHERE ID IN (SELECT MeasurementTypeID FROM #channel)
IF OBJECT_ID('tempdb..#measurementCharacteristic') IS NOT NULL DROP TABLE #measurementCharacteristic;SELECT * INTO #measurementCharacteristic FROM MeasurementCharacteristic WHERE ID IN (SELECT MeasurementCharacteristicID FROM #channel)
IF OBJECT_ID('tempdb..#phase') IS NOT NULL DROP TABLE #phase;SELECT * INTO #phase FROM Phase WHERE ID IN (SELECT PhaseID FROM #channel)
IF OBJECT_ID('tempdb..#seriesType') IS NOT NULL DROP TABLE #seriesType;SELECT * INTO #seriesType FROM SeriesType WHERE ID IN (SELECT SeriesTypeID FROM #series)

SELECT 'CREATE TABLE #meterLocation' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #meter' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #meterLine' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #line' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #lineImpedance' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #meterLocationLine' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #channel' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #series' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #measurementType' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #measurementCharacteristic' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #phase' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'CREATE TABLE #seriesType' UNION ALL
SELECT '(' UNION ALL
SELECT '    ID INT,' UNION ALL
SELECT '    ID2 INT' UNION ALL
SELECT ')' UNION ALL
SELECT 'GO' UNION ALL
SELECT '' UNION ALL
SELECT 'INSERT INTO MeterLocation(AssetKey, Name, Alias, ShortName, Latitude, Longitude, Description) SELECT ' +
    COALESCE('''' + AssetKey + '''', 'NULL') + ',' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Alias + '''', 'NULL') + ',' +
    COALESCE('''' + ShortName + '''', 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), Latitude), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), Longitude), 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + AssetKey + '''', 'NULL') + ' NOT IN (SELECT AssetKey FROM MeterLocation)' + ';' +
    'INSERT INTO #meterLocation(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM MeterLocation WHERE AssetKey = ' + COALESCE('''' + AssetKey + '''', 'NULL') + ')' +
    ')'
FROM #meterLocation
UNION ALL
SELECT 'INSERT INTO Meter(AssetKey, MeterLocationID, Name, Alias, ShortName, Make, Model, TimeZone, Description) VALUES(' +
    COALESCE('''' + AssetKey + '''', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #meterLocation WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterLocationID) + ')', 'NULL') + ',' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Alias + '''', 'NULL') + ',' +
    COALESCE('''' + ShortName + '''', 'NULL') + ',' +
    COALESCE('''' + Make + '''', 'NULL') + ',' +
    COALESCE('''' + Model + '''', 'NULL') + ',' +
    COALESCE('''' + TimeZone + '''', 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') +
    ');' +
    'INSERT INTO #meter(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    'SCOPE_IDENTITY()' +
    ')'
FROM #meter
UNION ALL
SELECT 'INSERT INTO Line(AssetKey, VoltageKV, ThermalRating, Length, Description) SELECT ' +
    COALESCE('''' + AssetKey + '''', 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), VoltageKV), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), ThermalRating), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), Length), 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + AssetKey + '''', 'NULL') + 'NOT IN (SELECT AssetKey FROM Line);' +
    'INSERT INTO #line(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM Line WHERE AssetKey = ' + COALESCE('''' + AssetKey + '''', 'NULL') + ')' +
    ')'
FROM #line
UNION ALL
SELECT 'INSERT INTO MeterLocationLine(MeterLocationID, LineID) SELECT ' +
    COALESCE('(SELECT ID2 FROM #meterLocation WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterLocationID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ' ' +
    'WHERE NOT EXISTS (SELECT * FROM MeterLocationLine WHERE ' +
    'MeterLocationID = ' + COALESCE('(SELECT ID2 FROM #meterLocation WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterLocationID) + ')', 'NULL') + ' AND ' +
    'LineID = ' + COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') +
    ');' +
    'INSERT INTO #meterLocationLine(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM MeterLocationLine WHERE ' +
    'MeterLocationID = ' + COALESCE('(SELECT ID2 FROM #meterLocation WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterLocationID) + ')', 'NULL') + ' AND ' +
    'LineID = ' + COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ')' +
    ')'
FROM #meterLocationLine
UNION ALL
SELECT 'INSERT INTO MeterLine(MeterID, LineID, LineName) VALUES(' +
    COALESCE('(SELECT ID2 FROM #meter WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ',' +
    COALESCE('''' + LineName + '''', 'NULL') +
    ');' +
    'INSERT INTO #meterLine(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    'SCOPE_IDENTITY()' +
    ')'
FROM #meterLine
UNION ALL
SELECT 'INSERT INTO LineImpedance(LineID, R0, X0, R1, X1) SELECT ' +
    COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), R0), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), X0), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), R1), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), X1), 'NULL') + ' ' +
    'WHERE ' + COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ' NOT IN (SELECT LineID FROM LineImpedance)' + ';' +
    'INSERT INTO #lineImpedance(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM LineImpedance WHERE LineID = ' + COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ')' +
    ')'
FROM #lineImpedance
UNION ALL
SELECT 'INSERT INTO MeasurementType(Name, Description) SELECT ' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + Name + '''', 'NULL') + ' NOT IN (SELECT Name FROM MeasurementType);' +
    'INSERT INTO #measurementType(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM MeasurementType WHERE Name = ' + COALESCE('''' + Name + '''', 'NULL') + ')' +
    ')'
FROM #measurementType
UNION ALL
SELECT 'INSERT INTO MeasurementCharacteristic(Name, Description) SELECT ' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + Name + '''', 'NULL') + ' NOT IN (SELECT Name FROM MeasurementCharacteristic);' +
    'INSERT INTO #measurementCharacteristic(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM MeasurementCharacteristic WHERE Name = ' + COALESCE('''' + Name + '''', 'NULL') + ')' +
    ')'
FROM #measurementCharacteristic
UNION ALL
SELECT 'INSERT INTO Phase(Name, Description) SELECT ' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + Name + '''', 'NULL') + ' NOT IN (SELECT Name FROM Phase);' +
    'INSERT INTO #phase(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM Phase WHERE Name = ' + COALESCE('''' + Name + '''', 'NULL') + ')' +
    ')'
FROM #phase
UNION ALL
SELECT 'INSERT INTO SeriesType(Name, Description) SELECT ' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ' ' +
    'WHERE ' + COALESCE('''' + Name + '''', 'NULL') + ' NOT IN (SELECT Name FROM SeriesType);' +
    'INSERT INTO #seriesType(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    '(SELECT ID FROM SeriesType WHERE Name = ' + COALESCE('''' + Name + '''', 'NULL') + ')' +
    ')'
FROM #seriesType
UNION ALL
SELECT 'INSERT INTO Channel(MeterID, LineID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, Name, SamplesPerHour, PerUnitValue, HarmonicGroup, Description, Enabled) VALUES(' +
    COALESCE('(SELECT ID2 FROM #meter WHERE ID = ' + CONVERT(VARCHAR(MAX), MeterID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #line WHERE ID = ' + CONVERT(VARCHAR(MAX), LineID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #measurementType WHERE ID = ' + CONVERT(VARCHAR(MAX), MeasurementTypeID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #measurementCharacteristic WHERE ID = ' + CONVERT(VARCHAR(MAX), MeasurementCharacteristicID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #phase WHERE ID = ' + CONVERT(VARCHAR(MAX), PhaseID) + ')', 'NULL') + ',' +
    COALESCE('''' + Name + '''', 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), SamplesPerHour), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), PerUnitValue), 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), HarmonicGroup), 'NULL') + ',' +
    COALESCE('''' + Description + '''', 'NULL') + ',' +
    COALESCE(CONVERT(VARCHAR(MAX), Enabled), 'NULL') +
    ');' +
    'INSERT INTO #channel(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    'SCOPE_IDENTITY()' +
    ')'
FROM #channel
UNION ALL
SELECT 'INSERT INTO Series(ChannelID, SeriesTypeID, SourceIndexes) VALUES(' +
    COALESCE('(SELECT ID2 FROM #channel WHERE ID = ' + CONVERT(VARCHAR(MAX), ChannelID) + ')', 'NULL') + ',' +
    COALESCE('(SELECT ID2 FROM #seriesType WHERE ID = ' + CONVERT(VARCHAR(MAX), SeriesTypeID) + ')', 'NULL') + ',' +
    COALESCE('''' + SourceIndexes + '''', 'NULL') +
    ');' +
    'INSERT INTO #series(ID, ID2) VALUES(' +
    CONVERT(VARCHAR(MAX), ID) + ',' +
    'SCOPE_IDENTITY()' +
    ')'
FROM #series