Web Development

Ask something
Question Locked

Merging Two Tables

0 helpful
6 replies

by joshgamble

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I need to figure out what is the easiest way to do this is. I have two tables that have the following columns:

TABLE 1
AcctNbr, GiftRef, Gdate, PayCode, Amt

Table 2
GiftRef, Amt, MotvCode, FundId

I need to merge the two tables with GiftRef being the common table between them. I have exported the tables from MSSQL to Access and am wondering what the easiest way to do this is. Is there an SQL Query that can merge the two tables on export by GiftRef? Is it easier to do this in Access now that I've exported the two Tables? Any help on this would be awesome. Thanks.

6 replies

by tony hopkinson

unless you are stuck on MSDE with a command line interface

SQl wise

There are a few ways to go I wouldn't recomend changing your source tables you'll have to restore to get them back when you make an arse of it. Not a dig, I wouldn't trust myself to get this right first time.

Even if you get the structure right, sods law says there's going to a be a few records which cause problems.

Insert Into Table1 Select ... From Table2
is one way Table 1 must exist.

Even nicer for this sort of thing is

Select into Table3 From Table1 Where ....

If you don't like Table3 just delete it
Drop Table Table3 in sql

Bear in mind that you'll need to add keys indexes, cehck constraints et al, select into will just give you a bare table.

So you could as a first go try

Select t1.acctnbr,t1.Giftref,t1.Gdate,t1.Paycode,t1.amt,t2,movtCode,t2.Fundid
Into Table3
From Table1 t1, Table2 t2
Where t1.Giftref = t2.giftref

Note This dropts tabl2.amount and assumes
that there are no giftrefs in table1 that arent in tabel 2 and vice versa

Ths problem fellas I alluded to earlier

Deal with them or bin them.

I've no feel for your data, but it looks to me like there should in a normalised design be more than one table, in fact possibly three

Accounts
Transactions
Gifts, but the column names could be leaving me with some nasty assumptions there. :D

HtHs

by ddwcompute

If I read your question properly you want to merge these two tables into one table with GiftRef as the common field. I would try something like this pseudocode:

create table TABLE3 as
select GiftRef, AcctNbr, Gdate, PayCode, Amt as Amt1, "blank" as MotvCode, "blank" as FundId, 0 as Amt2 from TABLE 1
union
select GiftRef, "blank", "blank","blank", 0, MotvCode, FundId, Amt from TABLE2

by joshgamble

I was able to solve this by using join. Thanks for all the help. Listed below is an example of the SQL code.

SELECT A.AcctNbr, A.GiftRef, A.Gdate, A.PayCode, A.Amt,
B.Amt AS BAmt, B.MotvCode, B.FundId
FROM Table1 AS A
JOIN Table2 AS B
ON A.GiftRef = B.GiftRef

Credit to Plamen Ratchev from sqlstudio.com
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/e6cfed2943182b76/0ff6ef899f4a074c?lnk=gst&q=merging+two+tables#0ff6ef899f4a074c

by joshgamble

I didn't know exactly how to phrase it. I just needed to get the data from the SQL Database, export them to Access and bring them together. I was unsure the easiest way to do this since I'm new to this. I knew you could run commands on the code on export so I didn't know if that was the easiest time to join the tables or to export the whole two tables and merge them from within Access. I got the help I needed though. Thanks.

This discussion is closed to new replies.