Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

How to insert data from one table into multiple tables?

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
akohan
Asked:
akohan
  • 6
  • 6
  • 4
  • +2
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
Brian CroweCommented:
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
 
akohanAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Scott PletcherSenior DBACommented:
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
 
PortletPaulCommented:
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
 
akohanAuthor Commented:
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
 
akohanAuthor Commented:
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
 
PortletPaulCommented:
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
 
akohanAuthor Commented:
so can I follow the same thing you have done here for my IIS inputs?
0
 
akohanAuthor Commented:
for now it is a daily insertion but eventually will be every hour.
0
 
PortletPaulCommented:
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
 
PatHartmanCommented:
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
 
akohanAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
PatHartmanCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
PatHartmanCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
PatHartmanCommented:
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
 
Scott PletcherSenior DBACommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now