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
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 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
rahul.rahuzz
rahul.rahuzz
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 169
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.
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 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
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: 3447 Visits: 33026
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

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: 8322 Visits: 19470
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.

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.
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 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
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: 3447 Visits: 33026
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

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45028 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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 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
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: 3447 Visits: 33026
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
and remember....every day is a school day

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