August 9, 2012 at 10:10 am
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
August 10, 2012 at 5:57 am
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.
August 10, 2012 at 7:52 am
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
August 10, 2012 at 8:22 am
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
August 10, 2012 at 8:29 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 11, 2012 at 1:50 pm
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
August 11, 2012 at 4:03 pm
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
August 12, 2012 at 7:05 am
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
Change is inevitable... Change for the better is not.
August 12, 2012 at 10:12 am
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
August 13, 2012 at 8:15 am
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
August 13, 2012 at 8:50 am
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;
August 13, 2012 at 12:43 pm
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
August 13, 2012 at 12:50 pm
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
August 13, 2012 at 12:55 pm
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
August 13, 2012 at 1:39 pm
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