Joining a table with a previous version

Question

We performed an upgrade of a proprietary program that formerly used Access and now uses SQL. The table schema are exactly the same, but the problem is that the primary key restarted when we switched to SQL. I've imported the old access database into another sql table in the database. The current (SQL based) table is named Archive and the imported Access one is ArchivePreSQL. What I'm trying to do is to create a query that joins those two tables (so I have all records in one query) and adding an "S" to the primary key if it's from the SQL table and an "A" if it's from the imported Access table. Is it possible to do this?

I can get the first chunk of data like this, but I'm unsure how to JOIN it with the second table how I desire.

SELECT 'S' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp FROM Archive

Any help with this is much appreciated!

Answer 1

I think you want to use a UNION. The idea with UNION is that you have two (or more) set of SELECT statements, both of which are expected to return the same exact number of columns with the same types for each, and then they get concatenated together.

So for example:

SELECT 'S' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp FROM Archive
UNION SELECT 'A' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp FROM ArchivePreSQL

Answer 2

Use the UNION keyword

SELECT 'S' + CAST(id AS varchar(MAX)) AS id,
       Account,
       BillingNumber,
       TakenTimestamp
FROM Archive
UNION
SELECT 'A' + CAST(id AS varchar(MAX)) AS id,
       Account,
       BillingNumber,
       TakenTimestamp
FROM ArchivePreSql

Answer 3

If the sets don't intersect, just UNION ALL them:

  SELECT 'S' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp
  FROM Archive
  UNION ALL
  SELECT 'A' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp
  FROM ArchivePreSQL

Answer 4

Yes, you can do that, and it can be accomplished via a Union

SELECT 'S' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp FROM Archive
union
SELECT 'A' + CAST(id AS varchar(MAX)) AS id, Account, BillingNumber, TakenTimestamp FROM ArchivePreSQL

Another alternative, would be to seed your ARCHIVE Identifier with say a number higher than the ArchivePreSQL table's highest identity. Say you have 522 rows in the ArchivePreSQL then seed the ARCHIVE table at say 1000. Then you know that any Identifier below 1000 is in the ArchivePreSql table as opposed to the Archive table.

Also, don't forget that you can temporarily disable the IdentityInsert functionality and perform inserts (including of the identifier) and then re-enable the Identity Insert function (which is what generates the auto-incrementing identifier field) and that will allow you to get rid of the ArchivePreSQL table entirely. If it's a matter of needing to know, you could then couple this with the resetting the Identity Insert seed value so that new items are always above a set amount.

Answer 5

SELECT a.id, 
      'A',
       a.account, 
       a.billingnumber, 
       a.takentimestamp
  FROM ARCHIVE a
UNION ALL
SELECT b.id,
      'B',
       b.account, 
       b.billingnumber, 
       b.takentimestamp
  FROM ARCHIVEPRESQL b
 ORDER BY id

I don't recommend prefixing the ID because then you can't order by it.