Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Inserting data in one table as selecting from other tables Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 8:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
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;

Post #1344194
Posted Monday, August 13, 2012 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 1:04 PM
Points: 45, Visits: 129
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
Post #1344353
Posted Monday, August 13, 2012 12:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 1:04 PM
Points: 45, Visits: 129
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
Post #1344361
Posted Monday, August 13, 2012 12:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 1:04 PM
Points: 45, Visits: 129
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
Post #1344367
Posted Monday, August 13, 2012 1:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
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.
Post #1344391
Posted Tuesday, August 14, 2012 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 1:04 PM
Points: 45, Visits: 129
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.
Post #1344866
Posted Tuesday, August 14, 2012 10:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 5,158, Visits: 12,001
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1344873
Posted Tuesday, August 14, 2012 10:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
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.
Post #1344874
Posted Tuesday, August 14, 2012 2:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 1,917, Visits: 19,585
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
Post #1345030
Posted Wednesday, August 15, 2012 7:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
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.
Post #1345279
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse