Appending tables

[Shwetabh]

Hi,
I wanted to know if it is possible to do to append two tables into a
third table.
For example, consider these two tables

Table 1
--------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 |
--------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
--------------------------------------------------------------

Table 2
---------------------------------------------------------------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
---------------------------------------------------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
---------------------------------------------------------------


Now, I want to append them to get this :


Table 3

-----------------------------------------------------------------------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-----------------------------------------------------------------------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
----------------------------------------------------------------------------------------------------------------------------


The blanks in Table 3 are , well ,blank.

Now can it be done or not?

Awaiting your replies,
Regards,
Shwetabh

[David Portas]

Re: Appending tables

Shwetabh wrote:[color=blue]
> Hi,
> I wanted to know if it is possible to do to append two tables into a
> third table.
> For example, consider these two tables
>
> Table 1
> --------------------------------------------------------------
> | Part_num | Prt_name | Desc1 | Desc2 |
> --------------------------------------------------------------
> | PRT1 | PartA | abc | xyz |
> | PRT2 | PartB | def | aaa |
> | PRT3 | PartC | ghi | bbb |
> --------------------------------------------------------------
>
> Table 2
> ---------------------------------------------------------------
> | Cat_num | Cat_name | SDsc1 | SDsc2 |
> ---------------------------------------------------------------
> | CAT1 | CatalogA | abc | xyz |
> | CAT2 | CatalogB | def | aaa |
> | CAT3 | CatalogC | ghi | bbb |
> ---------------------------------------------------------------
>
>
> Now, I want to append them to get this :
>
>
> Table 3
>
> -----------------------------------------------------------------------------------------------------------------------------
> | Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
> SDsc1 | SDsc2 |
> -----------------------------------------------------------------------------------------------------------------------------
> | PRT1 | PartA | abc | xyz |
> |
> | PRT2 | PartB | def | aaa |
> |
> | PRT3 | PartC | ghi | bbb |
> |
> | | | | | CAT1
> | CatalogA | abc | xyz |
> | | | | | CAT2
> | CatalogB | def | aaa |
> | | | | | CAT3
> | CatalogC | ghi | bbb |
> ----------------------------------------------------------------------------------------------------------------------------
>
>
> The blanks in Table 3 are , well ,blank.
>
> Now can it be done or not?
>
> Awaiting your replies,
> Regards,
> Shwetabh[/color]

My browser isn't displaying your Table 3 very well so I'm not quite
certain which data is going into which column. In general you can merge
tables like this using UNION:

SELECT part_num, prt_name, desc1, ...
FROM Table1
UNION ALL
SELECT NULL, NULL, cat_name, ...
FROM Table2 ;

Each SELECT list in the UNION has to have the same number of columns
and each column has to be made up of compatible datatypes. In your case
it seems like the big question is what will be the key of Table3? It
isn't clear to me whether it has a key at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Appending tables

Shwetabh (shwetabhgoel@g mail.com) writes:[color=blue]
> Table 3
>
> --------------------------------------------------------------------------[/color]
---------------------------------------------------[color=blue]
>| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
> SDsc1 | SDsc2 |
> --------------------------------------------------------------------------[/color]
---------------------------------------------------[color=blue]
>| PRT1 | PartA | abc | xyz |
> |
>| PRT2 | PartB | def | aaa |
> |
>| PRT3 | PartC | ghi | bbb |
> |
>| | | | | CAT1
> | CatalogA | abc | xyz |
>| | | | | CAT2
> | CatalogB | def | aaa |
>| | | | | CAT3
> | CatalogC | ghi | bbb |
> --------------------------------------------------------------------------[/color]
--------------------------------------------------[color=blue]
>
>
> The blanks in Table 3 are , well ,blank.
>
> Now can it be done or not?[/color]

Judging from the sample data you posted, what you want is

