Solved

How to insert data from one table into multiple tables?

Posted on 2014-09-17
20
97 Views
Last Modified: 2015-02-11
Hello,

A big table has been created for me and it has 22 columns. I made a few set of table just to normalize it so I ended up having 7 tables (sql server 12).

Source: Table A
Destination: Table a1, a2, ..., a7

How can I select few columns from table A and insert them in a1, and few in table a2, and etc?  is it possible to do this using one Select INTO command? or do I need to create a cursor?



Thanks,
Ak
0
Comment
Question by:akohan
  • 6
  • 6
  • 4
  • +2
20 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40329200
You'll need a separate SELECT ... INTO for each destination table:

SELECT col1, col5, col7
INTO a1
FROM a

SELECT col1, col3, col4
INTO a2
FROM a

...
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40329218
As Scott already said you will need separate inserts for each destination table.

Also, if you are truly normalizing your table instead of just separating it into multiple tables then you will need to set up foreign key relationships and reference those new tables in the parent table.
0
 

Author Comment

by:akohan
ID: 40329224
Will it be guaranteed that my primary key in a1 will be the same a2?
I need to keep those records in associate with one another.  

Each Id in those tables is set as Integer (is identity and incremental)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40329237
You'll insert the linking value into all the tables -- I used "col1" for the common/key value above, showing it going into both=all tables.

Identity is not needed here, and should definitely NOT be the clustering key (it can be the PK, although that is worthless).
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40329307
IF you are normalizing it is likely you do not get 7 tables each with the same number of rows, so being able to calculate  PK/FK values is essential. You will also probably need a method to unpivot some data.

Not sure if you will follow this former blog item of mine but it is a small exercise in moving data from a non-normalized table into 3 normalized tables, using DENSE_RANK() to calculate the PK/FK columns (in a predictable manner)

(updated since writing the blog) http://sqlfiddle.com/#!3/22dde/1
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12316-Unpivotting-by-CROSS-APPLY-and-VALUES.html

{+ edit}
details of that sqlfiddle just in case the url is inactive
**MS SQL Server 2008 Schema Setup**:

     
    CREATE TABLE UnPivotMe (
    	FirstName varchar(255) NOT NULL, 
    	LastName varchar(255) NOT NULL,
    	Question1 varchar(1000) NOT NULL,
    	Answer1 varchar(1000) NOT NULL,
    	Question2 varchar(1000) NOT NULL,
    	Answer2 varchar(1000) NOT NULL,
    	Question3 varchar(1000) NOT NULL,
    	Answer3 varchar(1000) NOT NULL,
    	Question4 varchar(1000) NOT NULL,
    	Answer4 varchar(1000) NOT NULL,
    	Question5 varchar(1000) NOT NULL,
    	Answer5 varchar(1000) NOT NULL
    	)
    INSERT INTO UnPivotMe VALUES
    	('Kenneth','Fisher','What is your first name?','Kenneth','What is your favorite color?','green','What do you do for a living?','Not much',
    			'What is 2x3','6','Why?','Because'),
    	('Bob','Smith','What is your first name?','Robert','What is your favorite color?','blue','What is 4x7?','238',
    			'What is 7x6','Life the Universe and Everything','Why?','Why not'),
    	('Jane','Doe','What is your first name?','John','What is your favorite color?','plaid','What do you do for a living?','Door to door salesman',
    			'What is 3/4','.75','Why?','yes'),
    	('Prince','Charming','What is your first name?','George','What is your favorite color?','Orange','What do you do for a living?','Not much',
    			'What is 1235x523','Yea right','Why?','no')
    ;
    
    
    SELECT
        dense_rank() over (order by UnPivotMe.LastName , UnPivotMe.FirstName) as RespondentID
      , UnPivotMe.LastName
      , UnPivotMe.FirstName
    
    INTO RESPONDENTS
    
    FROM UnPivotMe
    
    ;
    SELECT DISTINCT
        dense_rank() over (order by CrossApplied.SeqNo, CrossApplied.Question) as QuestionID
      , CrossApplied.Question
    
    INTO QUESTIONS
    
    FROM UnPivotMe
    CROSS APPLY (
    	VALUES 
                    (1, Question1)
                  , (2, Question2)
                  , (3, Question3)
                  , (4, Question4)
                  , (5, Question5)
                ) AS CrossApplied(SeqNo, Question)
    
    ;
    
    SELECT
        dense_rank() over (order by UnPivotMe.LastName , UnPivotMe.FirstName) as RespondentID
      , QIDS.QuestionID
      , CrossApplied.Answer
    
    INTO ANSWERS
    
    FROM UnPivotMe
    CROSS APPLY (
    	VALUES 
                    (Question1, Answer1)
                  , (Question2, Answer2)
                  , (Question3, Answer3)
                  , (Question4, Answer4)
                  , (Question5, Answer5)
                ) AS CrossApplied(Question, Answer)
    INNER JOIN QUESTIONS AS QIDS ON CrossApplied.Question = QIDS.Question
    order by UnPivotMe.LastName , UnPivotMe.FirstName, QIDS.QuestionID
    ;
    

