I go through the DTS wizard and specify what table to import from
Access to SQL.
It seems to want a table already created? If I have a col FName then,
the data from the Access table appears in the SQL table.
If I create a query within DTS wizard and specify which cols to import
and have something close to a match I get all NULL values.

All I want to do is import table structure and data from Access to
SQL. Can anyone help?

thanks

When you get to the part where it says

Choose your source table(s) and now your destination. To the right of that
is a column called "transform". If you click on there you can play with
what the destination will look like and how the data will be mapped.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <the_ainbinders (AT) yahoo (DOT) com> wrote

How can I do this where I need merge records from Access with SQL and
maintain the sequence of the primary key?

Rob



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Also, not insert records from Access that are already in SQL?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The wizard will only get you so far. For your situation

Access --> SQL Server
If record in SQL Server does not exist INSERT it
If it does exist UPDATE it
Maintain PK from Access through to SQL Server

1. Copy the table over in it's entirety to SQL Server (Or use a linked
server)
2. Use a series of TSQL statements.

INSERT INTO <<SQL Server>>
SELECT <<fields>> FROM <<Access>> LEFT OUTER JOIN <<SQL Server>> ON
KeyColumns
WHERE <<SQL Server>>.KeyColumn IS NULL

UPDATE<<SQL Server>>
SET <<SQL Server fields>> = <<Access Fields>>
FROM <<SQL Server>> JOIN <<Access>> ON KeyColumns

Maintaining PK column depends on how the PK is defined in SQL Server. If it
is an identity then you will need to all IDENTITY_INSERT





--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rob" <the_ainbinders (AT) yahoo (DOT) com> wrote