copy data from table by column and insert into single column in other table

Question

copy data from table by column and insert into single column in other table

tksmith

SSC Enthusiast

Points: 125
More actions
September 20, 2008 at 4:03 pm

#123788

Hi i'm looking for the best way to migrate data from an old table with column1,column2,column3 and insert the data from those columns into column1 in my new table.
the tables aren't too huge, 10,000 records.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To help those who can help you.... please follow the posting protocol as covered by the article in my signature block.. Thanks

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

tksmith SSC Enthusiast Points: 125 More actions · Answer 2

i don't have anything else to post.

i'm not asking to be spoon fed, just a suggested best stratgey i'll google myself from there.

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 3

you can try below one if data types of all columns(col1,col2, ___) in old table are same and in new table column is in varchar:

"insert into testcon select convert(varchar(20),col1)+convert(varchar(20),col2)+convert(varchar(20),col3) from con"

Thank You.

Regards,
Raghavender Chavva

tksmith SSC Enthusiast Points: 125 More actions · Answer 4

datatypes are all the same anyway. think reverse pivot.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

i don't have anything else to post.
i'm not asking to be spoon fed, just a suggested best stratgey i'll google myself from there

A ha so you did post some more information.

datatypes are all the same anyway. think reverse pivot.

Almost all members try to be helpful ... If you continue thinking as you have why not go Google...........INSERT INTO and / or SELECT INTO

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

tksmith (9/20/2008)
Hi i'm looking for the best way to migrate data from an old table with column1,column2,column3 and insert the data from those columns into column1 in my new table.
the tables aren't too huge, 10,000 records.

INSERT INTO NewTable(Col1)

SELECT Col1 from OldTable UNION ALL

SELECT Col2 from OldTable UNION ALL

SELECT Col3 from OldTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

bitbucket (9/20/2008)
To help those who can help you.... please follow the posting protocol as covered by the article in my signature block.. Thanks

BitBucket... you can turn that link into a clickable link by putting it between (url)(/url) IFCode shortcuts. Notice that you should replace the parenthesis with square brackets.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tksmith SSC Enthusiast Points: 125 More actions · Answer 8

Jeff Moden (9/21/2008)
tksmith (9/20/2008)
Hi i'm looking for the best way to migrate data from an old table with column1,column2,column3 and insert the data from those columns into column1 in my new table.
the tables aren't too huge, 10,000 records.
INSERT INTO NewTable(Col1)
SELECT Col1 from OldTable UNION ALL
SELECT Col2 from OldTable UNION ALL
SELECT Col3 from OldTable

thanks jeff that worked a treat - i was messing around with unpivot and it just wasn't happening. union is a much simply approach.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Perfect. Thanks for the feedback, Tk...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)