data migration

Hi all.
I need to to a lot of data migration from excel spreadsheets, access
databases, and SQL Server databases into a SQL Server database.
In the past I would have achieved this using a combination of linked
servers and ad hoc connections using opendatasource(). However my new
site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
that I may have to come up with a different way to do the migration.
Can anyone let me know any other ways (other than linked servers and
DTS - which is not flexible enough for my situation here) that I can
get data from Excel and Access into SQL Server?
No idea is too simple.
Thanks,
Josh.Hi Josh,
Well I tend to create insert statements in the excel sheet or access query.
That always works for me
Statement formula's usually look like
="insert into Table(Col1,Col2,Col3) values('" & a1 & "'," & b1 & ",'" & c1 &
"')"
then I copy the values into notepad and then into ISQL
I hope this helps
--
Greg O
Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com
Document all your databases now.
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab queries from SQL Server.
http://www.ag-software.com/xp_ags_crosstab.aspx
Edit Extended Properties as easy as can be.
http://www.ag-software.com/ags_SSEPE_index.aspx
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.com...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.|||Hi,
Easiest and fastest way is using DTS to move data from Access and Excel. If
DTS is really not feasible then save the Access / Excel files to a comma
seperated csv / text file and then use "BULK INSERT" to load the data into
SQL Server.
Thanks
Hari
MCDBA
"Josh White" <whitegoose@.inorbit.com> wrote in message
news:aafea0a8.0404201812.196002df@.posting.google.com...
> Hi all.
> I need to to a lot of data migration from excel spreadsheets, access
> databases, and SQL Server databases into a SQL Server database.
> In the past I would have achieved this using a combination of linked
> servers and ad hoc connections using opendatasource(). However my new
> site is having trouble with Microsoft.Jet.OLEDB.4.0 and I'm worried
> that I may have to come up with a different way to do the migration.
> Can anyone let me know any other ways (other than linked servers and
> DTS - which is not flexible enough for my situation here) that I can
> get data from Excel and Access into SQL Server?
> No idea is too simple.
> Thanks,
> Josh.