April 7, 2010 at 4:34 am
Can someone help me with the following, my T-SQL isn’t all that good but I hope the below makes sense. Im also new to this Forum so hope this has been posted in the correct place etc
Note: The below examples have been simplified
I have a table called contact with various columns.
ID (Primary Key)
Name (varchar 50)
Mobile (varchar 50)
Office (varchar 50)
I want to copy all entries from the Mobile column to office column and office column to mobile column. A bug in a front end app has inserted mobile numbers into the office column and office numbers into the mobile column. I know that changing the column names would be the simplest way but this isn’t an option as re-work would be required on the front end application hence want to try and do this within SQL.
I have a procedure that copies entries from the mobile column to a temp table and the same for office.
create table #temptablename1( mobile varchar(50))
insert into # temptablename1 ( mobile)
select mobile
from contact
and another to copy the office numbers
create table #temptablename2( office varchar(50))
insert into # temptablename2 ( office)
select mobile
from contact
What I need help with is copying the data from the temp table back.
There is a really simply query but only copies data from one column to another within the same table but this causes a problem. this will overwrite the entries in the mobile column, which I need to copy to the office column.
UPDATE tablename SET mobile = office
I have tried to use the following to copy from the temp table back into the contact tablr, but this does not work either
INSERT INTO contact( mobile ) SELECT mobile FROM #temptablename1
(if this worked I would want to run INSERT INTO contact( office ) SELECT office FROM #temptablename2)
what am i doing wrong, am i over complicating things.
April 7, 2010 at 4:40 am
This should work. Not tested though
DECLARE@strVariable VARCHAR(50)
UPDATEContact
SET@strVariable = Mobile,
Mobile = Office,
Office = @strVariable
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2010 at 4:49 am
If your table is relatively small, something like this is simplest:
DECLARE @test-2
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Mobile VARCHAR(50) NOT NULL,
Office VARCHAR(50) NOT NULL
);
INSERT @test-2
(ID, Name, Mobile, Office)
SELECT 1, 'Name 1', 'Office 1', 'Mobile 1' UNION ALL
SELECT 2, 'Name 2', 'Office 2', 'Mobile 2' UNION ALL
SELECT 3, 'Name 3', 'Office 3', 'Mobile 3' UNION ALL
SELECT 4, 'Name 4', 'Office 4', 'Mobile 4' UNION ALL
SELECT 5, 'Name 5', 'Office 5', 'Mobile 5';
SELECT *
FROM @test-2;
UPDATE @test-2
SET Mobile = Office,
Office = Mobile;
SELECT *
FROM @test-2;
Otherwise, I would probably use BCP to bulk copy the data out to a file, and re-load it using a format file to switch columns. Be sure to meet the requirements for bulk-logging if you do this, and perform the operation in a maintenance window, when there is no activity on the table.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 4:57 am
Fantastic, that worked a treat Kingston.D . I must say I’m extremely impressed with the prompt reply thank you very much – I will defiantly be using this forum more often.
Inbetween writing this post i notice there was anoher reply however I havent tried as Kingston.D reply done the job, and looks simpler but thanks for the reply as well Paul White NZ
April 7, 2010 at 4:58 am
Paul White NZ (4/7/2010)
If your table is relatively small, something like this is simplest:
DECLARE @test-2
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Mobile VARCHAR(50) NOT NULL,
Office VARCHAR(50) NOT NULL
);
INSERT @test-2
(ID, Name, Mobile, Office)
SELECT 1, 'Name 1', 'Office 1', 'Mobile 1' UNION ALL
SELECT 2, 'Name 2', 'Office 2', 'Mobile 2' UNION ALL
SELECT 3, 'Name 3', 'Office 3', 'Mobile 3' UNION ALL
SELECT 4, 'Name 4', 'Office 4', 'Mobile 4' UNION ALL
SELECT 5, 'Name 5', 'Office 5', 'Mobile 5';
SELECT *
FROM @test-2;
UPDATE @test-2
SET Mobile = Office,
Office = Mobile;
SELECT *
FROM @test-2;
Otherwise, I would probably use BCP to bulk copy the data out to a file, and re-load it using a format file to switch columns. Be sure to meet the requirements for bulk-logging if you do this, and perform the operation in a maintenance window, when there is no activity on the table.
One more instance of SQL Server being different from procedural languages like C, C++. Never thought something like this would work. My eyes were wide open for at least 5 seconds as to what i am seeing.:w00t:
Thanks for opening my eyes, Paul:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2010 at 5:09 am
Kingston Dhasian (4/7/2010)
My eyes were wide open for at least 5 seconds as to what i am seeing.:w00t:
Ah, then my work here is done :laugh:
Thanks for opening my eyes, Paul:-)
No worries - it is kinda counter-intuitive, but also kinda neat too :satisfied:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 5:11 am
Mr_Bacon (4/7/2010)
In between writing this post I notice there was anoher reply however I havent tried as Kingston.D reply done the job, and looks simpler but thanks for the reply as well Paul White NZ
No worries 🙂
You'll often get two or three good suggestions in response to an interesting question on here.
In this case, two (almost) the same, but hey.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 6:58 am
Paul White NZ (4/7/2010)
If your table is relatively small, something like this is simplest:
DECLARE @test-2
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Mobile VARCHAR(50) NOT NULL,
Office VARCHAR(50) NOT NULL
);
INSERT @test-2
(ID, Name, Mobile, Office)
SELECT 1, 'Name 1', 'Office 1', 'Mobile 1' UNION ALL
SELECT 2, 'Name 2', 'Office 2', 'Mobile 2' UNION ALL
SELECT 3, 'Name 3', 'Office 3', 'Mobile 3' UNION ALL
SELECT 4, 'Name 4', 'Office 4', 'Mobile 4' UNION ALL
SELECT 5, 'Name 5', 'Office 5', 'Mobile 5';
SELECT *
FROM @test-2;
UPDATE @test-2
SET Mobile = Office,
Office = Mobile;
SELECT *
FROM @test-2;
i know CASE works with values but can we use here CASE statement.
I am just curious:-)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 7, 2010 at 4:21 pm
Paul White NZ (4/7/2010)
If your table is relatively small, something like this is simplest:
DECLARE @test-2
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Mobile VARCHAR(50) NOT NULL,
Office VARCHAR(50) NOT NULL
);
INSERT @test-2
(ID, Name, Mobile, Office)
SELECT 1, 'Name 1', 'Office 1', 'Mobile 1' UNION ALL
SELECT 2, 'Name 2', 'Office 2', 'Mobile 2' UNION ALL
SELECT 3, 'Name 3', 'Office 3', 'Mobile 3' UNION ALL
SELECT 4, 'Name 4', 'Office 4', 'Mobile 4' UNION ALL
SELECT 5, 'Name 5', 'Office 5', 'Mobile 5';
SELECT *
FROM @test-2;
UPDATE @test-2
SET Mobile = Office,
Office = Mobile;
SELECT *
FROM @test-2;
Otherwise, I would probably use BCP to bulk copy the data out to a file, and re-load it using a format file to switch columns. Be sure to meet the requirements for bulk-logging if you do this, and perform the operation in a maintenance window, when there is no activity on the table.
Why would you only use this if it were a small table? What would be the downside (other than a bloated transaction log, I guess) of doing it this way on even a large table?
Rob Schripsema
Propack, Inc.
April 7, 2010 at 6:41 pm
Rob Schripsema (4/7/2010)
Why would you only use this if it were a small table? What would be the downside (other than a bloated transaction log, I guess) of doing it this way on even a large table?
Logging, performance and concurrency mostly.
Fully logged modification operations can be pretty slow.
The operation will also likely acquire an exclusive table lock, which would hurt concurrency.
Worst case, it takes an hour to run, fills the log, and rolls back! 🙂
It depends on the exact circumstances, of course...
edit: Just thought of another thing...
If the columns are variable-length, and one is larger than the other, the data page might have to split to accommodate the changes - or a ROW_OVERFLOW allocation unit would be created. Either would be horrible for logging, performance, and the internal structure of the finished table. (Fragmentation/ROW_OVERFLOW is bad for ordered index scan performance).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply