Rename Columns

I need some help with renaming about 115 columns
Here is what I have thus far
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
'Copy_AFS.Col027','LAST-REPRICING-DATE',
'Copy_AFS.Col028','AMORTIZATION-DATE',
'Copy_AFS.Col029','COTERM-RATE',
'Copy_AFS.Col030','COTERM-INDEX',
'Copy_AFS.Col031','AVERAGE-BALANCE',
'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
'Copy_AFS.Col034','INTEREST-INCOME-MTD',
'Copy_AFS.Col035','INTEREST-INCOME-YTD',
'Copy_AFS.Col036','PROCESSING-DATE',
'Copy_AFS.Col037','ACCT-NUM',
'Copy_AFS.Col038','BANK-NUM',
'Copy_AFS.Col039','CORP-NUM',
'Copy_AFS.Col040','BRANCH-NUM',
'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
'Copy_AFS.Col043','CUSTOMER-NUM',
'Copy_AFS.Col044','COUNTERPARTY-INFO',
'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
'Copy_AFS.Col048','PRODUCT-CODE',
'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
'Copy_AFS.Col050','Special Subsystem FTP Code',
'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
'Copy_AFS.Col054','BRANCH-RC',
'Copy_AFS.Col055','SIC-CODE',
'Copy_AFS.Col056','SUBSYSID',
'Copy_AFS.Col057','ACCRUED-INT-BAL',
'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
'Copy_AFS.Col061','VARIABLE-COST',
'Copy_AFS.Col062','FIXED-COST',
'Copy_AFS.Col063','OVERHEAD-COST',
'Copy_AFS.Col064','ORIG-TERM',
'Copy_AFS.Col065','REMAINING-TERM',
'Copy_AFS.Col066','AMT-OVERDUE-30',
'Copy_AFS.Col067','AMT-OVERDUE-60',
'Copy_AFS.Col068','AMT-OVERDUE-90',
'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
'Copy_AFS.Col070','PAST-DUE-DAYS',
'Copy_AFS.Col071','FUNDING-SOURCE',
'Copy_AFS.Col072','Fill-1',
'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
'Copy_AFS.Col075','FTP-GL-ACCOUNT',
'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
'Copy_AFS.Col077','FUNDING-LOOKBACK',
'Copy_AFS.Col078','Filler',
'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
'Copy_AFS.Col081','PREMIUM-TWO',
'Copy_AFS.Col082','PREMIUM-THREE',
'Copy_AFS.Col083','PREMIUM-FOUR',
'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
'Copy_AFS.Col086','FTP-CURRENT-YLD',
'Copy_AFS.Col087','PRODUCT',
'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
'Copy_AFS.Col089','ACCOUNT-STATUS',
'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
'Copy_AFS.Col091','OBLIGATION TYPE',
'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
'Copy_AFS.Col098','TICKLER-TYPE',
'Copy_AFS.Col099','TICKLER-START-DATE',
'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
'Copy_AFS.Col101','TICKLER-DESCRIPTION',
'Copy_AFS.Col102','PEHERNTY (PE908)',
'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
'Copy_AFS.Col105','CHARGE-CD',
'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
'Copy_AFS.Col107','LAST-RENEWAL-DATE',
'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
'Copy_AFS.Col109','AMORT-CODE (RJ440)',
'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
'Copy_AFS.Col111','AMORT-DATE-CALC',
'Copy_AFS.Col112','Filler2',
'Copy_AFS.Col113','BRANCH-ID',
'Copy_AFS.Col114','OFFICER-CODE',
'Copy_AFS.Col115','FILLER2';
But I am getting the following error
"Procedure or function sp_rename has too many arguments specified."
Please HelpHi, Chris
The error message is correct: there are way too many arguments... Also,
it's not a good idea to use hypens in column names; you should replace
them with underlines. You should use something like this:
EXEC sp_rename 'Copy_AFS.Col015','INTEREST_PAYMENT_CODE'
EXEC sp_rename 'Copy_AFS.Col016','INTEREST_COMPOUND_FREQUENCY'
[...]
Razvan|||sp_rename renames one column at a time and you should specify that you are
renaming a column. So you need
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE','COLUMN'
EXEC sp_rename
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY','COLUMN'
EXEC sp_rename
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY','COLUMN'
etc
Tom
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:55EBC93B-282D-4361-8DDB-DA4C02862F5E@.microsoft.com...
>I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>|||Beware of renaming in database production environment.
sp_rename make a complex SQL Script passing through a temp table.
This can make an extremly high volume of data in log file wich can go
down your server.
Do it in a single temp table if all the columns are in the same table !
A +
Chris a écrit :
> I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down your se
rver.
I don't understand what you are saying here. Sp_rename does *not* produce a
large number of log
records. In fact, a test show that the procedure produces less than 10 log r
ecords, regardless of
the number of rows in the table. the procedure only changes a value in one o
f the system tables, so
no need for producing lots of log records.
Run below if you want to verify:
USE master
GO
DROP DATABASE x
GO
CREATE DATABASE x
GO
USE x
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = 'nul'
CREATE TABLE t(c1 int)
GO
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.c1', 'cX', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
GO
--Insert 1000 rows and prove that no more logging:
INSERT INTO t(cX)
SELECT TOP 10000 1 FROM master..sysobjects, master..syscolumns
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.cX', 'cY', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%233smgTNTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Beware of renaming in database production environment.
> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down
your server.
> Do it in a single temp table if all the columns are in the same table !
> A +
> Chris a écrit :
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************sql

removing duplicates

I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:

>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:

> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger