| Author |
Topic |
|
truthseeker
Starting Member
15 Posts |
 Posted - 2010-09-24 : 11:45:33
|
| I have a temporary table named #MyTest and one of it's column is named [Customer Name].How do I rename [Customer Name] to [Name]?I tried:EXEC sp_rename 'dbo.#MyTest.[Customer Name]', '[Name]', 'COLUMN'But I keep getting this error:Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.Can somebody help me?Btw, this table was created from an OPENROWSET export of an excel file.Thanks. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-24 : 11:48:03
|
Why would you ever want to do that to a temp table?Do you only want to *display* a different label?IF so then that's easySELECT .... , [Customer Name] AS [Name] ....FROM #MyTest Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
truthseeker
Starting Member
15 Posts |
Posted - 2010-09-24 : 12:31:32
|
Well, when I imported the Excel file to SQL Server 2005 using OPENROWSET with HDR=Yes, the first row of the column which contained a non-needed text became the name of the first column. The rest of the columns were named F2, F3, F4 etc.So I used OPENROWSET with HDR=No and the columns were named F1, F2, F3, F4, etc. which was fine.I then just deleted the first row which contained the non-needed text.However, I noticed that some numeric data in the columns became NULL in SQL Server. I googled around and found that I had to add "IMEX=1" so that the numeric values would be imported to SQL Server correctly. But adding "IMEX=1" threw back the non-needed text as a column name in spite of the fact that HDR=No.That's why I need to rename the column back to F1. But seeing if this was possible, then I'd rather rename all the columns so that it's easier to see the columns matching up with the columns I have from the non-temporary tables.quote:
Originally posted by Transact Charlie
Why would you ever want to do that to a temp table?Do you only want to *display* a different label?IF so then that's easySELECT .... , [Customer Name] AS [Name] ....FROM #MyTest Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 12:46:08
|
| SELECT Col1Name AS [F1], Col2Name AS [F2], ...INTO #NewTempFROM #OldTemp??Bit tedious though .... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-09-24 : 12:53:50
|
Try this:USE TEMPDBGOEXEC sp_rename 'dbo.#MyTest.[Customer Name]', '[Name]', 'COLUMN' |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-09-24 : 13:03:16
|
quote:
Originally posted by X002548
I would..Create a staging table..making all columns varcharsave the excel file as delimitedbcp the dataperform audits on my dataThe move the staged data to it's appropriate base tableBrett8-)
That's an option. But, that seems like a whole lot of extra IO, when you could just use SSIS to read, audit and write the data in one shot. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
truthseeker
Starting Member
15 Posts |
Posted - 2010-09-24 : 13:06:44
|
quote:
Originally posted by Lamprey
Try this:USE TEMPDBGOEXEC sp_rename 'dbo.#MyTest.[Customer Name]', '[Name]', 'COLUMN'
This worked perfectly!!!Thank you very much!  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-09-24 : 13:26:34
|
quote:
Originally posted by X002548
really?HOW Do you think SSIS, DTS or what ever over abused piece of garbage "ETL" Tools work when they "Transform" the data?Brett8-)
It's called a "Pipeline", perhaps you should look up how this "abused piece of garbage" works so you can talk intelligently about it. But, if you think reading and writing data mutiple times is more effecient than doing it once, then more power to you. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-09-24 : 16:48:20
|
quote:
Originally posted by Lampreythat seems like a whole lot of extra IO, when you could just use SSIS to read, audit and write the data in one shot.
Avoid using SSIS (or any other ETL tool) for anything other than loading data. If you are smart, and follow best practices, you will do as Brett advised and do all your auditing of data after it hits the staging tables.ETL tools are notoriously difficult to debug. You end up spreading your business logic across several different tools, technologies, and coding languages, and I have one client who is still stuck on SQL Server 2000 because they have hundreds of DTS packages with complex business logic, data validation, and data transformations built into them, and there is no way to easily convert them all to SSIS packages. Meanwhile, none of the stored procedures I've ever created for ETL have ever suffered from backward compatibility issues...________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
How to rename a column from a temporary table