Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sebastian11c
Posting Yak Master
129 Posts |
 Posted - 2012-05-04 : 11:48:18
|
| hi there i have just exported an excel document with 100 tables to my sql databasenow i have to insert all the 100 tables in one uniquetable called "UNION"how could i do an automatic sp to insert all my 100 tables exported from excel to a unique table called "UNION"the tables names are 'Table 1$' to 'Table 99$any idea to do all the inserts in my "UNION"because do it manually wil be eternalmany many thanks in advanced |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-04 : 11:55:55
|
Run this:;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<99)SELECT 'SELECT * FROM [Table ' + CAST(n AS VARCHAR) + '$] UNION ALL'FROM n Copy the results and use that as your query. Make sure to remove the last UNION ALL from the last table or else you'll get a syntax error.Edit: And don't name your new table UNION, it's a reserved word and will confuse you and everyone else. |
 |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-05-04 : 12:10:42
|
| awesome . the query worksthanks a lot |
|
|
|
|
|
|
a loop to insert 100 tables