Merge 2 tables

[Kendel]

Hi All,

I have 2 tables seat on my DB. They are not the same structure but they do have some similar columns. Now I want to merge the 2 tables together. I don't want to insert all records from table 1 to table 2 because I don't to get the dups. I How can I do that?

Many Thanks.

 

[mercwrought]

depending on how you are doing this you could

insert both table into a temp table
truncate the table that they are going in and then
select distinct from the temp table


just a thought

 

[Kendel]

Sounds like a solution to me.

So I have to do something like:

Select * from table1 Insert to temp
Select * from table2 Insert to temp
Drop table1
Drop Table2
Select distinct * from temp Insert to MyNewTable
Drop temp?

The syntax part will take me a couple day. I'm not a DBA.

Thanks for your help.

P.S. The number of columns on each table are not the same, I can use the same logic, correct?

 

[JayKusch]

Don't think that will work either since your post contained the following item

They are not the same structure ...

You would need 2 temp tables. Each one reflecting the schema of one of the live tables ... but then you would be back to square one with 2 tables having diffrent schemas(table structures).



Thanks

J. Kusch

 

[MDXer]

if you want all the distinct columns in 1 and 2 in your temp table you need to create the temp table prior to population then you can use this to to select into your new table.

Select Distinct
Column_Name
From Information_schema.columns
where table_name in ('table1','table2')
order by Column_Name

will give you the Distinct column names

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mercwrought]

have to aggree with J,the temp table would have to be created with columns that would support the data from both tables or that the data would be converted as it was transfered. then it can be selected out.

 

[Kendel]

Thanks all. That's complicated!

 

[Kendel]

Can you suggest any online reading about DTS? I can google and get a bunch of them but I don't know which one is good. Thanks you everyone.

-kendel

 

[JayKusch]

This is a good place to look around ...

http://www.sqldts.com/

Thanks

J. Kusch

 

[Rafic]

HOw to merge LOtus Notes,Sql, Access database to Sql server 2000 database.
Rafic