How do I swap column info from two tables?

[ndp]

Hi,
I am new to sql programming. I have created a table tbl1 with 50 rows. I created another table tbl2 with 50 rows. Both tables have same structure but different set of data. I want to swap the column LastName from the two tables. This is I am doing for creating sample names. Is there any simple way to do it?

I tried insert statement like this...
insert into #tbl1(lname)
select lname from #tbl2

which gives me error. Basically, I just want to swap the lname from two tables so that I can produce some fake names. I also tried with update statement, but it puts only same name in all rows.

Can somebody suggest a way to do it? That will be a great help!

Thanks in advance!
ndp

 

[ClaireHCM]

What kinda error message you got? the syntax looks right

 

[ndp]

The table name is different in my sql.

I get this error message...
Cannot insert the value NULL into column 'Overlapping_Answers_Flag', table 'tempdb.dbo.#st11_______________________________________________________________________________________________________________000000179C41'; column does not allow nulls. INSERT fails.
The statement has been terminated.

 

[SQLBill]

Just to make sure we are understanding you...

You have data like this:

Table1 Table2
LName LName
Smith Franklin
Doe Washington

And you want to run a script that will create this:

Table1 Table2
LName LName
Franklin Smith
Washington Doe

Is that correct?

-SQLBill

 

[ndp]

Yes! Exactly like that. So that I won't have original full name in my tables.

Thanks so much for replying!
ndp

 

[ndp]

Hi SQLBill,
I am still waiting for your suggestion!

ndp

 

[SQLBill]

I'm working on it, but one thing is that your INSERT won't 'swap' the last names. It will only add the names from table2 into table1. So using my example, table1 will now look like:

Table1
Smith
Doe
Franklin
Washington

and there won't be a change to Table2.

I think if you really want to swap (move) the last name from each table to the other (table1 to table2 and table2 to table1), we are going to have to use a temporary table.

CREATE #mytable
 (
  t1_lastname VARCHAR(20),
  t2_lastname VARCHAR(20)
 )

Then copy the data from the tables into the temp table.

INSERT #mytable
SELECT t1.lastname, t2.lastname
FROM table1 t1, table2 t2

Then drop the columns in the original tables

ALTER TABLE table1 DROP COLUMN lastname
ALTER TABLE table2 DROP COLUMN lastname
[/COED]

Then recreate the columns

[CODE]
ALTER TABLE table1 ADD COLUMN lastname
ALTER TABLE table2 ADD COLUMN lastname

Then add the data in from the temp table

INSERT INTO table1 (lastname)
SELECT t2_lastname FROM #mytable
GO
INSERT INTO table2 (lastname)
SELECT t1_lastname FROM #mytable

DISCLAIMER: I haven't tested that, but it should work to swap the data from one table to another. Someone else might have a better way of doing it, but that's what I came up with.

Good luck!

-SQLBill

 

[ndp]

Thanks SQLBoll,
I was also trying something which worked too!
I created another temp table3 which is copy of table1.
I added identity column iNum to each of the three tables starting from 1.
I updated table1 by this statement..
update table1
set table1.lname = t.lname
from table2 t where table1.iNum = t.iNum

Again same way did with table2 and table3

Thanks for your help!
ndp

 

[SSesham]

in the last solution u found for yourself i have a comment..make sure that you sort the data in each table before you proceed with indentity insert..