April 13, 2012 at 5:29 pm
Hi, I am very new to SQL server managment 08 r2. I have done some things with SQL before in Oracle as well as Access and a few other places. For the life of me i cannot figure out how to append several tables into one.
Say I have a table for January, February, March, ETC to December. I want to append all of these together to have the entire year together.
The tables are imported from a DBF file and then i would have to append them.
Does anyone have an idea on how to accomplish this?
Also this will have to be something that can be run at any time to have the most up to date data possible. So i am assuming that i would need to drop the main table and then import and append each time...right?
April 14, 2012 at 1:38 am
You can use a variant of insert to copy data from one table to another.
It will be something like:
insert into xxx (col1, col2 etc)
select colx, coly from yyy
But I would also ask why the data is fragmented across multiple tables in the first place, surely if it was orginally inserted directly in to your "main table" as you call it you will avoid having to do this. It all depends on why you have it in two places really.
Mike
April 14, 2012 at 3:46 am
From what I understand this might be what you are looking for:
Insert Into tbl_year
Select * From tbl_Jan
Union All
Select * From tbl_Feb
Union All
Select * From tbl_March
Union All
Select * From tbl_April
Union All
Select * From tbl_June
Union All
Select * From tbl_July
Union All
Select * From tbl_Aug
Union All
Select * From tbl_Sept
Union All
Select * From tbl_Oct
Union All
Select * From tbl_Nov
Union All
Select * From tbl_Dec
Make sure that the fields in the tables are the same. If not you'll have to use it like:
Insert Into tbl_year Values(Col1, Col2)
Select Col1, Col2 From tbl_Jan
Union All
Select Col1, Col2 From tbl_Feb
Union All
Select Col1, Col2 From tbl_Mar
April 17, 2012 at 9:10 am
ok thanks.
I tried those different variations and it didnt work. I'll try again and see what it does.
April 17, 2012 at 9:18 am
If you aren't getting the answers you need, it is because you haven't given us what we need to best answer your question. Please provide the DDL (CREATE TABLE statement) for the table9s) involved, sample data (a series of INSERT INTO statements) for the table(s) involved, expected results based on the sample data.
Also, it would be helpful if you also posted to code you have written so far in an effort to solve your problem. It may be that there only needs to be a tweak or minor chnage to what you have already written.
April 17, 2012 at 9:23 am
I know i havent given much detail. Sorry about that. it is mainly because i have tried a ton of different things. I am VERY new to SQL server. I will try to do some with it again today.
I was just hoping for some tips or tricks, which i got.
Thank you
April 17, 2012 at 11:06 am
vinu512 (4/14/2012)
From what I understand this might be what you are looking for:
Insert Into tbl_year
Select * From tbl_Jan
Union All
Select * From tbl_Feb
Union All
Select * From tbl_March
Union All
Select * From tbl_April
Union All
Select * From tbl_June
Union All
Select * From tbl_July
Union All
Select * From tbl_Aug
Union All
Select * From tbl_Sept
Union All
Select * From tbl_Oct
Union All
Select * From tbl_Nov
Union All
Select * From tbl_Dec
Make sure that the fields in the tables are the same. If not you'll have to use it like:
Insert Into tbl_year Values(Col1, Col2)
Select Col1, Col2 From tbl_Jan
Union All
Select Col1, Col2 From tbl_Feb
Union All
Select Col1, Col2 From tbl_Mar
AHH!!! I would strongly caution against this method. I have seen huge performance problems doing this. If you must insert into a table, insert each month separately. However, a UNION ALL would work for you in a view. This way you do not have to truncate your table each time, just reference the view. We did this before we were able to partition our data. We had separate tables for each month of data, but a view that brought them all together.
Jared
CE - Microsoft
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply