CODEWITHSUNDEEP
sqlsql-server
photec
photec

Reputation: 81

Creating new table from columns in multiple tables

In full disclosure, I was kind of thrown into a project this week using SQL and, unfortunately, have very little experience using it up until now outside of very basic querying. I'm going to start studying up, but really could use some help here in the short-term.

In this example, the tables are named Person_PR and Person_CME.

Both tables

Tables:

Person_PR

record_num, person_num, pr_variable1, pr_variable2

Person_CME

record_num, person_num, cme_variable1, cme_varable2

I'd like for the new table Person_new to contain record_num, person_num, cme_variable1, cme_varable2, pr_variable1 and pr_variable2.

New table Person_new:

record_num, person_num, cme_variable1, cme_varable2, pr_variable1, pr_variable2

I have attempted to do this using both insert into and create table as select methods, but am unable to get anything to work properly. I was hoping that someone could get me started in the right direction. Thanks in advance for taking the time to read this and any help you might be able to provide.

Upvotes: 1

Views: 1139

Answers (3)

Brandon
Brandon

Reputation: 702

In SSMS, Object Explorer, you can right click the table, Script Table As, Create To, New Query Editor Window. Create a 3rd query window, copy and paste the fields you want to take from each table, and execute the script to create the new table.

You can also do it with a SELECT statement:

SELECT record_num, person_num, cme_variable1, cme_varable2, pr_variable1, and pr_variable2
INTO Person_new
FROM Person_PR
JOIN Person_CME ON 1=2

From there you can edit through Object Explorer.

If you want to include the DATA from both tables, make it a view rather than trying to maintain a seperate table:

CREATE VIEW Person_new AS
SELECT PR.record_num, person_num, cme_variable1, cme_varable2, pr_variable1, and pr_variable2
FROM Person_PR AS PR
JOIN Person_CME AS CME ON PR.record_num = CME.record_num AND PR.person_num = CME.person_num

You enforce order by including an ORDER BY clause in any select statements, tables are not "ordered"

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

You'll want to use a stored procedure, not a new table:

CREATE PROCEDURE [dbo].GetPersonData

AS
BEGIN

    SET NOCOUNT ON;

    SELECT  cme.record_num ,
            cme.person_num ,
            cme.cme_variable1 ,
            cme.cme_variable2 ,
            pr.prvariable1 ,
            pr.prvariable2
    FROM    Person_CME cme
            INNER JOIN Person_PR pr ON cme.record_num = pr.record_num
                                       AND cme.person_num = pr.person_num

END

You can then use the stored procedure as the record source for your Access form. There is no need to create a new table, which would need to be periodically refreshed and would stored duplicate data. You could also you a view if applicable.

Upvotes: 0

FJT
FJT

Reputation: 2083

The syntax to create a new table in sql server is

SELECT p.record_num
     , p.person_num
     , c.cme_variable1
     , c.cme_variable2
     , p.pr_variable1
     , p.pr_variable2
INTO new_table
FROM Person_PR p
INNER JOIN Person_CME c 
   ON  p.person_num = c.person_num
   AND p.record_num = b.record_num

However, it may make more sense to use a view to present the data you want.

Upvotes: 2

Related Questions