INSERT INTO table with only fields that exist

[dl000]

Hi,
I have two tables, t1 and t2. I want to insert all the records from t1 into t2 but the tables have differing fields. t1 has all the fields that t2 has but it could possibly have extra fields too. Is there a SQL statement to do this? Thanks.

 

[SecondToNone]

Do you want those extra fields in t1 too??

If not you can just do a UNION ALL query

-SecondToNone

 

[r937]

insert 
  into t2
     ( col_t2_one
     , col_t2_two
     , ... )
select col_t1_a
     , col_t1_b
     , ...
  from t1

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

 

[dl000]

I do not want the extra fields. Another note: The fields in t2 could differ for each query.

Is it possible to do a UNION ALL if the number of fields in the two tables are different?

 

[lespaul]

the number of fields in the table don't matter, it's the number of fields you are declaring in your SELECT statement:

THis will work:

SELECT F1, F2, F3 From someTable
UNION
SELECT F4, F5, F6 From SomeOtherTable

This will not:

SELECT F1, F2, F3 From someTable
UNION
SELECT F4, F5, F6, F7, F8, F9 From SomeOtherTable

In order to make the second query work you need "placeholders":

SELECT F1, F2, F3, "", "", "" From someTable
UNION
SELECT F4, F5, F6, F7, F8, F9 From SomeOtherTable

HTH

leslie

 

[r937]

i don't see what all this talk of UNION is for

dl000 clearly wanted to insert some rows from t1 into t2



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

 

[lespaul]

I thought we were trying to help him get just the information he wants into the table.

I want to insert all the records from t1 into t2 but the tables have differing fields

What does differing fields mean?

t1 has all the fields that t2 has but it could possibly have extra fields too

which table (it) could have extra fields?

Which fields do you want to keep?



Leslie

 

[dl000]

Sorry. I guess I should be more clear. Here's the SQL statement I'm using right now. INSERT INTO Table2 SELECT * From Table1

The problem with this is that in future versions of the software, we might want to add fields to Table1 but we still want people using the old version of the software, which has the old Table2 to be able to run this statement. So I just want to insert the fields that exist in Table2. As you can see, we cannot reference the names of the fields because we do not know how many fields are in Table2, because they may be using the old version or the new version.

 

[JonFer]

Write a function that takes two table names as parameters and build the INSERT statement dynamically by matching the field names in the Fields collection of the 2 TableDefs.


John

http://www.interautomateconsulting.com