data import tool

I'm trying to find a way to import data from a local access database into a
remote sql server database where the databases are similar and of the same
product (but different versions) and different enough with constraints, keys,
columns, that I can't use the SQL Server Import and Export Wizard (with my
current knowledge of it) to map table columns without things like null value
errors and adding insert values to disparate columns while doing a bulk copy.
So what I'm looking for is a tool that is a combination of an sql insert
statement and the bulk copy import wizard found in the SQL Server Management
studio.
The differences between product versions require more flexibility than I can
currently find without doing everything programatically with ado.net.
Any advice regarding this data migration greatly appreciated - thanks.So far the easiest way I can think of is to pre-modify the access tables
before I do an import. This is working - however now I'm looking for a way
for the sql server manager interface to remember all the table imports and
mappings - can everything be saved somehow to some kind of configuration file
in SQL Server Management Studio?
"segue" wrote:
> I'm trying to find a way to import data from a local access database into a
> remote sql server database where the databases are similar and of the same
> product (but different versions) and different enough with constraints, keys,
> columns, that I can't use the SQL Server Import and Export Wizard (with my
> current knowledge of it) to map table columns without things like null value
> errors and adding insert values to disparate columns while doing a bulk copy.
> So what I'm looking for is a tool that is a combination of an sql insert
> statement and the bulk copy import wizard found in the SQL Server Management
> studio.
> The differences between product versions require more flexibility than I can
> currently find without doing everything programatically with ado.net.
> Any advice regarding this data migration greatly appreciated - thanks.|||You can always do it with ADO.NET. But it may be simpler to just import the
access data as is into a staging SQL Server table using the Import Wizard,
and then write T-SQL script to modify the data to your specs.
Linchi
"segue" wrote:
> So far the easiest way I can think of is to pre-modify the access tables
> before I do an import. This is working - however now I'm looking for a way
> for the sql server manager interface to remember all the table imports and
> mappings - can everything be saved somehow to some kind of configuration file
> in SQL Server Management Studio?
> "segue" wrote:
> >
> > I'm trying to find a way to import data from a local access database into a
> > remote sql server database where the databases are similar and of the same
> > product (but different versions) and different enough with constraints, keys,
> > columns, that I can't use the SQL Server Import and Export Wizard (with my
> > current knowledge of it) to map table columns without things like null value
> > errors and adding insert values to disparate columns while doing a bulk copy.
> >
> > So what I'm looking for is a tool that is a combination of an sql insert
> > statement and the bulk copy import wizard found in the SQL Server Management
> > studio.
> > The differences between product versions require more flexibility than I can
> > currently find without doing everything programatically with ado.net.
> >
> > Any advice regarding this data migration greatly appreciated - thanks.