Imports data from MySQL Server; works best with mysql driver 3.51.06! You'll have to create a dsn and then a linked server to that dsn. The script checks if the table exists in the mysql catalog (defined in the dsn properties). Too bad it needs administrative privileges to run (dbcc statement) - my advice: don't run it without dbcc!
It imports by default the mysql table in the cleaning database - need to create it manually or simply modify the script with a desired databaase!
usp_import_from_mysql
/*
Autor: Tudor Sofron
Mail: tsofron@cluj.astral.ro
Data: 07.01.2005
Params: @tablename = table name we wish to import
@server: default DB1_GLOBALE_PANEL
Example: --usp_import_from_mysql @tablename = 'becheck' or
--usp_import_from_mysql @tablename = 'becheck', @server = 'DB1_GLOBALE_PANEL'
Version: 1.0
*/if exists (select name from sysobjects where name = 'usp_import_from_mysql' and type = 'P')
drop procedure usp_import_from_mysql
go
create procedure usp_import_from_mysql
@tablename varchar(30),
@server varchar(30) = 'db1_globale_panel'
as
set nocount on
declare @sql varchar (5000)
set @sql = 'sp_tables_ex @table_server =' + @server +', @table_catalog = bogdan, @table_name =' + @tablename
if object_id('tempdb.dbo.#tables') is not null
drop table #tables
create table #tables
(table_cat varchar(200),
table_schem varchar(200),
table_name varchar(200),
table_type varchar(200),
remarks varchar(200)
)
insert into #tables exec (@sql)
if (@@rowcount = 0)
begin
raiserror ('The desired table does not exist on the chosen server/ please check name!', 16, 1)
end
else
begin
dbcc traceon (8765)
set @sql = N'if object_id (''cleaning.dbo.' + @tablename + ''')' + 'is not null'
set @sql = @sql + N' drop table cleaning.dbo.' + (@tablename)
exec (@sql)
set @sql = N'select * into cleaning.dbo.' + @tablename + ' from openquery (db1_globale_panel, ''select * from ' + @tablename +''')'
exec (@sql)
print 'Table imported successfully. Please verify'
drop table #tables
end