SELECT a.Part_num, a.Prt_name, a.Desc1, a.Desc2, b.Cat_num,
b.Cat_name, b.SDsc1, b.SDcs2
FROM table1 a
JOIN table2 b ON a.Desc1 = b.SDsc1
AND b.Desc2 = b.SDcs2

But I cannot say that it make much sense to join over a description column.

Maybe you need to consider a little more what you are actually looking
for and what you want to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shwetabh]

Re: Appending tables

Hi,
it seems Table 3 got pretty messed up.
So I will give the schema definations of the tables here:

Table 1:

CREATE TABLE TABLE1
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20)
)

Table 2:

CREATE TABLE TABLE2
(
PART_NUM varchar(10) primary key,
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)

Now the resultant table should have the following schema:

CREATE TABLE TABLE3
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20),
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)

This schema will be created programmaticall y.

Now my question is, if it is possible, how can I
insert records from table1 and table2 in table3?
I hope I have now made the things clearer.

Awaiting your reply,
Regards,
Shwetabh

[David Portas]

Re: Appending tables

Shwetabh wrote:[color=blue]
> Hi,
> it seems Table 3 got pretty messed up.
> So I will give the schema definations of the tables here:
>
> Table 1:
>
> CREATE TABLE TABLE1
> (
> PART_NUM varchar(10) primary key,
> PRT_NAME VARCHAR(10),
> DESC1 VARCHAR(20),
> DESC2 VARCHAR(20)
> )
>
> Table 2:
>
> CREATE TABLE TABLE2
> (
> PART_NUM varchar(10) primary key,
> CAT_NUM VARCHAR(10),
> CAT_NAME VARCHAR(10),
> SDESC1 VARCHAR(20),
> SDESC2 VARCHAR(20)
> )
>
> Now the resultant table should have the following schema:
>
> CREATE TABLE TABLE3
> (
> PART_NUM varchar(10) primary key,
> PRT_NAME VARCHAR(10),
> DESC1 VARCHAR(20),
> DESC2 VARCHAR(20),
> CAT_NUM VARCHAR(10),
> CAT_NAME VARCHAR(10),
> SDESC1 VARCHAR(20),
> SDESC2 VARCHAR(20)
> )
>
> This schema will be created programmaticall y.
>
> Now my question is, if it is possible, how can I
> insert records from table1 and table2 in table3?
> I hope I have now made the things clearer.
>
> Awaiting your reply,
> Regards,
> Shwetabh[/color]

It looks like you'll want something like this:

INSERT INTO Table3
(part_num, prt_name, desc1, desc2,
cat_num, cat_name, sdesc1, sdesc2)
SELECT COALESCE(T1.par t_num, T2.part_num),
T1.prt_name, T1.desc1, T1.desc2,
T2.cat_num, T2.cat_name, T2.sdesc1, T2.sdesc2
FROM Table1 AS T1
FULL JOIN Table2 AS T2
ON T1.part_num = T2.part_num ;

It still seems at least questionable whether Table3 or even Table2
represent "good" designs but as I only have your column names to go on
there isn't much point in me speculating about that.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Shwetabh]

Re: Appending tables

Hi,
Well I cant really do anything about the design for table2 because that
is the way the client wants it to be.

As far as table3 goes, I wanted to check the possiblility of appending
the
records of all the tables into a single table and check the performance
and
efficiency. I understand that table3 is a poor database design
but the main motivation for doing this is to check the reaction of the
application
which will using such database.

Also, till now, I have converted DBASE database into SQL database using

OPENROWSET to import the data, I was wondering if the same database
can be entered into a new table which can hold the data from all
tables.

Any ideas?

[Shwetabh]

Re: Appending tables

Hi,
Well I cant really do anything about the design for table2 because that
is the way the client wants it to be.

As far as table3 goes, I wanted to check the possiblility of appending
the
records of all the tables into a single table and check the performance
and
efficiency. I understand that table3 is a poor database design
but the main motivation for doing this is to check the reaction of the
application
which will using such database.

Also, till now, I have converted DBASE database into SQL database using

