SQL insert from table and static data

Question

I am creating a stored procedure to insert data into a table from another table. However, all of the data that I want inserted into the new record is not in the other table and at the present time it is not in any table. I am calling this "static" data. I have two tables: one called Resident and one called Lease. If I have a record in the Lease table, I also want an associated record in the Resident table for that Lease.

These are the fields that I need to populate in the Resident table:

    Comp_Name = SMITHJ1234
    Comp_NameLong = SMITH; JOHN
    Comp_Type = Resident
    Comp_Status = Active
    Comp_Source = Import
    Comp_CreatedBy = 1
    Comp_UpdatedBy = 1

These are the fields that will come from the Lease table:

  Lease_primaryresident
  Lease_name

So this is what I have right now for stored procedure:

 INSERT INTO Resident (Comp_Name,Comp_NameLong)
    SELECT Lease_primaryresident,
       Lease_name
    FROM Lease
    WHERE NOT EXISTS (SELECT * FROM Resident
    WHERE Lease.Lease_primaryresident = Resident.comp_name);

How do I get the other items to populate in the Resident record (i.e. comp_type, comp_status, etc.) during this insert?

Any assistance would be greatly appreciated.

Thanks!

mustaccio · Accepted Answer · 2014-12-11 15:20:26Z

1

You can add literal values to the select, obviously:

INSERT INTO Resident (Comp_Name,Comp_NameLong, Comp_Type, Comp_Status)
SELECT 
   Lease_primaryresident,
   Lease_name,
   'Resident',
   'Active'
FROM Lease
WHERE NOT EXISTS (SELECT * FROM Resident
WHERE Lease.Lease_primaryresident = Resident.comp_name);

answered Dec 11, 2014 at 15:20

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL insert from table and static data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related