| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2010-02-22 : 05:50:50
|
| I am using SQLS 2005 SP 3; I have made an output table like this:CREATE TABLE [dbo].[output]( [prac] [char](5) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, [compdate] [datetime] NULL, [visdate] [datetime] NULL, [amrdate] [datetime] NULL, [lastdate] [datetime] NULL, [health_authority] [varchar](20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL) ON [PRIMARY]I have multiple small tables in the same database, each with the same structure as 'output'. I want to be able to insert the contents of each of these tables into output - as a single procedure - but I am struggling to find a way of referring to the tables so that the procedure will identify each and then add its contents to the output table. I have called the source tables testa, testb, testc, testd and teste.In my test database, there are five source tables, each with about 50 rows of data, so I would hope to end up with 'output' containing all 250 rows from the 5 small source tables. In the version I eventually want to run, there will be 500 source tables and about 500 million rows in the output - but I will be more than happy to find a method that works with 5 tables at this point...Can anyone set me on the right road?Thanks.Chris |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-22 : 06:04:49
|
How do you identify the source tables?Why do you want to do that?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-22 : 06:26:27
|
Maybe thisInsert into outputselect * from ( select * from testa union select * from testb....so on)t PBUH |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-02-22 : 07:33:54
|
| No as he said from source table 50 records per table so in output table should be 250 records then in place of union there should be Union all... and no need of building sub query that can be main query just after the insert statementVaibhavVabhav T |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-22 : 07:41:18
|
quote:
Originally posted by vaibhavktiwari83
No as he said from source table 50 records per table so in output table should be 250 records then in place of union there should be Union all... and no need of building sub query that can be main query just after the insert statementVaibhavVabhav T
Maybe he didnt think about the duplicate records scenario.And how will the data make sense practically if the records are duplicated & where have I used sub query?PBUH |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-02-22 : 07:43:53
|
| Yes you are right...That is nt the sub query inner queryinside select * from ()tVabhav T |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-22 : 07:48:56
|
| Yes there is no need.But it will be needed if the OP needs to format the data after selection from the union query & before inserting into the table.PBUH |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-02-23 : 07:44:28
|
| Thank you for the thoughts.The issue is this: we work in medical research and are supplied with anonymised data from an external group. They are considering changing their approach so that they provide data as an SQLS database (rather than as text tables).However, there are 500 practices contributing data, so I am presented with a difficulty: if we take the diagnosis database as an example, there will be 500 separate tables of consultation data to be combined into one large table. At the moment, I have a script that does this on a cursor when the source is in text format, but I can't think of a way to automate this when the source tables already exist as SQL Server tables.If I use a Union All procedure, I have to write this...that is, I have to do it 500 times in order to build the whole procedure, and this will be very time-consuming. - So I was wondering if there is a way to tell SQL Server to append each table automatically to the output table - something equivalent to a cursor or a While loop: it is the automation, or looping, that is causing the difficulty.Any thoughts about this?Thanks.Chris |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-23 : 07:48:37
|
If you want to do it "automatically" then my question is again:How do you identify the source tables?Always the same table names?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-02-25 : 03:17:45
|
| Thanks - yes, the tables always have the same name (in every new release of the database). For example, diagnostic tables will always be med1234, med1235, med1236 etc.I tried developing a method by deriving table names from sysobjects, as follows:Select [name], id AS tableidFrom sysobjectsWhere type = 'U'I tweaked this by adding an auto-incrementing integer to give me a row id that starts at one and increases by one for each new file. This gives me all the info about table names and sequence but I could not see a way of using this to force SQL Server to pick up each table automatically as part of an append procedure.If necessary, I will use the Union All solution since this can be saved and recycled, having been hand-thrown once.Chris |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-02-26 : 07:10:10
|
| Thanks for this interesting link and idea - I will give it a go.Chris |
|
|
|
|
Adding the contents of many tables into one table