Inserting data in one table as selecting from other tables

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » T-SQL (SS2K5) » Inserting data in one table as selecting from...

Add to briefcase

22 posts, Page 1 of 3

Inserting data in one table as selecting from other tables

Expand / Collapse

Author

Message

priest Ivan Delic

Posted Thursday, August 09, 2012 10:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:56 AM
Points: 43, Visits: 122

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

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 11:49 PM
Points: 13, Visits: 135

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

priest Ivan Delic

Posted Friday, August 10, 2012 7:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:56 AM
Points: 43, Visits: 122

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

J Livingston SQL

Posted Friday, August 10, 2012 8:22 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 1,456, Visits: 14,268

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

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1343439

priest Ivan Delic

Posted Saturday, August 11, 2012 1:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:56 AM
Points: 43, Visits: 122

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

J Livingston SQL

Posted Saturday, August 11, 2012 4:03 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 1,456, Visits: 14,268

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

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910, Visits: 26,802

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1343888

priest Ivan Delic

Posted Sunday, August 12, 2012 10:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:56 AM
Points: 43, Visits: 122

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

J Livingston SQL

Posted Monday, August 13, 2012 8:15 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 1,456, Visits: 14,268

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

22 posts, Page 1 of 3

Permissions

Expand / Collapse