**Query 1**:

    select
    *
    from UnPivotMe
    

**[Results][2]**:
    
    | FIRSTNAME | LASTNAME |                QUESTION1 | ANSWER1 |                    QUESTION2 | ANSWER2 |                    QUESTION3 |               ANSWER3 |        QUESTION4 |                          ANSWER4 | QUESTION5 | ANSWER5 |
    |-----------|----------|--------------------------|---------|------------------------------|---------|------------------------------|-----------------------|------------------|----------------------------------|-----------|---------|
    |   Kenneth |   Fisher | What is your first name? | Kenneth | What is your favorite color? |   green | What do you do for a living? |              Not much |      What is 2x3 |                                6 |      Why? | Because |
    |       Bob |    Smith | What is your first name? |  Robert | What is your favorite color? |    blue |                 What is 4x7? |                   238 |      What is 7x6 | Life the Universe and Everything |      Why? | Why not |
    |      Jane |      Doe | What is your first name? |    John | What is your favorite color? |   plaid | What do you do for a living? | Door to door salesman |      What is 3/4 |                              .75 |      Why? |     yes |
    |    Prince | Charming | What is your first name? |  George | What is your favorite color? |  Orange | What do you do for a living? |              Not much | What is 1235x523 |                        Yea right |      Why? |      no |


**Query 2**:

    select
    *
    from RESPONDENTS
    

**[Results][3]**:
    
    | RESPONDENTID | LASTNAME | FIRSTNAME |
    |--------------|----------|-----------|
    |            1 | Charming |    Prince |
    |            2 |      Doe |      Jane |
    |            3 |   Fisher |   Kenneth |
    |            4 |    Smith |       Bob |


**Query 3**:

    select
    *
    from QUESTIONS
    

**[Results][4]**:
    
    | QUESTIONID |                     QUESTION |
    |------------|------------------------------|
    |          1 |     What is your first name? |
    |          2 | What is your favorite color? |
    |          3 | What do you do for a living? |
    |          4 |                 What is 4x7? |
    |          5 |             What is 1235x523 |
    |          6 |                  What is 2x3 |
    |          7 |                  What is 3/4 |
    |          8 |                  What is 7x6 |
    |          9 |                         Why? |


**Query 4**:

    select
    *
    from ANSWERS
    

**[Results][5]**:
    
    | RESPONDENTID | QUESTIONID |                           ANSWER |
    |--------------|------------|----------------------------------|
    |            1 |          1 |                           George |
    |            1 |          2 |                           Orange |
    |            1 |          3 |                         Not much |
    |            1 |          5 |                        Yea right |
    |            1 |          9 |                               no |
    |            2 |          1 |                             John |
    |            2 |          2 |                            plaid |
    |            2 |          3 |            Door to door salesman |
    |            2 |          7 |                              .75 |
    |            2 |          9 |                              yes |
    |            3 |          1 |                          Kenneth |
    |            3 |          2 |                            green |
    |            3 |          3 |                         Not much |
    |            3 |          6 |                                6 |
    |            3 |          9 |                          Because |
    |            4 |          1 |                           Robert |
    |            4 |          2 |                             blue |
    |            4 |          4 |                              238 |
    |            4 |          8 | Life the Universe and Everything |
    |            4 |          9 |                          Why not |



  [1]: http://sqlfiddle.com/#!3/22dde/1

Open in new window

0
 

Author Comment

by:akohan
ID: 40329313
No, it is not clustering. Let me explain here better so you guys can correct me in case I have been wrong:

rawtable A has
id int
Col1 varchar
Col2 varchar
Col 3 varchar
Col4 varchar
Col5 varchar
Col6 varchar

I am inserting value1 (in col1) ~  value4 (in col4) in table a1 where a1 has a single column as Id which is int and incremental
Then I am inserting value3 (in col5) in a2 and a2 has a structure as
Id  int
col 1

and same thing value6 (in col6) of table A gets inserted into a2 table where its structure is:
Id  int
col 1

Questions:

1) what should I do as far as foreign key in a2 (its ID column) ? the truth is that when I insert from A into a1 each primary get generated by the table per each insert so now I need to have the same value of a1.Id in a2.Id so that they can stay related.

Any help will be appreciated.
0
 

Author Comment

by:akohan
ID: 40329336
PortletPaul,
Thanks for the URL and also pasting the sample, I see what you have done here. Of course, after years of not doing DB I feel not updated and have lost  my intimacy with sql server.

Of course, my case is a little different but I think would work for me. I am getting data from IIS log file which are several columns coming from web server and different columns each showing how user behaves and what IP or queries are involved.

Let me look at it and I will get back to you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40329345
If you are facing a regular cycle of inserts from IIS logs then my proposal to calculate the PK/FK via dense_rank() might be too simplistic, but I do find the unpivot approach very useful.
0
 

Author Comment

by:akohan
ID: 40329348
so can I follow the same thing you have done here for my IIS inputs?
0
 

Author Comment

by:akohan
ID: 40329350
for now it is a daily insertion but eventually will be every hour.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40329366
I am quite certain other are actually doing something similar from IIS logs (I don't)
I suggest you wait for others to comment
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40330969
Will it be guaranteed that my primary key in a1 will be the same a2?
This is not normalizing the table.  It is de-normalizing it.  1-1 relationships (which is what you are asking how to create) are extremely rare in the real world.  They are most frequently created for efficiency when you want to isolate memo fields or other large objects from the rest of the table data.  Or occasionally when you have multiple entity types and so you want to create separate tables to hold their specific columns.  So you have an entity table and then subclass it into Individuals and Companies.

To do what you are asking (even though at the moment I believe it is incorrect), the primary table has the identity column primary key.  All the related tables have long integer primary keys.  So when you insert into the secondary tables, you must include the PK from the primary table and that becomes the FK to the primary table as well as the PK for the secondary table.  In any case, you have to do this with multiple queries or a code loop.
0
 

Author Comment

by:akohan
ID: 40331074
Hello PatHartman,

So now two things:

First, if it is incorrect then what you do suggest I can do?
Second, if there is no a better way and we have to stick to this plan then how should I do this with multiple queries or a code loop?

Thanks.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40331111
It is possible that data was normalized to produce multiple tables.  For example, if the original table included columns such as "phone1", "phone2", "email1", "email2", etc..

You child tables should:
1) have the possibility of more than 1 row; if there will only ever be one row, then leave those column(s) in the main table
2) the child table should be keyed by the id of the parent table + the identifying data from the second table, such as phone number, email address, etc..
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40331127
Dividing one table into multiples by arbitrarily picking out columns isn't normalization.  Normalization is going through every column in the table and applying at least first through third normal forms and then refining further if necessary.  Once you determine that a column belongs in a different table, then it will have a unique PK in its new table and a FK that will link it to the parent table.

