Author |
Topic |
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-03-22 : 16:43:57
|
Hi Guys,First I want to say that I appreciate everyone's help. It's the usual suspects who usually provide me the solutions I need, and it's much appreciated. I've been learning SQL to great extent since being on here, and of course the more I learn, the more complicated things get!! So I have a question.I had a flat table with every piece of data ever collected for this program we're measuring in the office. It's just one table, each record unique, but obviously with lots of redundancies. We got smart and decided to split it up into separate tables. How do I do that :) ?The field names dont match. We revamped the field names so they actually make sense. Field names like "ColumnA" were hardly descriptive and totally meaningless. How do I insert the data from "ColumnA" on Table1 to "MeaningfulTitle" on Table2? Further, how do I do this for multiple columns in one query? Thank you in advance. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-22 : 17:00:32
|
When dealing with Tables that have Rows and Columns, it helps to provide DDL, DML and expected output.quote: How do I insert the data from "ColumnA" on Table1 to "MeaningfulTitle" on Table2?
Well the basic gist is:INSERT Table2 ( MeaningfulTitle1 ,MeaningfulTitle2 ,... )SELECT Column1 ,Column2FROM Table1 If you need more help, please read the following link for how to post database questions:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
nurunseo
Starting Member
6 Posts |
Posted - 2013-03-24 : 02:14:06
|
Hello! Thanks for nice shearing.................http://paneuromix.com/enhttp://paneuromix.com/nootropics.html |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-03-25 : 10:18:01
|
Thanks for the tip Lamprey. So I have data like below. Pretty unorganized. Table 1Col X|Col B|Col F|Col A|Col G|Col DData type: All are nvarchar with one or two columns that are datetimeI want to move this to the much more organized and data-type defined Table 2. The column names don't match, and they are not in the same order. That's where the challenge lies for me. They are different datatypes but I think I will be ok. There is a combination of ints, varchars, text and datetimeTable 2Headers: Col 1|Col 2|Col 3|Col4Insert from Table 1: Col G|Col D|Col A|Col BI want to insert the data into the appropriate columns and be able to say, in the query (psuedo code) "I want Col G in Table 1 to go into Col 1 in Table 2. I do not have to worry about distinct values. |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-03-25 : 10:53:40
|
Since the columns from the source table are not in the same order as the destination table, would it be a bad idea to create new temporary tables with the relevant columns in order, then do the INSERT INTO for the final destination table? |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-03-25 : 11:30:04
|
Order of columns is not that important here, and I don't think a temp table make any difference.Borrowing example from Lamprey INSERT Table2 ( MeaningfulTitle1 ,MeaningfulTitle22 ,... )SELECT Column10 ,Column2FROM Table1You can load Column10 from source to destination MeaningfulTitle1and Column2 from source to destination MeaningfulTitle22, as long as your query make the right alignment. Data type changes is a bigger issue here. Your query may run into different kind of warnings/errors duo to data type not compatible.Another consideration is that since you are splitting one large table to two or more tables, the relationship between these new tables need to be defined and keys identified. |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-03-28 : 11:54:47
|
Sorry I haven't thanked you all sooner. It's been a crazy week. I will try this and report back. |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-03-28 : 15:12:31
|
Guys, I'm really puzzled. I used the queries above, queries ran and got the "Query Executed Successfully" but when I open the table, all of the values are NULL. Any clues on why this may be happening? I refreshed and everything, yet no values were inserted into this table. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-28 : 15:52:21
|
When your query completed did sql server report number of rows affected?Post the actual code you used to insert.Be One with the OptimizerTG |
|
|
|