How to replace TOP 1000 rows of select columns indiscriminately

Question

Basically I have a table that contains 1000 rows with three columns. (TABLE A)

I have ANOTHER table with 200 columns with 1million+ records. (TABLE B)

I am trying to replace the THREE COLUMNS OF 1000 rows of TABLE B with those of TABLE A. I've read a lot of solutions where you can INSERT into table B from TABLE A.. but that's useless because I'll get NULLs in the remaining 197 columns that I need data for.

So the task is to replace rows of certain columns from one table to select columns of another table. There is NO conditions, just the top rows or whatever order you can think of is fine. If you can give an answer that takes ORDER BY something into account, that'd be bonus! Thank you so much!

this is a simple question, there is no use of me trying to mindlessly following the policy of "oh show your work".. obviously my work amounted to wrong results - I'd agree with you on complex questions where it makes sense to share part of the journey - this is a direct query with nothing to show for except results. So I see no point of sharing wrong data that I got... I did share links — LearnByReading, Dec 09 '15 at 17:42
"Show Your Work" is especially important in cases like this *because it greatly helps us to figure out what exactly you are trying to do*, which is not at all clear. Failing that, please give us an example, with data, of what you are looking for (before and after). The example can be reduced, say 10 rows of 8 columns in A to 3 columns in B... — RBarryYoung, Dec 09 '15 at 17:45

Martin Smith · Answer 1 · 2015-12-09T17:52:02.583

5

If I understood your requirements

WITH TA
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    ORDER BY col1) AS RN
         FROM   TableA),
     TB
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    ORDER BY col1) AS RN
         FROM   TableB)
UPDATE TB
SET    TB.col1 = TA.col1,
       TB.col2 = TA.col2,
       TB.col3 = TA.col3
FROM   TB
       JOIN TA
         ON TB.RN = TA.RN

edited Dec 09 '15 at 17:52

answered Dec 09 '15 at 17:47

Martin Smith

438,706
87
741
845

@Bulat - Thanks. Fixed – Martin Smith Dec 09 '15 at 17:52
@MartinSmith Thank you so much for your answer. I think someone fixed my question, it reads okay now. Basically, TABLE B (with lots of columns) needs updating from TABLE A (few columns). The answer is more complicated than I was thinking (not to say your answer is not efficient or correct, just saying I thought this was a simpler task than this). Thanks again – LearnByReading Dec 09 '15 at 17:55
@LearnByReading - So do the answers you have do what you need? – Martin Smith Dec 09 '15 at 18:01
Silly question, I get an error saying "TA" is not a recognizable table hint.. this is so odd. I can't fix it :S – LearnByReading Dec 09 '15 at 20:39

score 3 · Accepted Answer · answered Dec 09 '15 at 17:49

3

Try something like this:

WITH topB AS (
SELECT TOP 1000 row_number() OVER(ORDER BY field_n) rn, b.* FROM table_b b
ORDER BY field_x),
topA AS (
SELECT row_number() OVER(ORDER BY field_m) rn, a.*
FROM table_a a)
UPDATE b
SET 
  b.Field_1 = a.Field_1,
  b.Field_2 = a.Field_2,
  b.Field_3 = a.Field_3
FROM 
  TopB b JOIN TopA a ON b.rn = a.rn

Idea here is to assign row numbers in both tables, join them by these numbers, and update the B part of the join with values from A.

answered Dec 09 '15 at 17:49

Bulat

6,869
1
29
52

1

`+1` The `TOP 1000` could well help here. I'm not sure without testing whether it will end up numbering the whole million rows in B without that, – Martin Smith Dec 09 '15 at 17:57
funny how at least 8 member people found the answers helpful and yet the question is voted down. Stackoverflow is over-managed by too many pseudo-admins. – LearnByReading Dec 10 '15 at 20:46
@LearnByReading it is a democracy – Bulat Dec 11 '15 at 01:06

How to replace TOP 1000 rows of select columns indiscriminately

2 Answers2