November 20, 2012 at 10:07 am
I have an issue trying to apply this.
The way I am doing it it applies the data into new rows with NULL values for all the other columns.
Table A – Multiple columns 100K Rows
Table B One Column 100K rows
I need to add an extra column onto Table A (not a problem), then insert the data from table B into the existing rows with the empty column into table A.
How do I achieve this.
November 20, 2012 at 10:22 am
Please provide detailed information including scripts to create and populate sample tables so we can see exactly what you are trying to accomplish.
Reading http://www.sqlservercentral.com/articles/Best+Practices/61537/ is a good start.
November 20, 2012 at 10:23 am
One would assume that the rows are ordered in exactly the same way in both tables. if that is true, you could first select into a temp table like this :
SELECT tableA.PrimaryKey, tableB.Column1 into #temp
from tableA, tableB
ORDER BY tableA.PrimaryKey
You would then update your original table, tableA as below:
UPDATE tableA
SET NewColumn = t.Column1
from #temp t
inner join tableA a
on a.PrimaryKey = t.PrimaryKey
November 20, 2012 at 10:28 am
I'll give that a bash tomorrow, if this works you are the man!!!!
Thanks.
November 20, 2012 at 10:34 am
Guru Nagabhushan (11/20/2012)
One would assume that the rows are ordered in exactly the same way in both tables. if that is true, you could first select into a temp table like this :
SELECT tableA.PrimaryKey, tableB.Column1 into #temp
from tableA, tableB
ORDER BY tableA.PrimaryKey
You would then update your original table, tableA as below:
UPDATE tableA
SET NewColumn = t.Column1
from #temp t
inner join tableA a
on a.PrimaryKey = t.PrimaryKey
Not sure why you created a cross join into a temp table. This is not the way to do this. Which value will be used in the update?
You can use this type of update but there is no need for a temp table. Here is a better way to do this.
UPDATE tableA
SET NewColumn = b.Column1
from tableA a
inner join tableB b on a.PrimaryKey = b.PrimaryKey
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2012 at 10:59 am
Thanks it looks good.
Again I'll give it a try tomorrow. I see the logic more clearly. Makes perfect sense. Just couldn't figure it out.
November 20, 2012 at 12:53 pm
Worked a treat. Nice one
November 20, 2012 at 12:58 pm
Sean, From the initial problem description, it looks like the second table does not have the(or any) primary key, which was my assumption to start with. A direct update in that case would not work. However, your point about the cross join was correct.
If my assumptions are still valid(TableB being just the one column, without any primary key), then the following should work(Please also bear in mind that this will only work if tableA does not have an identity based column. if it is, an alternate non nullable unique key will be needed and can be used):
SELECT IDENTITY(int, 1, 1) as Rownum, PrimaryKey
into #temp1
from TableA
order by PrimaryKey
SELECT IDENTITY(int, 1, 1) as Rownum, Col1
into #temp2
from TableB
SELECT PrimaryKey, Col1
into #temp3
from #temp1 t1
inner join #temp2 t2
ON t1.Rownum = t2.Rownum
Update TableA
SET NewColumn = t3.Col1
from #temp3 t3
inner join TableA
ON TableA.PrimaaryKey = t3.PrimaryKey
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply