I want to copy a table of data from a source SQL Server to a destination SQL
Server. The destination will be empty. The destination may not have some
fields that the source has, but the source will have all the fields the
destination has. DTS seems to want to name every field, but I want to do an
insert without having to name the fields every time. Is there a way to
insert all the fields "Insert Into NewDatabase.myTable Select * From
OldDatabase?

If you are using one database then you should be able to use a SQL Task to
create a table & run your Insert statement.
If you want to move your data from one database to another you cannot do as
you propose, because you need multiple connection objects. If needed run a
SQL Task to create the table with the connection associated with the
destination. Then create & run a Transform Data Task to copy the data from
the source connection to the destination connection. Your Transform Data Task
can use a 'Select * from table' or the entire table as the source. The
Transformation will then insert the fields based on the transformation(s)
into the destination database.

For a good primer on DTS go to SQLDTS.com. An excellent source of
information & examples.

"Derek Hart" wrote: