Combining three tables to one table.

[Work23]

Hello. I have three tables within my database. Table 1, Table 2, Table 3. I want to merge all three tables into one table called FinalTable. Table 1 has 7 fields, while Table 2 and Table 3 only have 5 fields. I would like to know how to create this new table with all the fields being displayed in the table. The extra two cells without any data from table 2 and table3 because they only have 5 fields stay blank. I'm using microsoft sql server. How would I go about this. Thank you so much!

 

[Pattycake245]

If the first 5 field in all three tables are the same and you want the two fields that are not in tables 2 an 3 to be blank, then this should work

insert into FinalTable
select field1, field2, field3, field4, field5,
field6, field7 from table1
union
select field1, field2, field3, field4, field5
'' as field6, '' as field7 from table2
union
select field1, field2, field3, field4, field5
'' as field6, '' as field7 from table3

 

[Work23]

Thank you. I am confused about the quotation marks around AS field 6? What does that actually mean. And what does field 7 function as. I am a bit confused. Thank you very much!

 

[Pattycake245]

If you are inserting into a table, the number of columns you are inserting must match the number of columns in your destination table. So, since table 2 and 3 are two columns short I just created two blank columns and gave them alias names. You may not even need the alias names just the quotes. The alias names are just written out of habit.

I am basically saying create a column called field6 and assign it a value of '' for every row. If you didn't create these two columns, you would get a "number of columns does not match" error.

Hope this clears things up a bit

Tim

 

[Work23]

Thank you very much Pattycake245. That makes a bit more sense. I think I'm still confused on how to modify my code programmatically. I do get that "number of columns does not match" error before I tried you suggestion. I actually am not able to get that to work. Here is the code. Please let me know what you think. Thanks so much! (The first SELECT bunch is from the table with 7 fields. The other two SELECT bunches have only 5 fields.)


INSERT INTO FINALTABLEMERGE
SELECT TFINAL_EVENTID,
TFINAL_EVENTNAME,
TFINAL_SESSIONID,
TFINAL_TIMESTAMP,
TFINAL_USERID,
TFINAL_SOLDTO,
TFINAL_LOCALE
FROM DBO.LOGINFINAL
UNION
SELECT TEVENTID,
TEVENTNAME,
TSESSIONID,
TTIMESTAMP,
TEVENTVALUE


AS " ",
AS " "


FROM DBO.PAGEVIEWEDFINAL
UNION
SELECT
TEVENTID,
TEVENTNAME,
TSESSIONID,
TTIMESTAMP,
TEVENT


AS " ",
AS " "


FROM DBO.LOGOFFINAL

 

[r937]

INSERT INTO FINALTABLEMERGE
SELECT TFINAL_EVENTID
     , TFINAL_EVENTNAME
     , TFINAL_SESSIONID
     , TFINAL_TIMESTAMP
     , TFINAL_USERID
     , TFINAL_SOLDTO
     , TFINAL_LOCALE
  FROM DBO.LOGINFINAL
UNION
SELECT TEVENTID
     , TEVENTNAME
     , TSESSIONID
     , TTIMESTAMP
     , TEVENTVALUE
     , ''
     , ''
  FROM DBO.PAGEVIEWEDFINAL
UNION
SELECT TEVENTID
     , TEVENTNAME
     , TSESSIONID
     , TTIMESTAMP
     , TEVENT
     , ''
     , ''
  FROM DBO.LOGOFFINAL

an empty string, two single quotes in a row, not doublequotes

of course, this is all contingent upon field6 and field7 being character type fields

personally, i'd use NULL, but this too might cause problems if field6 or field7 is declared NOT NULL

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Work23]

Thanks a lot r937. That helped a lot. Have to do some fine tuning now, but that is what I basically needed. Thanks so much!