SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT INTO Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sccrsurfer
Starting Member

USA
43 Posts

Posted - 03/22/2013 :  16:43:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4606 Posts

Posted - 03/22/2013 :  17:00:32  Show Profile  Reply with Quote
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

Bangladesh
6 Posts

Posted - 03/24/2013 :  02:14:06  Show Profile  Reply with Quote
Hello!
Thanks for nice shearing.................

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

sccrsurfer
Starting Member

USA
43 Posts

Posted - 03/25/2013 :  10:18:01  Show Profile  Reply with Quote
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

USA
43 Posts

Posted - 03/25/2013 :  10:53:40  Show Profile  Reply with Quote
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

784 Posts

Posted - 03/25/2013 :  11:30:04  Show Profile  Reply with Quote
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.




Edited by - Hommer on 03/25/2013 11:31:54
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 03/28/2013 :  11:54:47  Show Profile  Reply with Quote
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

USA
43 Posts

Posted - 03/28/2013 :  15:12:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 03/28/2013 :  15:52:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000