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 Thursday, August 09, 2012 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:03 AM
Points: 45, Visits: 128
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
Post #1342810
Posted Friday, August 10, 2012 5:57 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:34 AM
Points: 13, Visits: 136
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.
Post #1343323
Posted Friday, August 10, 2012 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:03 AM
Points: 45, Visits: 128
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
Post #1343404
Posted Friday, August 10, 2012 8:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,645, Visits: 16,619
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 !
__________________________________________________________________
Post #1343432
Posted Friday, August 10, 2012 8:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 4,832, Visits: 11,197
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.



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 #1343439
Posted Saturday, August 11, 2012 1:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:03 AM
Points: 45, Visits: 128
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
Post #1343823
Posted Saturday, August 11, 2012 4:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,645, Visits: 16,619
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 !
__________________________________________________________________
Post #1343829
Posted Sunday, August 12, 2012 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1343888
Posted Sunday, August 12, 2012 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 11:03 AM
Points: 45, Visits: 128
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
Post #1343907
Posted Monday, August 13, 2012 8:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,645, Visits: 16,619
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

ID	book	page 	number
1 1 1 1
2 1 1 2
3 1 1 3
4 1 1 4
5 1 1 5
6 1 1 6
7 1 1 7
8 1 1 8
9 1 1 9
10 1 1 10
11 1 2 1
12 1 2 2
13 1 2 3

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 !
__________________________________________________________________
Post #1344165
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse