Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting data in one table as selecting from other tables


Inserting data in one table as selecting from other tables

Author
Message
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
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;


priest Ivan Delic
priest Ivan Delic
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 130
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
priest Ivan Delic
priest Ivan Delic
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 130
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
priest Ivan Delic
priest Ivan Delic
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 130
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
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
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.
priest Ivan Delic
priest Ivan Delic
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 130
Thanks laurie!

There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).

When I executed T-SQL

Select Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS Parent

Result was NULL

cause of NULL data in Father_Occupation field (column).

I will try, however, to work it out on my own. Thanks for the help.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 19457
priest Ivan Delic (8/14/2012)
Thanks laurie!

There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).

When I executed T-SQL

Select Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS Parent

Result was NULL

cause of NULL data in Father_Occupation field (column).

I will try, however, to work it out on my own. Thanks for the help.


Try

select IsNull(Father_FirstName,'') + ...

which replaces NULLs with empty strings and should make things work.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
If you add a NULL into a string, the result is NULL, so you need to handle the nulls individually like this:

ISNULL(first value, second value) - returns the first non-null value. COALESCE can handle more than 2 values - I don't think you need this though.

So you would use:



Select ISNULL(Father_FirstName + ' ', '') + ISNULL(Father_LastName + ' ', '') + ISNULL(Father_Occupation... AS Parent




You only need to deal with the columns which contain nulls (of course).

Note: The way it's written stops too many spaces being entered in the string.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 33011
priest Ivan Delic (8/13/2012)
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


I make a small observation.....it would appear from what you have said that that the printing app requires you to provide the order of book/page/number etc prior to print....by linking to the RECORDS table on ID ...it seems you only have one method of sort order?

what are you going to do if, for instance you decide to change your mind and print 5 records per page, book - place of baptism - chronological order of dob?

I am not sure that the code will give that you a degree of flexibility going forward...but thats for you to decide.

...or of course, I may have misunderstood :-)

kind regards

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

laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
I read it as: the book/page/entry data refers to the original hand-written books, and the data is just required to print on the certificate so it can be referred-back to the books if required.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search