splitting a row into multiple rows

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Dears,

I have a table called CustomerA, with the below fields

Id(Pk,Identity) SenderName SenderMobileNo SendToName SendToMobile

1 PersonA 438983948 PersonB 3984938949

2 PersonC 343434343 PersonD 3984938948

And another table called CustomerB, with the below fields

Id(PK, Identity) Name MobileNo

I want to put Sendername, mobile and sendToName, Mobile from CustomerA table to the name,mobileNo on the customerB table.

Help me with a query how to do it.

Thanks

Regards
Azeem

Posted 21-Jun-13 12:15pm

Ahamed Azeem

Add a Solution

Comments

ankur789 21-Jun-13 23:47pm

select CustomerA.sendername,CustomerA.sendermobileno,CustomerA.sendtoname,customerb.name,customerb.mobileno from CustomerA join customerB on customerA.id=customerB.id

Ahamed Azeem 22-Jun-13 0:17am

Hi Ankur,

I want to insert the sendername, mobileno, sendtoname mobileno to the name and mobile number on the other table. actually splitting a row into 2 and insert to the other table

regards

ankur789 22-Jun-13 0:20am

Table1
-------------
ID Name
1 A
2 B
3 C

Table2
--------
ID Name
1 Z
I need to insert data from Table1 to Table2 and I can use following sytax for the same:-

INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1
However, In my case duplicate Ids might exist in Table2 (In my case Its Just "1") and I dont want to copy that again as that would throw an error.

I can write something like this:-

IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1
ELSE
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id<>1

Ahamed Azeem 22-Jun-13 0:27am

I think you are confused, I have two different structure in both tables

Table1
---------
Id SenderName SenderMobile SendToName SendToMobile

1 PersonA 053433434 PersonB 0343943893
2 PersonC 343434343 PersonC 9348398893

Table2
--------
Id Name Mobile

Now I want Table2 Id column should have 1, 2, 3, 4, and name should have personA, personB, PersonC, Person D, mobile should have 4 values from the table1

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Radhadeep12 · Accepted Answer · 2013-06-24T05:56:00

Hope this helps

CREATE TABLE Test
(
	Id INT IDENTITY, SenderName VARCHAR(30),SenderMobile INT,
	SendToName VARCHAR(30),SendToMobile INT
)
INSERT Test VALUES
('PersonA',53433423,'PersonB',34394388),
('PersonC',43434324,'PersonD',93483988)
SELECT * FROM Test

Id	SenderName	SenderMobile	SendToName	SendToMobile
1	PersonA		53433423	PersonB		34394388
2	PersonC		43434324	PersonD		93483988

SELECT IDENTITY(INT,1,1) AS Id, A.[Sender Name],A.[Sender Mobile] INTO #NewTab
FROM
(
	SELECT T.SenderName [Sender Name],T.SenderMobile [Sender Mobile] FROM Test T
	UNION ALL
	SELECT T.SendToName [Sender Name],T.SendToMobile [Sender Mobile] FROM Test T
)A

SELECT * FROM #NewTab

Id	Sender Name	Sender Mobile
1	PersonA		53433423
2	PersonC		43434324
3	PersonB		34394388
4	PersonD		93483988