OPENROWSET to import the data, I was wondering if the same database
can be entered into a new table which can hold the data from all
tables.

Any ideas?

Regards,
Shwetabh


P.S: If you want I can mail you the code I have written in VB for this
purpose.

[Doug]

Re: Appending tables

In table one, there are rows or records. Take the "first one."

Now in table two there are a bunch of rows or records.

how do we know which record or records from the second to table to
combine with the first row of the first table?

In other words, if there are 100 rows in the first table, and 10 rows
in the second, how many rows are you expecting in the third table?

[Erland Sommarskog]

Re: Appending tables

Shwetabh (shwetabhgoel@g mail.com) writes:[color=blue]
> Well I cant really do anything about the design for table2 because that
> is the way the client wants it to be.
>
> As far as table3 goes, I wanted to check the possiblility of appending
> the
> records of all the tables into a single table and check the performance
> and
> efficiency. I understand that table3 is a poor database design
> but the main motivation for doing this is to check the reaction of the
> application
> which will using such database.
>
> Also, till now, I have converted DBASE database into SQL database using
>
> OPENROWSET to import the data, I was wondering if the same database
> can be entered into a new table which can hold the data from all
> tables.
>
> Any ideas?[/color]

For it to be meaningful to merge table1 and table2 into one table,
there must be some relation between the data. Is there any such relation?

In your sample data PRT1 went with CAT1, but you did not indicate what
rule said that these two should go together.

If you don't know what you want, we will not know either.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shwetabh]

Re: Appending tables


Erland Sommarskog wrote:[color=blue]
> Shwetabh (shwetabhgoel@g mail.com) writes:[color=green]
> > Well I cant really do anything about the design for table2 because that
> > is the way the client wants it to be.
> >
> > As far as table3 goes, I wanted to check the possiblility of appending
> > the
> > records of all the tables into a single table and check the performance
> > and
> > efficiency. I understand that table3 is a poor database design
> > but the main motivation for doing this is to check the reaction of the
> > application
> > which will using such database.
> >
> > Also, till now, I have converted DBASE database into SQL database using
> >
> > OPENROWSET to import the data, I was wondering if the same database
> > can be entered into a new table which can hold the data from all
> > tables.
> >
> > Any ideas?[/color]
>
> For it to be meaningful to merge table1 and table2 into one table,
> there must be some relation between the data. Is there any such relation?
>
> In your sample data PRT1 went with CAT1, but you did not indicate what
> rule said that these two should go together.
>
> If you don't know what you want, we will not know either.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

Hi,

Please disregard the sample data for now. I had missed a field in table
2 there.
Let's look at the schema I have given for table 1,2,3. Here, I am able
to create
table 3 programmaticall y and it successfully creates all the columns in
table 1
and table 2.

As for the rules, let me make it clearer. The application (EasyLabel
from www.tharo.com) will be using this database. This application is
used to create labels. Each label consists of various components like
barcodes,images etc. The data for this label is mapped to the fields in
the database. The user enters the Part_num (which is unique for all
records) and the software retrieves the fields *which* are mapped from
the database and loads them in the label. In case there are some other
fields which are not mapped to the database, the application does not
care about them. In other words, they are as good as blank.

Now, what I have in mind is to create a table such that all Database is
stored in it. Since
the application will load only those fields into the labels which are
mapped, it wouldnt create a problem.
Now if I have 100 records in table 1 and 10 records in table 2, the
table 3 should have 110 records at the end of operation. This table
alone will be accessed by the application to do its work.
My question is how to do this? I mean how can I take the records from
table 1, put them in table 3, then take the records from table 2 and
put them in table 3 , and so on?

Awaiting replies,
Regards,
Shwetabh

[Shwetabh]

Re: Appending tables

I am expecting 110 rows.

All i want is that first all records from table 1 are added to table 3.
Then all records from table 2 are added to table 3.
The fields which are not present in table 1 or table 2 are left blank.

One more question, if I have a table in SQL,
can I alter a field to make it primary key?
Or do I have to to it while creating the table itself?

[Erland Sommarskog]

Re: Appending tables

> Now, what I have in mind is to create a table such that all Database is[color=blue]
> stored in it. Since
> the application will load only those fields into the labels which are
> mapped, it wouldnt create a problem.
> Now if I have 100 records in table 1 and 10 records in table 2, the
> table 3 should have 110 records at the end of operation. This table
> alone will be accessed by the application to do its work.
> My question is how to do this? I mean how can I take the records from
> table 1, put them in table 3, then take the records from table 2 and
> put them in table 3 , and so on?[/color]

To make a completely wild guess, this may be what you are looking for:

INSERT tbl3(PART_NUM, PART_NAME, DESC1, DESC2)
SELECT PART_NUM, PART_NAME, DESC1, DESC2
FROM tbl1

INSERT tbl3(PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2)
SELECT PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2
FROM tbl2 a
WHERE NOT EXISTS (SELECT *
FROM tbl3 WHERE a.PART_NUM = b.PART_NUM)

UPDATE tbl3
SET CAT_NUM = b.CAT_NUN,
CAT_NAME = b.CAT_NAME,
SDESC1 = b.SDESC1,
SDESC2 = b.SDESC2
FROM tbl3 a
JOIN tbl2 b ON a.PART_NUM = b.PART_NUM
[color=blue]
>One more question, if I have a table in SQL,
>can I alter a field to make it primary key?
>Or do I have to to it while creating the table itself?[/color]

You cannot alter the field to make it a PK, but you can alter the table
to define a PK, if it does not have one. And PK can have more than one
column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shwetabh]

Re: Appending tables


Erland Sommarskog wrote:[color=blue][color=green]
> > Now, what I have in mind is to create a table such that all Database is
> > stored in it. Since
> > the application will load only those fields into the labels which are
> > mapped, it wouldnt create a problem.
> > Now if I have 100 records in table 1 and 10 records in table 2, the
> > table 3 should have 110 records at the end of operation. This table
> > alone will be accessed by the application to do its work.
> > My question is how to do this? I mean how can I take the records from
> > table 1, put them in table 3, then take the records from table 2 and
> > put them in table 3 , and so on?[/color]
>
> To make a completely wild guess, this may be what you are looking for:
>
> INSERT tbl3(PART_NUM, PART_NAME, DESC1, DESC2)
> SELECT PART_NUM, PART_NAME, DESC1, DESC2
> FROM tbl1
>
> INSERT tbl3(PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2)
> SELECT PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2
> FROM tbl2 a
> WHERE NOT EXISTS (SELECT *
> FROM tbl3 WHERE a.PART_NUM = b.PART_NUM)
>
> UPDATE tbl3
> SET CAT_NUM = b.CAT_NUN,
> CAT_NAME = b.CAT_NAME,
> SDESC1 = b.SDESC1,
> SDESC2 = b.SDESC2
> FROM tbl3 a
> JOIN tbl2 b ON a.PART_NUM = b.PART_NUM[/color]

Thanks, but I found out another way to get the job done.
[color=blue]
>[color=green]
> >One more question, if I have a table in SQL,
> >can I alter a field to make it primary key?
> >Or do I have to to it while creating the table itself?[/color]
>
> You cannot alter the field to make it a PK, but you can alter the table
> to define a PK, if it does not have one. And PK can have more than one
> column.[/color]

Agreed, I can use
ALTER TABLE <tablename> ADD PRIMARY KEY (<fieldname>);
to alter the table and define a PK. But it works only if the
<fieldname> is
NOT NULL. Is there any way I can alter the table to make the field NOT
NULL?
[color=blue]
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Erland Sommarskog]

Re: Appending tables

Shwetabh (shwetabhgoel@g mail.com) writes:[color=blue]
> Is there any way I can alter the table to make the field NOT
> NULL?[/color]

ALTER TABLE ALTER COLUMN


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx