How to populate an empty table using INSERT commands

[jthep]

Hi, I'm a newbie at this but how can I populate a table with data from other tables by using INSERT statements? Below is of what I have so far, I have to populate the PERSON_PROFILE table. I've tried using INSERT with SELECT and UPDATE commands which just dont work. I cant input data in one column at a time either since when I created the table, I specified all fields to be NOT NULL.

CREATE TABLE PERSON ( */this table was originally created in assignment 1*/
Person_Id int NOT NULL,
First_Name varchar(20) NOT NULL,
Last_Name varchar(20) NOT NULL,
Middle_Name_Initial char(1) NULL,
Date_Of_Birth DateTime NOT NULL,
Employment_Status char(1) NOT NULL,
CONSTRAINT ck_employmentStats
CHECK(Employment_Status IN ('F', 'P')),
CONSTRAINT pk_person
PRIMARY KEY (Person_Id),
);
/*current assignment*/
CREATE TABLE BADGE (
Badge_Number_Id int NOT NULL,
Issuer_Person int NOT NULL,
Assigned_Person int NOT NULL,
Date_Issued DateTime NOT NULL,
CONSTRAINT pk_badge_number_id
PRIMARY KEY(Badge_Number_Id),
);

BULK INSERT DB2914.dbo.[BADGE]
FROM 'C:\Documents and Settings\Jthep\My Documents\SQL Server Management Studio\Projects\S2914-HW1\BadgeData.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

ALTER TABLE PERSON
ADD Badge_Number int;

UPDATE PERSON
SET Badge_Number = (SELECT Badge_Number_Id
FROM BADGE
WHERE Assigned_Person = Person_Id);

ALTER TABLE BADGE
DROP COLUMN Assigned_Person;


ALTER TABLE PERSON
ADD CONSTRAINT fk_badge_number_id
FOREIGN KEY (Badge_Number) REFERENCES BADGE(Badge_Number_Id);

CREATE TABLE PROFILE (
Profile_Code varchar(4) NOT NULL,
Profile_Description varchar(100) NOT NULL,
CONSTRAINT ck_profile_code
CHECK(Profile_Code IN ('Scty', 'Eval')),
CONSTRAINT pk_profile_code
PRIMARY KEY (Profile_Code),
);

CREATE TABLE PERSON_PROFILE (
Person_Profile_Code varchar (4) NOT NULL,
Person_Profile_Id int NOT NULL,
Profile_Assign_Date DateTime NOT NULL,
CONSTRAINT pk_person_profile
PRIMARY KEY(Person_Profile_Code, Person_Profile_Id),
CONSTRAINT fk_person_profile_code
FOREIGN KEY (Person_Profile_Code) REFERENCES PROFILE (Profile_Code),
CONSTRAINT fk_person_profile_id
FOREIGN KEY (Person_Profile_Id) REFERENCES PERSON (Person_Id),
);

INSERT INTO PROFILE
VALUES('Scty', 'A person authorized to issue organization badges');
INSERT INTO PROFILE
VALUES('Eval', 'A person authorized to perform evaluations');

[azimmer]

Hi, I'm a newbie at this but how can I populate a table with data from other tables by using INSERT statements? Below is of what I have so far, I have to populate the PERSON_PROFILE table. I've tried using INSERT with SELECT and UPDATE commands which just dont work. I cant input data in one column at a time either since when I created the table, I specified all fields to be NOT NULL.

CREATE TABLE PERSON ( */this table was originally created in assignment 1*/
Person_Id int NOT NULL,
First_Name varchar(20) NOT NULL,
Last_Name varchar(20) NOT NULL,
Middle_Name_Initial char(1) NULL,
Date_Of_Birth DateTime NOT NULL,
Employment_Status char(1) NOT NULL,
CONSTRAINT ck_employmentStats
CHECK(Employment_Status IN ('F', 'P')),
CONSTRAINT pk_person
PRIMARY KEY (Person_Id),
);
/*current assignment*/
CREATE TABLE BADGE (
Badge_Number_Id int NOT NULL,
Issuer_Person int NOT NULL,
Assigned_Person int NOT NULL,
Date_Issued DateTime NOT NULL,
CONSTRAINT pk_badge_number_id
PRIMARY KEY(Badge_Number_Id),
);

BULK INSERT DB2914.dbo.[BADGE]
FROM 'C:\Documents and Settings\Jthep\My Documents\SQL Server Management Studio\Projects\S2914-HW1\BadgeData.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

ALTER TABLE PERSON
ADD Badge_Number int;

UPDATE PERSON
SET Badge_Number = (SELECT Badge_Number_Id
FROM BADGE
WHERE Assigned_Person = Person_Id);

ALTER TABLE BADGE
DROP COLUMN Assigned_Person;


ALTER TABLE PERSON
ADD CONSTRAINT fk_badge_number_id
FOREIGN KEY (Badge_Number) REFERENCES BADGE(Badge_Number_Id);

CREATE TABLE PROFILE (
Profile_Code varchar(4) NOT NULL,
Profile_Description varchar(100) NOT NULL,
CONSTRAINT ck_profile_code
CHECK(Profile_Code IN ('Scty', 'Eval')),
CONSTRAINT pk_profile_code
PRIMARY KEY (Profile_Code),
);

CREATE TABLE PERSON_PROFILE (
Person_Profile_Code varchar (4) NOT NULL,
Person_Profile_Id int NOT NULL,
Profile_Assign_Date DateTime NOT NULL,
CONSTRAINT pk_person_profile
PRIMARY KEY(Person_Profile_Code, Person_Profile_Id),
CONSTRAINT fk_person_profile_code
FOREIGN KEY (Person_Profile_Code) REFERENCES PROFILE (Profile_Code),
CONSTRAINT fk_person_profile_id
FOREIGN KEY (Person_Profile_Id) REFERENCES PERSON (Person_Id),
);

INSERT INTO PROFILE
VALUES('Scty', 'A person authorized to issue organization badges');
INSERT INTO PROFILE
VALUES('Eval', 'A person authorized to perform evaluations');

According to the rules of the Forum I shall not give you a complete solution, as this looks very much like an assignment. However, I give you hints:

1. The general format of INSERTing values from one table into another is:

INSERT INTO dest_table
SELECT myColumn1, myColumn2, ...
FROM source_table
 

2. Any column in a SELECT statement (even inside an INSERT!) can be a constant or any other expression.

3. There is a "SELECT ... INTO ..." statement as well; you may or may not want to use it, though.

And a last one: read the help (that of INSERT and SELECT INTO, of course).

[jthep]

Thanks for the reply. I dont really need the full solution, an approach to handling/hints are great. With just solutions, I know I wont get how it works. And I really need to understand how it works. I tried doing the INSERT with SELECT, the only thing is the three different fields (the profile badge code, the id, and date issued) I must put in are in three seperate tables that have about almost all different fields. When I do the INSERT, I have to put in all three fields because if I only try to put in one field or two fields, the SQL will give me an error that states, unable to insert null into table. Thats because I have three fields that does not allow NULLS.

The three tables are:

profile table that contains two fields, badge code which have values Stcy and Eval and a description field of 'people authorized to issue badges' and 'people authorized to perform evaluations' respectively. I will need to use these values for the first column of the new table

Badge table that contains three fields, the badge id (or badge number which has an int value) that was issued, the issuer person which is the person Id from the third table below (Person) and the issued date. the issuer person would have a badge code of 'Stcy' in the new table since they are authorized to issuer badges.

The last table is the Person table which contains fields, person id, last name, first name, birth date, badge id (badge number) etc as listed in the previous post. Any person Id that is not in the Badge Table under the field issuer person will be classified as 'Eval' in the first field of the new table.

Then for the last column, I can set the issued date to any date I want. I was also thinking that since I was provided with the Badge Data to do the bulk insert for the Badge table, if will I be able to use that data to do a bulk insert for the new table called Person_Profile then update the values in the graph. The only thing is that the data contains data for four fields vs the three fields define in the Person_Profile. Is it possible to specify which fields I want to insert to the table from the data? I know at the begining when I made the Badge table, it had four fields, but as there were specific instructions that I had to follow before I got stuck, the current results I had for the Badge table is now three fields.

*EDIT: Sorry I didnt fully read your hints. Question, what you said that the fields in the SELECT statment can be constant. By that do you mean we can hardcode the value into the the statement as in SELECT 'Stcy', person_id, '10/21/2001'? I dont know how that will work since the profile table will return only one row, badge will return many rows since I'm inserting all the id numbers listed in badge. If I added another column in badge name called badge code and set the code to 'Stcy' or I if do that in the person profile and set those person id whos not in badge to 'Eval' and those are to 'Stcy'. Then I can probably do a select statement and delete the fields from other tables after. However, the instructions were to just populate the table using INSERT statements.

Sorry long post.

[azimmer]

Thanks for the reply. I dont really need the full solution, an approach to handling/hints are great. With just solutions, I know I wont get how it works. And I really need to understand how it works. I tried doing the INSERT with SELECT, the only thing is the three different fields (the profile badge code, the id, and date issued) I must put in are in three seperate tables that have about almost all different fields. When I do the INSERT, I have to put in all three fields because if I only try to put in one field or two fields, the SQL will give me an error that states, unable to insert null into table. Thats because I have three fields that does not allow NULLS.

The three tables are:

profile table that contains two fields, badge code which have values Stcy and Eval and a description field of 'people authorized to issue badges' and 'people authorized to perform evaluations' respectively. I will need to use these values for the first column of the new table

Badge table that contains three fields, the badge id (or badge number which has an int value) that was issued, the issuer person which is the person Id from the third table below (Person) and the issued date. the issuer person would have a badge code of 'Stcy' in the new table since they are authorized to issuer badges.

