August 31, 2010 at 7:34 am
Hello, all.
I receive, routinelly, Excel files that I have to import into my DB. I have to populate the original name of the file and the name of the tab as fields (so later on the source can be tracked). No biggie. A script and Loop structures provide me what i need. However, some times instead of receiving the Excel files I receive a set of tables with the data already imported into them. These tables are named based on the original Excel file and the tab from which the data was imported. I need to bring those tables into my main table and populate the fields indicating the original name and tab; which should be easy. A simple union qry and braking the table name to populate the adequate fields. My problem is i can't get the correct syntax to capture the name of the table in each of the data sets.
This has to be simple; however, my thick skull can't figure it out.
Thx in advance for your suggestions.
LRH
If you don't have a destination,
No matter how fast you travel
You never get there.
September 23, 2010 at 1:22 pm
I think you could do what you're trying to do with some dynamic sql:
declare @sometable nvarchar(255)
declare @sql nvarchar(4000)
DECLARE @ParmDefinition nvarchar(500)
set @ParmDefinition = '@sometable varchar(255)'
set @sometable = 'CUSTOMER'
set @sql = 'SELECT *,''' + LEFT(@sometable,7) +
''' as table_name , ''' + RIGHT(@sometable,6) + ''' as sheet_name ' +
'FROM ' + @sometable
PRINT @sql
EXECUTE sp_executesql @sql, @ParmDefinition, @sometable
September 23, 2010 at 2:01 pm
Thx! I'll give it a try.
LRH
If you don't have a destination,
No matter how fast you travel
You never get there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply