Importing SQL File From MySQL
Jul 25, 2006
Is there a specific way i shoudl setup the way i export my tables tfom mysql to import them into MSSQL? Im exporting it to .sql and i have tried a bunch of different options but every time i try to import the .sql file to MSSQL i get stuff like this
There was an error importing the database. The status of the import is unknown.
Incorrect syntax near '='.
Incorrect syntax near '`'.
also... how hard would it be to import a .TAB file (which is a csv file but uses tabs instead of commas)?
View 14 Replies
ADVERTISEMENT
Jul 26, 2006
Check SQLServer 2005 feature comparison Express doesn't even have Integration Services (DTS successor)
View 3 Replies
View Related
May 30, 2008
Hi, I am relatively new to SSIS so please bear with me. Basically, I'm looking to import data from a MySQL database table on a regular basis. However, I only want to import data since a certain date or time which will be stored in a table in an SQL Server database, so need to look to this table for the criteria. Can anybody tell me the best way to do this?
View 1 Replies
View Related
May 10, 2004
Hi all!
I'm trying importing a MySQL database to my MSSQL server. I've donwloaded the MySQL ODBC drivers and I can connect succesfully throught a DSN to my MySQL server. Everything works fine EXEPT my BLOB field. I'm using a BLOB field for storing a binary file.
When I choose preview while importing my bob field is empty.
I'm desperate!
:eek:
View 1 Replies
View Related
Feb 13, 2008
Please help - I am dealing with data transfer from multiple tables to multiple tables - what is the best practice to get this done? So far I have my connection to MySql figured out and I am using a DataReader source to get the data. What is the approriate Data Flow Transformation to pump data into three different tables in MS Sql 2005 database from three different tables in MySlq database? So far I was able to figure out how to do this for one table only...
Thanks,
Galina
View 12 Replies
View Related
May 15, 2006
good morning,
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Best regards.
View 1 Replies
View Related
Aug 4, 2006
We want to migrate a mySQL database to sql server 2000 or sql Server 2005. I have been given a DDL file to perform the conversion, but I don't know what to do with the file? Can anyone help me out? From what I can conclude, the DDL files is a script. So how do I run this script? Where do I place the file, before I run it?
Please help !
View 2 Replies
View Related
Apr 14, 2004
Hi,
I would preciated if some could help me with this problem.
My have used Mysql database and now i have to change my database to sql server.
I have made a dump file from mysql db and i would like to insert that dumb file to sql server.
Have any idea how it is possible?
cheers,
inkku
View 1 Replies
View Related
Feb 12, 2007
Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'
Many Thanks
David Hills
View 7 Replies
View Related
Jun 22, 2007
Hi i'v e installed the file: SQLServer2005_DTS.msiBecause i've heard that i need that to import a csv file into a ms sql database. now i have no idea how to work it, like to even make it open.Where would i open it from? I reckon i'v eread enough literature about using it, to be able to have a bash... thanks in advance...
View 3 Replies
View Related
Oct 5, 1999
Hi, this is my first time and was wondering what's the best way to do the following:
i've sql server 6.5. also have a sql database(.DATfile) in a folder.
i need to import this database to my server. should i just LOAD/import it? or do i need to first create a database device on my server(how much size should i allocate etc) and then load it??
thanks a lot
rohit
View 3 Replies
View Related
Jun 24, 2002
Hi guys,
I have to write a Store procedure which will pickup a txt file from a destination, read it and update some table.
How to pick and read a txt file in a Stored procedure.
Thanks in advance
Vineet
View 1 Replies
View Related
Jun 11, 2007
Hi,
i am having problems while importing a .csv file.
when i import a .csv file in sql server I get the folowing error message.
"Cannot create an OLE DB accessor. Verify that the column metadata is valid".
Any help would be appreciated
View 3 Replies
View Related
Jul 23, 2005
I have a .txt file that I need to add to an existing table, which ismade up of Varchar, Char, numeric and int fields.What is the best way to do it.The first thing I tried was importing the .txt file, and then goinginto the design and changing the field type, but I hit problems on theNumeric fields.Then I tried changing the field types from varchar to different typesat the transfom stage of importing, but that failed too.Regards,Ciarán
View 2 Replies
View Related
Nov 22, 2007
I have a CSV file that i need to import into a SQL table. The problemis the values in the first column are not brackited in "". There areover 700K rows. Is there an easy way to fix the data or have SQLcorrectly import the the data?The data looks like this1, "xxx","zzz"2, "aaa","bbb"an so on...Thanks
View 1 Replies
View Related
Sep 19, 2006
Hi all-
I am in need of some help importing a .CSV file into a SQL Server 2005 Express database. I can't use SSIS because it's SQL Server Express. The operation needs to run as a parameterized Stored Proc which I will call from ASP.NET.
The problem is that I need to get the only 2 columns from the text file, then "tack on" 3 more columns that will have data that changes, which will come in as parameters to the T-SQL stored proc. This prevents me from using a straight BULK INSERT.
The data in each of these columns will be the same for each record in that column - that is, every record in every field in column 3, for example, will be the same.
Some of the files will have 4 million records and up, so speed is of the essence here. I tried using BULK INSERT to dump the data into a #temporary table - which took 38 seconds and was acceptably fast - but then, my next step was to dump the additional data into the other columns using UPDATE... SET. I gave up on this after the query ran for THIRTY MINUTES! My next step was going to be to move the data from the temporary table into the target permanent table somehow, but I never got that far when I saw how long the previous step took...
It's a little odd, because I can do the same thing in MS Access in under 5 minutes by using a SQL statement like this in my ASP.NET code:
"INSERT INTO [Target_Table] (field1, field2, field3, field4)" & _
"SELECT F1, F2, '" & strSource & "', Now() AS DateTimeStamp " & _
"FROM [Text;HDR=NO;DATABASE=" & strPath & ";].[" & strFilename & "]"
strSource is a string that the User enters (properly vetted for security); strPath and strFilename are strings holding the path and filename to the .CSV file. I create a unique filename from the file the user uploads. This works in under 5 minutes for several millions of records in MS Access, as I said above. I've had no luck getting anything similar to work in SQL Server, though.
Anyway, does anyone have any ideas? This is somewhat urgent, as the project was about to go "live" when it was discovered that the actual, live data had grown to the point where Access couldn't hold it, and a move to SQL Server Express was necessary.
Thanks in advance,
-Andrew
View 9 Replies
View Related
Sep 19, 2005
I need to be able to create a DTS package that imports a CSV file which is loated at URL. I.E. HTTP://www.url.com/csv/thefile.xls I tried copying the URL an pasting it in the file location when in SQL wazird but I got an error message.
View 1 Replies
View Related
Jun 4, 2007
I'm a newbie to SSIS, so there is probably an easy solution:
I'm importing from a csv file into a db table, but would like to remove the quotation marks from around the data before inserting into the table.
Are there any suggestions of how to remove the "data" marks? Would a Foreach Loop container work for this?
View 1 Replies
View Related
Jul 31, 2007
How do i import a Excel file into a table i have created in my database in SQL server 2005???
View 10 Replies
View Related
May 1, 2008
Happy Thursday all,
I am importing a text file to sql and most of my fields look like this:
"M","NEW ADDRESS",
and my other field looks like this:
"firstname Lastname"
but I need it like this:
"firstname", "Lastname"
Can anyone help me understand a better way of making this happen?
Thanks in advance
View 2 Replies
View Related
Jul 22, 2004
Can someone please help me.
I need to import a csv fiel to sql server and I know that the column delimiter is
and the newline delimiter is but I don't know what the rowterminator is or the fieldterminator. How can I import the file into an empty table in an existing database.
Any suggestions would be greatly appreciated.
View 1 Replies
View Related
Sep 15, 2004
HI Guys,
I am doing the following to read the data in a text file and inserting it into SQL.
1) Open db connection
2) Open Text File
3) loop through text file all along inserting each row into the db
4) close the text file
5) close the db connection
However, the text file has over 400 rows/lines of data that need to be inserted into the db. Each line in the text file is a row in the db. At anyrate, the above script times out. Is there a better, faster way to do this? I can't use Bulk Insert due to permissions previlages.
Thanks in Advance!
View 2 Replies
View Related
Sep 4, 2001
Hi all,
Please let me know if it is possible to import an Oracle .dmp binary file into SQL using DTS. DTS has ODBC and OLE DB drivers for Oracle but both require specifying the Oracle database name, user name and password. i.e., it looks like the only way to import the .dmp file into SQL would be to first load the .dmp file into Oracle and then tranfer it from Oracle to SQL using DTS. Is there any way to load the .dmp file directly into SQL?
The .dmp file is a binary file that has been generated from Oracle using the 'Export' utility.
Thanks in advance,
-Praveena
View 2 Replies
View Related
Dec 6, 2000
I have large flat files that I need to import using DTS. The record counts are between 500,000 and 3000,000. Or, 16 to 116 MB. When creating the DTS package I need to use to import, the wizard is haulting at the point where I should place the fixed points for the fields. I thinks it is because, the wizard will only recognize excel or text files. I cannot save these files as either txt or xls, because of their size. does anybody have any suggestions?
View 3 Replies
View Related
May 19, 2004
I am building a aspx/c# application with SQL Server 2000 backend. Now i want to have the option for "Importing" the data into one of the tables in my database.
The source file for the import is a text file , CSV format. I want the users to click on the "Import" button placed on my webform and supply the souce file and the data should get imported into the SQL Server 2000 database table.
I want to know the various ways to implement this. Is it possible to invoke the DTS and then DTS will itself guide the users do the import? or if i need to write a SQL query , what would that be like??
View 2 Replies
View Related
Feb 14, 2007
I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need
The user needs a function to import Contact data from a txt/csv file into the Contact Table
Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields
Then i need to use those parameters and run some sort of import routine putting the data into the specific table.
The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above
How do I do this using MS SQL??
I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.
Is this the correct command to use??
Do you know any websites that can point me in the right direction??
I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need
The user needs a function to import Contact data from a txt/csv file into the Contact Table
Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields
Then i need to use those parameters and run some sort of import routine putting the data into the specific table.
The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above
How do I do this using MS SQL??
I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.
Is this the correct command to use??
Do you know any websites that can point me in the right direction??
Hope all this makes sense
Thanks
View 1 Replies
View Related
Feb 14, 2007
I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need
The user needs a function to import Contact data from a txt/csv file into the Contact Table
Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields
Then i need to use those parameters and run some sort of import routine putting the data into the specific table.
The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above
How do I do this using MS SQL??
I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.
Is this the correct command to use??
Do you know any websites that can point me in the right direction??
Thanks
View 2 Replies
View Related
Feb 27, 2008
Hello Everybody,
I am importing an csv file into Sql Server straight forward no data cleansing or anything and i noticed everyday it misses the last row in the CSV file.
Has anybody come accross this if so is there anything that i can do to get that row.
ANY HELP ON THIS IS GREATLY APPRECIATED.
Thanks In advance....
View 5 Replies
View Related
Apr 18, 2012
I have a stored procedures that will import 28 CSV files with different columns layout for each one, into their own table in SQL Server 2008 and a master stored procedure which will execute the other 28 store procedures.
All the CSV files are stored on the network drive in one folder.The naming of these files will vary from week to week and I don't want to manually change the names in the stored procedures. Each table name has a unique Identifier F1 to F28 that will be a constant before the rest of the name for example ...
F1_country23
F2_region12
Can I use the unique Identifier to add an wildcard or variable to the stored proc so it will pick any file in the folder for example with "F1" or "F2" meaning format layout 1 or 2 and use the correct stored procedure like below?
WHEN 'F1' THEN 'usp_F1_ImportScript'
WHEN 'F2' THEN 'usp_F2_ImportScript'
Example SQL scripts so far ...
Code:
Create PROC [dbo].[usp_F1_ImportScript]
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CSVTest]') AND type in (N'U'))
DROP TABLE dbo.CSVTest
-- Create a new template table
[Code] ....
the SP which will run the 28 SP
Code:
CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255))
AS
--check if @FileName is null
IF @FileName IS NULL
BEGIN
SELECT 'No Files Match' AS ERROR
[Code] .....
I cannot use SSIS because other users from different countries (50+ so far) may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and with documentation any user without experience with MSSQL would be able to make simple changes.
View 1 Replies
View Related
Feb 18, 2004
Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;
View 5 Replies
View Related
May 6, 2004
I am trying to import some data from a csv file on a regular basis. This file, however, is stored on a website. I can't seem to find a way, either using DTS or a stored procedure to make this task simple. If the file is at http://www.mysite.com/import.csv is there anyway I can get DTS to download that file and import it into a new table?
Thanks,
Scott
View 3 Replies
View Related
Jul 27, 2006
Hi
How can i import data from a csv file or text file
i have the text file located at C:Imports/Importdata.csv
the table that i have is as follows:
CREATE TABLE Invoice
(
InvoiceID INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)
i dont have any data for the invoiceID is it possible to import the data from the csv file .... the csv file partially is shown below...
Invoice_no, RefText, Customer details
272727, treesale, Garstang
345667, TidyBox, northampton
thanks
View 11 Replies
View Related
Feb 14, 2007
I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need
The user needs a function to import Contact data from a txt/csv file into the Contact Table
Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields
Then i need to use those parameters and run some sort of import routine putting the data into the specific table.
The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above
How do I do this using MS SQL??
I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.
Is this the correct command to use??
Do you know any websites that can point me in the right direction??
Thanks
View 1 Replies
View Related
