How do I stitch together tables using SQL?

Question

Ok, I am learning SQL and just installed SQL Server. I've read about outer joins and inner joins but am not sure either is what I want. Basically, I want to reconstruct a text file that has been "chopped" up into 5 smaller text files. The columns are the same across all 5 text files, e.g. name, age, telephone #, etc. The only difference is that they have different numbers of rows of data.

What I'd like to do is "append" the data from each file into one "mega-file". Should I create a table containing all of the data, or just create a view? Then, how do I implement this...do I use union? Any guidance would be appreciated, thanks.

Why are you accessing files through SQL Server? You can append files on the shell (command.com). — Oded, Jan 15 '13 at 19:08
Because the combined file is greater than 40 million rows of data and I'd like to run some analysis on it. Should I still append through the shell or use SQL Server? — MrPatterns, Jan 15 '13 at 19:22
Well, without knowing details... You can still append in shell and use something like [logparser](http://en.wikipedia.org/wiki/Logparser) to analyse it. — Oded, Jan 15 '13 at 19:23

score 1 · Answer 1 · edited Feb 15 '13 at 18:19

1

You need to look into using UNION.

SELECT * 
FROM TABLE1 
UNION
SELECT * 
FROM TABLE2

And I would just create a View -- no need to have a stored table especially if the data ever changes.

edited Feb 15 '13 at 18:19

wickedone

542
1
6
18

answered Jan 15 '13 at 19:06

sgeddes

62,311
6
61
83

thanks for the guidance. in what scenario might one decide to recreate a table instead of creating a view? – MrPatterns Jan 15 '13 at 19:22

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Beyond your immediate goal of merging the five files it sounds like you want the data contained in your text files to be generally available for more flexible analysis.

An example of why you might require this is if you need to merge other data with the data in your text files. (If this is not the case then Oded is right on the money, and you should simply use logparser or Visual Log Parser.)

Since your text files all contain the same columns you can insert them into one table*.

Issue a CREATE statement defining your table
Insert data into your newly created table**
Create an index on field(s) which might often be used in query predicates
Write a query or create a view to provide the data you need

*Once you have your data in a table you can think about creating views on the table, but to start you might just run some ad hoc queries.

**Note that it is possible to accomplish Step 2 in other ways. Alternatively you can programmatically construct and issue your INSERT statements.

Examples of each of the above steps are included below, and a tested example can be found at: http://sqlfiddle.com/#!6/432f7/1

-- 1.
CREATE TABLE mytable 
    (
     id int identity primary key, 
     person_name varchar(200), 
     age integer,
     tel_num varchar(20)
    );

-- 2. or look into BULK INSERT option https://stackoverflow.com/q/11016223/42346
INSERT INTO mytable
    (person_name, age, tel_num)
VALUES
    ('Jane Doe', 31, '888-888-8888'),
    ('John Smith', 24, '888-555-1234');

-- 3.
CREATE UNIQUE INDEX mytable_age_idx ON mytable (age);

-- 4.
SELECT id, person_name, age, tel_num
FROM mytable
WHERE age < 30;

For analyzing 40M rows, it might be helpful to index the data. — HABO, Jan 15 '13 at 19:37
Indeed. Thanks for adding that. I have edited the answer to include some information about indexing. — mechanical_meat, Jan 15 '13 at 19:46

How do I stitch together tables using SQL?

2 Answers2