Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT INTO Question

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
,Column2
FROM
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
Go to Top of Page

nurunseo
Starting Member

6 Posts

Posted - 2013-03-24 : 02:14:06
Hello!
Thanks for nice shearing.................

http://paneuromix.com/en
http://paneuromix.com/nootropics.html
Go to Top of Page

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 1
Col X|Col B|Col F|Col A|Col G|Col D
Data type: All are nvarchar with one or two columns that are datetime

I 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 datetime

Table 2
Headers: Col 1|Col 2|Col 3|Col4
Insert from Table 1: Col G|Col D|Col A|Col B

I 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.
Go to Top of Page

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?
Go to Top of Page

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
,Column2
FROM
Table1

You can load Column10 from source to destination MeaningfulTitle1
and 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.



Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 Optimizer
TG
Go to Top of Page
   

- Advertisement -