So, the question is - why are you breaking up the table?  What normal form does the column you are removing violate?  Once you understand why you are removing the column, you will know what its relationship should be and therefore how to construct its Foreign Key.

Frequently, tables created for us by other parties have been de normalized.  Sometimes (but not always), it makes sense to normalize them.  For example I would always normalize repeating groups because that would make my queries easier but I would normally leave partial dependencies alone because I am not updating this data so I have no problem with having EmployeeID and EmployeeName in my sales detail records but I would have a problem having 12 columns for months.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40331138
>> so I have no problem with having EmployeeID and EmployeeName in my sales detail records <<

I sure would.  Not only can name change, but it's a HUGE waste of disk space, since there could be many detail rows.


Some normalizations are done simply to add consistency to data and to save space, such as for code values and corresponding descriptions.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40331283
Scott, This is data the OP is getting from some other company/system.  It isn't coming from his own database.  If he is going to keep it and maintain it, that is a whole different story.  If he downloads it every day/week/month/year, there is no benefit to trying to keeping it normalized.  You do what you need to do to make the reporting easy.  You won't be updating it so you won't have to worry about data anomalies cropping up.  You have to assume that the source data is correct because if it isn't, that is an entirely different project.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40331300
Pat:

Again, one can normalize for reasons other than just data maintenance/changes.  And, being a DBA, my tendency is to do so whenever it seems required, whether developers understand why or not.

For example, there are still consistency and disk space benefits to be gained from normalization of long character strings of certain types.

Let's take a less obvious case that many people overlook, say street address.  Most people just put address1 (line 1), address2, etc., in every table with an address.  But if you store billions of rows of such addresses, for shipments, etc., as we do, that is huge amounts of storage and inconsistency.  Conversely, if I normalize the street name to a code instead, then 1 = "Main St" (USPS standard abbrev for "Street") for every city in the country with a Main Street.  Moreover, every such entry now refers to "Main St" and not "Main Rd" or "Main" or "Main Ave", etc..  [If it really is "Main Rd", that would be a different entry.]
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40331464
Scott,
I am with you 100% if the data is mine to keep.  If I am using it for reference, I am not inclined to fix it up any more than I have to.  Just because I import the data into my database doesn't make it mine to keep.  I import it for convenience and discard it when the replacement file arrives.  It is temporary and I don't update it.  I simply replace it when a new file arrives.  If that is not the situation here, then I agree, more complete normalization is called for.  Think of it as a data warehouse that happens to be in your own database.  But, no one prior to me even questioned the splitting of the tables.  They were just offering solutions on how to duplicate the PK including yourself.

I have worked for clients where postal mail was the lifeblood of the company (Readers' Digest for example) and they broke addresses down completely.  For lots of reasons, including the ability to find duplicates.  Most applications are not concerned with postal efficiency or weeding out duplicate addresses.  They keep an address because they may need to mail something.  There is quite a different standard in how addresses are kept in the typical application from how they are kept in one that deals with direct mail or shipping.  You wouldn't completely abstract the addresses in my employee table.  There is no reason to do it and there's only 200 of them.  Keeping the typical 5 columns is more than normalized enough.  I've seen way too many that smush everything together into a single column.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40331474
I assumed the initial tables were not done arbitrarily; you assumed they were, as you stated.

I also stated a simple rationale by which the OP could determine if multiple tables were needed.  If OP continues to ask about multiple tables, I assume they met that rationale.  Because:
1) I assume competency in the absence of demonstrated incompetency
2) If they really insist on separate tables, they will create them regardless
3) there may be other requirements and details of which I am unaware (and frankly I probably don't have the time to get into anyway).
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now