INION clause to create a table on Server 1

[katrina11]

Hi experts,

I have two tables which I am going union into one table:

1. Claims with acute hospitalization
2. Emergency Room claims

I can use Union to create this common table. Something like that:

select * from acute
Union
select * from ER

However it would be just a select query while I need a table

************************

It causes two questions:

1. Actually Union clause remove duplicates ...and I wonder if it means exact dedup by all fields? I do not need duplicates of the same claim ID while membername duplicates is OK.
How do I write my code to dedup just by claimid?


2. Whenever I create a single table I use the following syntax:

select * into MyTable from DWtable

What would be a similar syntax with Union clause (for creating a table on Server)?

Any help would appreciate,

Thank you,

Katrin

 

[gmmastros]

union will look at all columns of data for duplicates and only remove the row if it finds a duplicate.

What would be a similar syntax with Union clause (for creating a table on Server)?

Select * Into MyTable
From   (
       select *  from acute
       Union
       select *  from   ER
       ) As AnyAlias

Your other question about de-duping by claimid is confusing to me. More accurately, I cannot picture the situation in my head. If you provide some sample data and expected results, it would help us to help you. The data does not need to be "real", but it should be representative of the problem you are trying to solve.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Once you create the table you can insert from select.

insert Into MyTable
select *
from acute

insert Into MyTable
select *
from ER

Simi

 

[katrina11]

Select * Into MyTable
From (
select * from acute
Union
select * from ER
) As AnyAlias

Thank you very much.

One more question....Is it possible to do a calculation
during this table creation?

I need an additional field to name it LOS that is a result of
substruction of Dischdate - AdmitDate.

How do I incorporate it in syntax?

Thank you for support!

 

[gmmastros]

Personally, I would use a computed column for this. For example,
after you create the table, then run this:

Alter Table YourTableName Add LengthOfStay As (DateDiff(Day, AdmitDate, DischDate))

When you do this, SQL Server will not actually store the data. Instead, it will run this calculation every time you use the "LengthOfStay" column. In my opinion, this is better because you do not have to worry about the calculation getting out of synch with the discharge and/or admit day.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katrina11]

Just wonder why after running the following code:

select * into HospitalizationBackup
From (select * from ACUTE
union
select * from ER)

I strarted to get the following error?

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'


What might be wrong? Both tables have the same the same structure (same set of fields...)
I brought my head...

 

[gmmastros]

You need to use an alias for the derived table, like this:

select * into HospitalizationBackup
From  (select *  from ACUTE
       union
       select *  from ER) As Anything

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katrina11]

OMG! Thank you!

 

[katrina11]

One more question. In a process of analysis it was decided
to get 2 more fields in ER table. In other words to change table structure
while Acute table stays unchanged.

Select
Field 1,
Filed2,
FieldADD1,
FieldADD2
Into ER
FROM…

Is there any easy way to make 2 more columns in table ACUTE although there is no criteria for that.
Just to populated columns them with numeric type =0 because as long as tables are a subject to be union they should be the same structure

What would be syntax for the ACUTE table creation to make it match ER table structure?

 

[gmmastros]

When you use union, the fields must match and the field types *should* match. Now, just to be perfectly clear, the tables do not need to have the same columns or even the same order of columns. What must match is the select list from each table.

If you have more columns in the ER table than you have in the ACUTE table, you can just dummy up some data, kinda like this:

select * into HospitalizationBackup
From   (
       select [!]Col1, 
              Col2, 
              Convert(Int, NULL) As Col3, 
              Convert(VarChar(10), NULL) As Col4[/!]  
       from   ACUTE

       union

       select [!]Col1, Col2, Col3, Col4[/!]
       from   ER) As Anything

The import part here is the select list. The list of columns returned from each union query must match. There must be the same number of columns and the data types should match (to remove any ambiguity).

Notice the first query in the union, I put "Convert(Int, NULL) As Col3". When you hard code a null in to a query, there is no data type associated with it. By converting the NULL to int, the data type for the column will be integer but the value will be null.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom