November 3, 2011 at 10:26 pm
I have two tables
A and B
A has A.ID,A.Name, A.add, A.lastname
B has
B has B.ID,B.Name, B.add, B.lastname,B.otherfield
and i need to copy B into A with a query but i have nothing to match the two tables on as table A is empty so when i try to copy from B i get 0 rows updated
I tried
UPDATE A
SET A.ID = B.ID
from B inner join A
where A.ID <> B.ID
November 3, 2011 at 10:48 pm
SQLTestUser (11/3/2011)
I have two tablesA and B
A has A.ID,A.Name, A.add, A.lastname
B has
B has B.ID,B.Name, B.add, B.lastname,B.otherfield
and i need to copy B into A with a query but i have nothing to match the two tables on as table A is empty so when i try to copy from B i get 0 rows updated
I tried
UPDATE A
SET A.ID = B.ID
from B inner join A
where A.ID <> B.ID
If A is empty why are you trying to update? You need to insert the data:
insert into dbo.A (ID, Name, [add], lastname)
select ID, Name, [add], lastname from dbo.B;
November 3, 2011 at 11:04 pm
Thankyou
now the issue is that there are multiple Rows that have similarID's and i need to update columns with same ID's into a single row, I think i can use CTE for this but if there is any other better way of doing this please let me know
A now has
ID name lastname address description
1 sam atkins sanfran moved in 2009
1 sam atkins haiti moved in 2010
1 sam atkins london moved in 2011
i have to make it into
1 sam atkins sanfran,haiti,london moved in 2009, moved 2010, moved in 2011
November 3, 2011 at 11:22 pm
Instead of giving us requirements piecemeal, how about making sure you tell use the whole story upfront.
First, please read the first article I reference below in my signature block. It will walk you step by step as to what you need to post and how to get the best answers in return. Also, be sure to provide the expected results based on the sample data you post.
What all this leads to is that I could have concatenated values during the insert into table A had I known more about what you were trying to accomplish.
November 4, 2011 at 12:04 am
Sorry for the confusion,
yes i have to insert into table A and then ensure that similar ID's are concatenated into a single row of data as i displayed in my example above
So something like below is in table b
ID Name lastname Address Moving details
1 sam atkins sanfran moved in 2009
1 sam atkins haiti moved in 2010
1 sam atkins london moved in 2011
which when inserted into table A has to be concatenated into a single row as
ID Name lastname Address Moving details
1 sam,sam,sam atkins,atkin,atkin sanfran,haiti,london moved in 2009, moved in 2010, moved in 2011
thanks
November 4, 2011 at 6:43 am
SQLTestUser (11/4/2011)
Sorry for the confusion,yes i have to insert into table A and then ensure that similar ID's are concatenated into a single row of data as i displayed in my example above
So something like below is in table b
ID Name lastname Address Moving details
1 sam atkins sanfran moved in 2009
1 sam atkins haiti moved in 2010
1 sam atkins london moved in 2011
which when inserted into table A has to be concatenated into a single row as
ID Name lastname Address Moving details
1 sam,sam,sam atkins,atkin,atkin sanfran,haiti,london moved in 2009, moved in 2010, moved in 2011
thanks
Again, please read the first article I reference below in my signature block. Follow the guidlines in that article regarding what and how to post the information we need to help you. Be sure to include your expected results based on the sample data you will post.
November 4, 2011 at 7:03 am
It's easy enough to have T-SQL build the kind of strings you need. But I have to ask why you want to violate basic database design that way. Why denormalize like that?
It's going to be more efficient to store the data in one-row-per-datum than horizontally like that.
For example, what do you do if you find out that Sam Atkins moved to Moscow in 2010 before he moved to Haiti? Now you have to insert data in the middle of the string. That's complex. Inserting a new row is trivially easy, inserting in the middle of a string is painfully complex.
Also, why store the repeating strings for first and last name?
If you really insist on storing data that way, it can be done like this:
SELECT DISTINCT ID,
STUFF((SELECT ',' + FirstName
FROM dbo.TableB AS B_FirstName
WHERE B_FirstName.ID = TableB.ID
FOR XML PATH(''), TYPE).value('.','VARCHAR(1000)'), 1, 1, '') AS FirstNames
FROM dbo.TableB ;
Copy the sub-query for each column you want to concatenate that way.
But only do that if you really, truly understand the drawbacks of what you're doing (there are lots of drawbacks to doing this in most data situations), and decide to go ahead with it after due consideration of the very few advantages it might possibly give you.
Usually, if you need to concatenate data this way for some specific purpose, it's better to do so at runtime, rather than storing it that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply