Inserting data in one table as selecting from other tables

  • Hi all!

    I have a simple question. I've made a simple database in Access and upsized it in my SQL Server 2005 Express edition Service Pack 4 on my Windows 7 machine, 2GB RAM.

    That database was newbie one, I tried to normalized my data in about 10 tables. In one table called Records I put these columns

    ID int NOT NULL PK auto-increment (I don't recall at this moment how is it called actually)

    book int NULL

    page int NULL

    number int NULL

    Second table is called names with following structure:

    ID int NOT NULL PK auto-increment (see above)

    name1 varchar (70)

    name2 varchar (120)

    Third table is most complex one. Now I realize that I should make it simple. Table name is Parents and the structure is:

    ID int NOT NULL PK auto-increment (see above)

    father_first_name varchar (50)

    father_last_name varchar (50)

    father_occupation varchar (50)

    ...

    All those ID fields are the same, e. t. have the same value, so I want to make one table with all those fields together. I know of routine such as

    SELECT father_first_name + '' + father_last_name + '' + father occupation AS father,

    which is, in my experience with SQL Server, the best solution for putting few fields together in one field.

    Could anyone help me with my problem.

    P. S. I am aware of possible solution

    INSERT INTO...

    AS

    SELECT

    FROM

    but I don't know the whole T-SQL sequence.

    Sincerely yours,

    Fr. Ivan Delic

    Serbia

  • I could not get what you really want but will complete correct sequence for select into..

    Insert into NewTableName(ColumnName1,ColumnName2)

    Select ColumnName1,ColumnName2+ColumnName3 from OldTableName

    If you want Concat of column2 & 3 to be inserted into column2 of new table.

  • Thanks for the quick answer.

    I simply want to copy my data from table2, table3 to table1.

    I have about 25,000 records.

    Fr. Ivan Delic

  • priest Ivan Delic (8/10/2012)


    Thanks for the quick answer.

    I simply want to copy my data from table2, table3 to table1.

    I have about 25,000 records.

    Fr. Ivan Delic

    what are tables 1,2,3?

    what are the columns in each table?

    and how do the columns from table 1/2 relate to table3?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • priest Ivan Delic (8/10/2012)


    Thanks for the quick answer.

    I simply want to copy my data from table2, table3 to table1.

    I have about 25,000 records.

    Fr. Ivan Delic

    Please follow the link in my signature to find out how to post questions in such a way as to make them easy to understand and answer.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks again.

    Table1 is called Records, Table2 Names, Table3 Parents. They could be related by ID field which has the same value for all of them.

    I was recently asked to create only one table and put all of my data in that one table. So how to insert data from other tables in one?

    I hope my answer is clear.

    Sincerely yours,

    Fr. Ivan Delic

    Serbia

  • Hello

    could you please provide some sample data for each of your tables and also what results you are expecting, based on the sample data, once they are all put into one table?

    Phil suggested reading a link in how to provide this in his post above.

    If you are still unclear...pls post back, I am sure we can help, but some idea of the data that you can see would help us help you.

    hope this helps....look forward to your reply.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • priest Ivan Delic (8/9/2012)


    Hi all!

    I have a simple question. I've made a simple database in Access and upsized it in my SQL Server 2005 Express edition Service Pack 4 on my Windows 7 machine, 2GB RAM.

    That database was newbie one, I tried to normalized my data in about 10 tables. In one table called Records I put these columns

    ID int NOT NULL PK auto-increment (I don't recall at this moment how is it called actually)

    book int NULL

    page int NULL

    number int NULL

    Second table is called names with following structure:

    ID int NOT NULL PK auto-increment (see above)

    name1 varchar (70)

    name2 varchar (120)

    Third table is most complex one. Now I realize that I should make it simple. Table name is Parents and the structure is:

    ID int NOT NULL PK auto-increment (see above)

    father_first_name varchar (50)

    father_last_name varchar (50)

    father_occupation varchar (50)

    ...

    All those ID fields are the same, e. t. have the same value, so I want to make one table with all those fields together. I know of routine such as

    SELECT father_first_name + '' + father_last_name + '' + father occupation AS father,

    which is, in my experience with SQL Server, the best solution for putting few fields together in one field.

    Could anyone help me with my problem.

    P. S. I am aware of possible solution

    INSERT INTO...

    AS

    SELECT

    FROM

    but I don't know the whole T-SQL sequence.

    Sincerely yours,

    Fr. Ivan Delic

    Serbia

    First, I wuldn't denormaize like that. Second, I don't believe you could anyway because there is absolutely nothing in those tables to relate them to each other unless you're 100% sure that the ID columns are, in fact, currently 1:1:1.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the answer.

    Sample data in tables are, as follows

    TABLE RECORDS

    ID book page number

    1 1 1 1

    1 1 1 2

    1 1 1 3

    etc. (I have ten records per each page)

    TABLE NAMES

    ID person_name person_sex priest_name priest_title godfather_name

    1 Lara female priest Alexandar Pesic priest Goran Jevtic

    2 Goran male Dusan Kovac archpriest Milena Taylor

    etc.

    TABLE DATES

    ID birth_date baptism_date

    1 13.07.1950 (in format YYYY., MMMM DD. e.t. 1950., July 13.) 14.11.1952 (in format 1952., November 14.)

    etc.

    TABLE PARENTS

    ID father_first_name father_last_name father_nationality father_faith mother_name mother_birthlast_name...

    1 Goran Pesic Serbian Orthodox Bosa Delic

    etc.

    My Result data should be, as excepted in application:

    TABLE BAPTISM

    ID book page number birth_date baptism_date person_name parent priest godfather

    1 1 1 1 1950., July 13. 1952., November 14. Lara, female Goran Pesic and Bosa b. Delic, Serbian Orthodox...

    I started my app for research over data. The reason for denormalizing is that I received application for printing our data in unique paper form, and that app use only one table, with all the data in it.

    Sincerely yours

    Fr. Ivan Delic

    Serbia

  • I think I may now understand what you are trying to do....but there again I may well be completely wrong!

    You have a "printing application" that requires a table as its record source.

    In order to get the details for the print you need to provide some data and which book/page to print it on...each page has 10 records.

    so...I think your TABLE_RECORDS should look like thie

    IDbookpage number

    1111

    2112

    3113

    4114

    5115

    6116

    7117

    8118

    9119

    101110

    11121

    12122

    13123

    etc

    eg...records 11 to 20 will print on page twp...and so on

    for some reason you have split your data into separate tables...and now wish to join it back together by ID..is this correct?

    Have you still got the original table imported from MS Access? ...becasue I have a feeling that may already have all the details you need...ie before you split it out.

    You may need some help in concatenating (joining) columns and formatting dates to meet your print requirements, but before we go there...is this a correct interpretation of your problem?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • How about this?

    IF OBJECT_ID('dbo.RECORDS') IS NOT NULL

    DROP TABLE dbo.RECORDS;

    IF OBJECT_ID('dbo.NAMES') IS NOT NULL

    DROP TABLE dbo.NAMES;

    IF OBJECT_ID('dbo.DATES') IS NOT NULL

    DROP TABLE dbo.DATES;

    IF OBJECT_ID('dbo.PARENTS') IS NOT NULL

    DROP TABLE dbo.PARENTS;

    IF OBJECT_ID('dbo.BAPTISM') IS NOT NULL

    DROP TABLE dbo.BAPTISM;

    --Sample data in tables are, as follows

    CREATE TABLE dbo.RECORDS

    (

    ID int,

    book int,

    page int,

    number int

    );

    insert into dbo.RECORDS values ( 1, 1, 1, 1 );

    insert into dbo.RECORDS values ( 2, 1, 1, 2 );

    insert into dbo.RECORDS values ( 3, 1, 1, 3 );

    insert into dbo.RECORDS values ( 4, 1, 1, 4 );

    insert into dbo.RECORDS values ( 5, 1, 1, 5 );

    insert into dbo.RECORDS values ( 6, 1, 1, 6 );

    insert into dbo.RECORDS values ( 7, 1, 1, 7 );

    insert into dbo.RECORDS values ( 8, 1, 1, 8 );

    insert into dbo.RECORDS values ( 9, 1, 1, 9 );

    insert into dbo.RECORDS values ( 10, 1, 1, 10 );

    /*

    TABLE RECORDS

    ID book page number

    1 1 1 1

    1 1 1 2

    1 1 1 3

    etc. (I have ten records per each page)

    */

    CREATE TABLE dbo.NAMES

    (

    ID int,

    person_name varchar(50),

    person_sex char(6),

    priest_name varchar(50),

    priest_title varchar(30),

    godfather_name varchar(50)

    );

    insert into dbo.NAMES values ( 1, 'Lara', 'female', 'Alexandar Pesic', 'priest', 'Goran Jevtic' );

    insert into dbo.NAMES values ( 2, 'Goran', 'male', 'Dusan Kovac', 'archpriest', 'Milena Taylor' );

    /*

    TABLE NAMES

    ID person_name person_sex priest_name priest_title godfather_name

    1 Lara female priest Alexandar Pesic priest Goran Jevtic

    2 Goran male Dusan Kovac archpriest Milena Taylor

    etc.

    */

    CREATE TABLE dbo.DATES

    (

    ID int,

    birth_date varchar(30),

    baptism_date varchar(30)

    );

    insert into dbo.DATES VALUES ( 1, '1950., July 13.', '1952., November 14.' );

    /*

    TABLE DATES

    ID birth_date baptism_date

    1 13.07.1950 (in format YYYY., MMMM DD. e.t. 1950., July 13.) 14.11.1952 (in format 1952., November 14.)

    etc.

    */

    CREATE TABLE dbo.PARENTS

    (

    ID int,

    father_first_name varchar(30),

    father_last_name varchar(30),

    father_nationality varchar(30),

    father_faith varchar(30),

    mother_name varchar(30),

    mother_birthlast_name varchar(30)

    );

    insert into dbo.PARENTS values ( 1, 'Goran', 'Pesic', 'Serbian', 'Orthodox', 'Bosa', 'Delic' );

    /*

    TABLE PARENTS

    ID father_first_name father_last_name father_nationality father_faith mother_name mother_birthlast_name...

    1 Goran Pesic Serbian Orthodox Bosa Delic

    etc.

    My Result data should be, as excepted in application:

    TABLE BAPTISM

    ID book page number birth_date baptism_date person_name parent priest godfather

    1 1 1 1 1950., July 13. 1952., November 14. Lara, female Goran Pesic and Bosa b. Delic, Serbian Orthodox...

    I started my app for research over data. The reason for denormalizing is that I received application for printing our data in unique paper form, and that app use only one table, with all the data in it.

    */

    CREATE TABLE dbo.BAPTISM

    (

    ID int,

    book int,

    page int,

    number int,

    birth_date varchar(30),

    baptism_date varchar(30),

    person_name varchar(100),

    parent varchar(100),

    priest varchar(100),

    godfather varchar(100),

    );

    insert into dbo.BAPTISM

    ( ID, book, page, number, birth_date, baptism_date, person_name, parent, priest, godfather )

    select

    REC.ID,

    REC.book,

    REC.page,

    REC.number,

    DAT.birth_date,

    DAT.baptism_date,

    person_name = PAR.father_first_name + '' + PAR.father_last_name,

    parent = 'father',

    priest = NAM.priest_name,

    godfather = NAM.godfather_name

    from

    dbo.RECORDS REC

    INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID

    INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID

    INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;

    select * from dbo.BAPTISM;

    I'm not sure where the 'people' column comes from...

    If you prefer, you can create the BAPTISM table without declaring it like this:

    select

    REC.ID,

    REC.book,

    REC.page,

    REC.number,

    DAT.birth_date,

    DAT.baptism_date,

    person_name = PAR.father_first_name + '' + PAR.father_last_name,

    parent = 'father',

    priest = NAM.priest_name,

    godfather = NAM.godfather_name

    into

    dbo.BAPTISM

    from

    dbo.RECORDS REC

    INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID

    INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID

    INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;

  • Yes, you are right want I need to do.

    I started to create MS Access database with those different tables knowing for normalization. My printing app need only one table as data Source.

    Of course I have my original Access DB, but I am little bit familiar with SQL Server when it comes of joining two or more fields in one new field, than I was familiar and know to do it in MS Access.

    My app printing report should look like:

    Book 1

    Page 1

    Number 1

    Birthdate 1980. October 13. in 12:25 (in app script there is way to convert data input into needed format)

    Birthplace Belgrade

    Baptism_date 1981. October 13.

    Baptism_place Belgrade, Saint Petka's Chappel

    Person_name Ivan

    Person_sex Male

    Parent ...

    etc...

    I need T-SQL statement to put together multiple tables and fields into one table.

    Sincerely yours,

    Fr. ivan

    Serbia

  • Hi!

    I forgot one thing. In paper-data there is about 30 books with 100 pages in each of them. On each page there is 10 records, so 1,000 records per book, equally about 30,000 records all.

    In app that was given to me, I have assignment to print only one record on printing form (which is called in Serbia Baptism paper, I don't know in English how to call it). So I need to print one record on one page with all data for that record (book, page, number, dates, name, parents, priest, godfather, about 15 fields). As for ID field, it is PK, Auto-increment and not changeable in app.

    Sincerely yours,

    Fr. Ivan

    Serbia

  • Thanks laurie!

    This is exactly what I wanted to do. Thanks a lot.

    Very grateful, wishing you and yours all the best from our Lord Jesus Christ and Our Father who are in Heaven,

    Fr. Ivan Serbia

  • Thank you.

    One thing - using INNER JOINs assumes the ID exists in all tables. If it doesn't you will lose the whole record. If any table is missing IDs, you can join with an OUTER JOIN instead, to retrieve the data that is in the other tables.

    If you need anything else post it here & I'll try to answer tomorrow.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply