• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

mssql create a new table from existing tables

i have the following tables and i want to create 1 table for these all three tables so in single

select statement like

select * from newtable, i shuold get inforation for the tables which were created using threse below tables

The values should come as distinct

here are the tables

CREATE TABLE [dbo].[info] (
  [MEMID] [nvarchar] (255) NULL,
  [LAST_NAME] [nvarchar] (255) NULL,
  [FIRST_NAME] [nvarchar] (255) NULL,
  [PID] [nvarchar] (255) NULL,
  [PNAME] [nvarchar] (255) NULL,
  [GENDER] [nvarchar] (255) NULL,
) ON [PRIMARY];
GO

CREATE TABLE [dbo].[icd] (
  [ICD] [nvarchar] (255) NULL,
  [Description] [nvarchar] (255) NULL,
) ON [PRIMARY];
GO
CREATE TABLE [dbo].[membercodes] (
  [MEMID] [nvarchar] (255) NULL,
  [ICD9] [nvarchar] (255) NULL,
) ON [PRIMARY];
GO

Open in new window

0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Are you going to tell us how those tables are related or even what output you expect?
0
 
lwadwellCommented:
I am curious as to why you would want to.  You could create a view that will allow the simple select and have the benefit of the data being kept in sync with the underlying tables that it is created from.

Guessing the join columns by the names, something like:
SELECT DISTINCT i.[MEMID],[LAST_NAME],[FIRST_NAME],[PID],[PNAME],[GENDER],icd.[ICD],[Description]
INTO newtable
FROM [dbo].[info] i
JOIN [dbo].[membercodes] mc ON i.[MEMID] = mc.[MEMID]
JOIN [dbo].[icd] icd ON ms.[ICD9] = icd.[ICD]

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
yep, tables are related as:

MEMID - First table

ICD - second table

MEMID - third table
ICD9 - third table

so i think it joins 3 and 1 on memid bassis and 2 and 3 on icd basis
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
lwadwellCommented:
Will I ever learn to check my typing before I submit ...
SELECT DISTINCT i.[MEMID],[LAST_NAME],[FIRST_NAME],[PID],[PNAME],[GENDER],icd.[ICD],[Description]
INTO newtable
FROM [dbo].[info] i
JOIN [dbo].[membercodes] mc ON i.[MEMID] = mc.[MEMID]
JOIN [dbo].[icd] icd ON mc.[ICD9] = icd.[ICD]

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
is that the only way

no other way
0
 
lwadwellCommented:
Some might say SQL is more of an art form than a science - so there can be many ways to write SQL statements to produce the same results ... some more efficient than others.

As I have no test data or entity relationship diagrams to work with ... I honestly do not know.

is there something you are specifically looking for or concerned about?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now