Copying columns from one table to other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying columns from one table to other

Author

Topic

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 11:35:09

Hello to all,
this is my first post on this forum, and I hope you'll be able to solve my problem. I'm working in industrial field and only from time to time I have to work with SQL. My problem is the following:
I have two tables. Both tables have same number of rows but different number of columns. Table 1 has 5, and table2 has three columns.
I need SQL statement to copy two columns A and B from table 1 to table 2 with condition, for example table2.C <> 2?

I don't know if I was clear enough. Basically I need to copy data from two columns of one table to other. Tables have no common data which I can use to join tables.
I tried something like this:

update table2 set (A,B)=(select A,B from table 1) where table2.c <>2
but don't know how to use syntax properly...

Thanks in advance

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-30 : 12:06:28

[CODE]
INSERT INTO table2 (col1, col2)
SELECT col1
, col2
FROM table1
WHERE col1 <> 2
[/CODE]

George
<3Engaged!

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 12:29:37

Thank you for your quick response. However that didn't help me to solve the problem. I tried what you suggested (application to my case):
INSERT INTO tabela_b (A, B)
SELECT A
, B
FROM tabela_a
WHERE tabela_b.C <> 2

But I'm getting error message:
"Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tabela_b.C" could not be bound."
I'm trying to execute SQL statement under SQL Server 2005.
I also want you to know that other table which I'm trying to copy to is not empty, it is rather refresh (overwritten) by new data from tabela_a...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-30 : 13:16:11

You can't reference tabela_b in the SELECT statement since it isn't in the FROM and you don't have any JOINs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 13:40:36

Yes, I realize what is the problem.
I tried to join these tables, but problem is "ON" clause. In both tables I have two columns with same name, but data is different. Basically I don't know how to join to avoid problem appearing NxM; records where is N numbers of items in first and M in second table...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-30 : 13:45:44

INSERT INTO tabela_b (A, B)
SELECT A, B
FROM tabela_a a
INNER JOIN tabela_b b
ON a.SomeColumn = b.SomeColumn
WHERE b.C <> 2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 14:03:19

Tkizer, I always thought that inner join can be used to join two table that has same attributes (columns that are keys). Now I have two tables with different column number, but both have two columns with same name and different data.Let's say that table_b has previous values for A and B, while table_a has current valuse. Would it be Ok to use inner join on tabela_a.A = tabela_b.A.
I'm not sure this is the right way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-30 : 14:44:18

Please post a data example of what you mean to make this more clear.

Joins do not have to be on key columns. They can even be on columns with different names.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 15:04:54

I'll make example on two very simple tables.
TableA:Columns: A,B;
TableB: Columns A,B,C
Both tables are already filled with data.
Both tables have same number of rows.
For simplicity, let's say that TableB has column C defined as bit.
I need to make SQl statement to copy data from TableA to TableB only if C is different from 1.
For example,
TableA:
A B
1 5
2 6
7 8

TableB
A B C
1 8 0
2 8 1
5 9 0

Result of operation would be:

1 5 0
2 8 1
7 8 0

I think Insert into statement will not do the job, because insert command will insert new values at the end of table, I need updat (overwrite)....

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-30 : 15:25:43

Books online has sample code, just look at UPDATE (Transact-SQL).

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 16:01:06

I already did that, but informations there was not sufficient to solve this. Like I said, I'm not IT guy, but work in field of industrial automation. From time to time I need to work with software that is SQL Server based, abd similar issues appear.
I hope tkizer will help me to solve this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-30 : 16:05:24

rmiao,

This is the New to SQL Server forum. Please provide more information in your posts in this forum.

Micik,

I can't figure out what you want even with the sample data provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Micik
Starting Member

9 Posts

Posted - 2007-12-30 : 16:53:54

