0

I have two tables(Current and Prior) that have all the same columns and are combined through a full outer join in a query. I also have a derived column for each of the respective columns that compares the values of Current and Prior corresponding fields and says whether they match or not. This creates a derived table that has all the Current and Prior Fields as well as a derived comparing column. I need to create an actual table in a database that captures that data. How would I do that?

Kate Kononenko
  • 1
  • 2
    `SELECT ... INTO NewTableName FROM ...`? You could also create the table in advance and just do an `INSERT NewTableName (columns...) SELECT ...` – Siyual Jun 23 '17 at 15:06
  • This sounds like a good idea for a *view* versus a table, though. – S3S Jun 23 '17 at 15:11
  • [Insert into a table you created](https://stackoverflow.com/a/4101761/6167855) and using [select into a new table (created on the fly)](https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008) – S3S Jun 23 '17 at 15:14
  • I would use a proc that does your selects from both tables and the comparing column into a temp table then select from it. Easier to manage and less memory. – briskovich Jun 23 '17 at 15:17
  • what is a proc? also i tried just doing select ... into and it doesnt actually for some reason carry over the values i am trying to insert – Kate Kononenko Jun 23 '17 at 15:44
  • actually select into worked – Kate Kononenko Jun 23 '17 at 16:05
  • a proc is a [procedure](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql), often referred to as a [stored procedure](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure) but it is not needed contrary to @briskovich suggestions since your desire (for better or worse) is to have an actual table. Otherwise, i'd just [create a view](https://learn.microsoft.com/en-us/sql/relational-databases/views/views) from your query which is a *virtual* table and thus doesn't take much space (unless indexed) – S3S Jun 23 '17 at 18:22

1 Answers1

0

This should create a view for you to use, in case the tables are not very large.

CREATE VIEW [dbo].[vw_Compare]
AS
SELECT /* Column list*/
    IIF(A.Col1 IS NULL, 1, 0) AS [CompareCol1],
    IIF(A.Col2 IS NULL, 1, 0) AS [CompareCol2]
FROM A
FULL OUTER JOIN C
    ON A.Col1 = C.Col1

If you wish to create a table:

CREATE TABLE [dbo].[Compare]
(
    [CompareCol1] BIT,
    [CompareCol2] BIT,
    /* Insert Column 1 to N here */
)

INSERT INTO [dbo].[Compare]
(
    [CompareCol1],
    [CompareCol2],
    /* Column list*/
)
SELECT IIF(A.Col1 IS NULL, 1, 0) AS [CompareCol1],
    IIF(A.Col2 IS NULL, 1, 0) AS [CompareCol2],
    /* Column list*/
FROM A
FULL OUTER JOIN C
    ON A.Col1 = C.Col1
Shubham Pandey
  • 919
  • 1
  • 9
  • 19