SQL: Copying data from 1 table to 3 others

Question

I've been reading W3 Schools on SQL Insert INTO, which from my understanding creates a backup copy of data into a new table. I have over 300 records to add.

I have 4 tables, one of which is the source I am coping from. 1) Card Access, which is the source 2) pro_Profile, main dest 3) pro_Email, secondary dest 4) pro_Address, third dest

I have no control over this database schemea. It's too much work to rebuilt everything to work with 1 table then the three. Also the data I am adding maybe a duplicate of what exists, so we need to skip those too.

Card access has these fields: CSUID which maps to pro_Profile firstName which maps to pro_Profile lastName which maps to pro_Profile eName which maps to pro_Profile Email Address which maps to pro_Email Contact Phone Number which maps to pro_Address

Once I copy into Pro_Profile it'll need to get the primary key, profileID which is the reference to the profile accross all these tables.

How do I copy all this data first into the pro_Profile so I can get the unique key profileID which is a forgien key to all the other tables?

Here's my code so far:

SELECT
    FirstName,
    LastName,
    eName,
    CSUID
INTO

WHERE NOT EXISTS (
 Select
    firstName,
    LastName 
 From
    pro_Profile 
)

New QUERY which affects 0 rows

INSERT INTO pro_Profile ("firstName","lastName","userName","departmentID","csuID")
SELECT "First Name","Last Name","eName","Department","CSUID"
FROM "Card Access"
WHERE NOT EXISTS (
 Select
    firstName,
    LastName 
 From
    pro_Profile 
)

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

The format for your insert will be

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

So basically you select what you want to insert. You will be able to do it as one statement from all the tables and can add a where clause also. Check out the answers to this question also.

UPDATE: First write your sql query to get the rows you want to insert. So the query you have above:

SELECT "First Name","Last Name","eName","Department","CSUID"
FROM "Card Access"
WHERE NOT EXISTS (
 Select
    firstName,
    LastName 
 From
    pro_Profile 
)

when ran alone should return all the results you will be inserting. What do you get when you just run that?

UPDATE: I think you are misunderstading not exist. Check out this. I think you need something like:

SELECT "First Name","Last Name","eName","Department","CSUID"
FROM "Card Access" ca
WHERE (
  Select count(*)
  From pro_Profile 
  WHERE firstName = ca.firstName
    AND LastName = ca.lastName
  ) = 0

UPDATE: Or alternatively you should be able to do:

SELECT "First Name","Last Name","eName","Department","CSUID"
FROM "Card Access" ca
WHERE NOT EXISTS (
 Select
    firstName,
    LastName 
 From
    pro_Profile 
 WHERE firstName = ca.firstName
   AND LastName = ca.lastName
)

edited May 23 '17 at 11:55

Community

1
1

answered Mar 17 '11 at 19:01

Jacob Schoen

14,034
15
82
102

I just tried that and got the message, (0 row(s) affected), it's posted above. – Snow_Mac Mar 17 '11 at 19:17
1

@jschoen, you need a `WHERE` in your `NOT EXISTS` linking the two tables – Brad Mar 17 '11 at 19:24
@Brad I think what you are saying would work, but I think the count I just put would work also. – Jacob Schoen Mar 17 '11 at 19:33
In my experience, `WHERE NOT EXISTS` is usually considerably faster than any other implementation of an anti-join. It's a good trick to have in your belt. – Mar 17 '11 at 19:38
@Jon You are most likely correct. I have just never used it much, so my first thought was to do a count. I added it to my answer and will definitely keep it in mind for my own future reference. – Jacob Schoen Mar 17 '11 at 19:42
None of the data gets filtered out. Lets say I have my name in both pro_Profile and the card access, I'll still be inserted as a dup. – Snow_Mac Mar 17 '11 at 19:56

SQL: Copying data from 1 table to 3 others

1 Answers1