The last table is the Person table which contains fields, person id, last name, first name, birth date, badge id (badge number) etc as listed in the previous post. Any person Id that is not in the Badge Table under the field issuer person will be classified as 'Eval' in the first field of the new table.

Then for the last column, I can set the issued date to any date I want. I was also thinking that since I was provided with the Badge Data to do the bulk insert for the Badge table, if will I be able to use that data to do a bulk insert for the new table called Person_Profile then update the values in the graph. The only thing is that the data contains data for four fields vs the three fields define in the Person_Profile. Is it possible to specify which fields I want to insert to the table from the data? I know at the begining when I made the Badge table, it had four fields, but as there were specific instructions that I had to follow before I got stuck, the current results I had for the Badge table is now three fields.

*EDIT: Sorry I didnt fully read your hints. Question, what you said that the fields in the SELECT statment can be constant. By that do you mean we can hardcode the value into the the statement as in SELECT 'Stcy', person_id, '10/21/2001'? I dont know how that will work since the profile table will return only one row, badge will return many rows since I'm inserting all the id numbers listed in badge. If I added another column in badge name called badge code and set the code to 'Stcy' or I if do that in the person profile and set those person id whos not in badge to 'Eval' and those are to 'Stcy'. Then I can probably do a select statement and delete the fields from other tables after. However, the instructions were to just populate the table using INSERT statements.

Sorry long post.

Hi, I'll try to be short (for now, that is :)). Let me try to rephrase what you seem to want; tell me if it's right or wrong.

You want to fill the PERSON_PROFILE table with INSERTs in such a way that:

• all persons from the PERSON table get inserted (as Person_Profile_ID)
• Profile_Code is 'Stcy' or 'Eval' based on the person's existence/non-existence in the BADGE table (as Assigned_person)
• Profile_Assign_Date is Date_Issued from the BADGE table (it there's a corresponding entry) and today otherwise

[jthep]

Yep, that's basically it.

Person has a relationship with Badge through the badge number.
Person_Profile has a relationship with Profile through the profile code
Person_Profile also has a relationship with Person through Id.

Thanks for trying to help me, it gets confusing switching between C and SQL as I'm taking both concurrently. Too much codes overload.

[azimmer]

Yep, that's basically it.

Person has a relationship with Badge through the badge number.
Person_Profile has a relationship with Profile through the profile code
Person_Profile also has a relationship with Person through Id.

Thanks for trying to help me, it gets confusing switching between C and SQL as I'm taking both concurrently. Too much codes overload.

OK -- but this one is going to be longer.

NB: Person profile is basicly a switching table; these are not normally populated from existing data (because they cannot be). In your case it is possible, though.

Some of the data you need to insert is in the PERSON table, some in the BADGE table, and -- in a somewhat tricky way -- some will be constants (namely the profiles codes).

In the first step, let's join the two relevant tables in a "left outer" manner, so that each line in PERSON get selected along with data in the BADGE table if they exist:

SELECT
    p.Person_Id,
    b.Badge_Number_Id
FROM
    PERSON p LEFT OUTER JOIN BADGE b ON p.Badge_Number = b.Badge_Number
 

(Do run it and see what the result looks like.)

Now, let's throw in the first trick: "Profile code". If b.Badge_Number_Id is NULL it has to be "Eval", "Stcy" otherwise. (When the left outer join cannot find a matching row in BADGE it fills the corresponding fields with NULLs.)

SELECT
    p.Person_Id as Person_Profile_ID,
    case when b.Badge_Number_Id is null then 'Eval' else 'Stcy' end as Profile_Code
FROM
    PERSON p LEFT OUTER JOIN BADGE b ON p.Badge_Number = b.Badge_Number
 

And the last adjustment: "Profile_Assign_Date"; it goes along a similar line (but I coded it differently so that you can see an alternative):

SELECT
    p.Person_Id as Person_Profile_ID,
    case when b.Badge_Number_Id is null then 'Eval' else 'Stcy' end as Profile_Code,
    COALESCE(b.Date_Issued,getdate()) as Profile_Assign_Date
FROM
    PERSON p LEFT OUTER JOIN BADGE b ON p.Badge_Number = b.Badge_Number
 

Hope it helps. From this on, though, I'll have to leave you on your own with the INSERT (but as a hint: it is obviously an INSERT INTO ... SELECT construction).

Ask if you have questions regarding the SELECTs; and please, if I may ask you, read the help for all unknown constructs (e.g. OUTER JOIN, CASE ... WHEN, and COALESCE if you don't know them).

[jthep]

Thanks soo much. I have no problem with the INSERT portion as long as I know how to get the data from the tables. Where can I go to read the articles? I went up to the SQL Server section under Articles, but I only came up with one page with a couple threads.

[azimmer]

Thanks soo much. I have no problem with the INSERT portion as long as I know how to get the data from the tables. Where can I go to read the articles? I went up to the SQL Server section under Articles, but I only came up with one page with a couple threads.

This is a good place to start with helps: http://technet.microsoft.com/en-us/library/aa299742(SQL.80).aspx