Unfortunately, I don't know how to explain this exactly.
I want to copy all data from two columns (A and B) in TableA to TableB. Before overwriting, data in column TableB.C must be checked to be different from some value (let's say 1).
I gave simple example. These data is potential candidates for placing in table B:
A B
1 5
2 6
7 8
and since only first and last row in Table B, column C has value different from 1, only first and last row from table A will be copied to table B and thus overwrite data in TableB. So result is:
Table B
1 5 0
2 8 1
7 8 0
So Table B is now updated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-30 : 19:25:10

Here's the best I could come up with as I still am a bit confused:

UPDATE t1
SET B = t2.B
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.A = t2.A
WHERE t1.C <> 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 01:25:39

quote:
Originally posted by Micik

I'll make example on two very simple tables.
TableA:Columns: A,B;
TableB: Columns A,B,C
Both tables are already filled with data.
Both tables have same number of rows.
For simplicity, let's say that TableB has column C defined as bit.
I need to make SQl statement to copy data from TableA to TableB only if C is different from 1.
For example,
TableA:
A B
1 5
2 6
7 8

TableB
A B C
1 8 0
2 8 1
5 9 0

Result of operation would be:

1 5 0
2 8 1
7 8 0

I think Insert into statement will not do the job, because insert command will insert new values at the end of table, I need updat (overwrite)....

I think this is what you are looking for:-

UPDATE t2
SET t2.A=t1.A,
    t2.B=t1.B
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY A,B) AS 'RowNo',
A,B
FROM TableA
)t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY A,B) AS 'RowNo',
A,B,C
FROM TableB
)t2
ON t2.RowNo=t1.RowNo
AND t2.C <>1

Micik
Starting Member

9 Posts

Posted - 2007-12-31 : 03:31:12

visakh16, thank you for that. It is very clever to join these table on row numbers since they have same number of rows. This is what almost worked:
UPDATE tabela_b
SET tabela_b.A=tabela_a.A,
tabela_b.B=tabela_a.B
FROM
(
SELECT ROW_NUMBER() OVER A AS 'RowNo',
A,B
FROM Tabela_A
)tabela_a
INNER JOIN
(
SELECT ROW_NUMBER() OVER B AS 'RowNo',
A,B,C
FROM Tabela_B
)tabela_b
ON tabela_b.RowNo=tabela_a.RowNo
where tabela_b.C <>1

However, problem is that "ORDER BY" because it sorts table, and after update ot tableB is done, data in other columns (one columns represent time stamp) do not match data that are newly updated from table A.
All I need to do now is to perform this without sorting.

Please, look at the following example:

Before Update command:
TableA
A B
9 4
7 4
4 6
2 6

TABLEB
A B C
1 4 0
2 6 1
7 2 0
3 2 0

Column C has 1 in second row, so only that row will stay unchanged. Other rows will be overwritten with data from TableA
Result should be:
TableB(updated)
A B C
9 4 0
2 6 1
4 6 0
2 6 0

But, If I execute above SQL statement I get this as result:

TableB
2 6 0
2 6 1
9 4 0
7 4 0

which is wrong, because 7 4 is second row in Table A, and in second row of table B thees is C = 1 which means that row shouldn't be copied.

I tried to join these tables using COUNT, something like this

UPDATE tabela_b
SET tabela_b.A=tabela_a.A,
tabela_b.B=tabela_a.B
FROM
(
SELECT COUNT(A) AS 'RowNo',
A,B
FROM Tabela_A
)tabela_a
INNER JOIN
(
SELECT COUNT(A) 'RowNo',
A,B,C
FROM Tabela_B
)tabela_b
ON tabela_b.RowNo=tabela_a.RowNo
where tabela_b.C <>1

But in that case I 'm getting error message:
Msg 8120, Level 16, State 1, Line 1
Column 'Tabela_A.A' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I think I'm very close...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:19:57

Is the A field always in decreasing order in TableA and increasing order in TableB?

Micik
Starting Member

9 Posts

Posted - 2007-12-31 : 04:24:40

No, unfortunately, there is no order...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:37:18

Then i think there's only one way to do this. Try this too:-

CREATE #tempA
(
ID int IDENTITY(1,1),
A int,
B int)

CREATE #tempB
(
ID int IDENTITY(1,1),
A int,
B int,
C bit)


INSERT INTO #tempA (A,B)
SELECT A,B FROM TableA

INSERT INTO #tempB (A,B,C)
SELECT A,B,C FROM TableB


UPDATE t2
SET t2.A=t1.A,
    t2.B=t1.B
FROM #tempA t1
INNER JOIN #tempB t2
ON t2.ID=t1.ID
AND t2.C <>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources