Hot answers tagged

6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


6

You just need to add OPTIONALLY ENCLOSED BY '"': LOAD DATA INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; Documentation link. File: n1,s1,s23,n2,s4,s56 1,string1,"str2,str3",2,string4,"str5, str6" Table and test: mysql> create table tbl_name ( ...


4

SQLCMD can be used to export data. The default output is sometimes unwieldy for that, but you can use some command-line options to turn off the headers, trim trailing spaces, and to use a comma separator instead of the default space: sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^ -h-1 -W -s "," Returns: ...


4

I see you are attempting to use the Wizard to import data. You may be better off manually creating the table with the column definitions you desire, and using the T-SQL BULK INSERT command to import the data. USE tempdb; CREATE TABLE dbo.TestSource ( SomeNumber INT NULL ); I created a plain-text sample file named C:\temp\test.txt which contains ...


3

There is a known issue when certain checkpointing / flushing configuration results in stall. Here is a quote from Percona 5.5 documentation Improved InnoDB I/O Scalability: Normally, the checkpoint is done passively at the current oldest page modification (this is called “fuzzy checkpointing”). When the checkpoint age nears the maximum checkpoint age ...


3

Files checked with hex editor and answer revealed. My CSVs differed in that rows either terminated with: Hex 0D CARRIAGE RETURN (\r), or Hex 0D CARRIAGE RETURN (\r) AND 0A LINE FEED (\n) This is a very important line of code: LINES TERMINATED BY '\r\n' or LINES TERMINATED BY '\r' In my case, taking out the \n took care of it - either CSV ...


3

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


3

The constraints are kept for the renamed table. Drop them before you import the tables with the correct data. alter table t1 drop constraint c; There is also another way to do it if you do not want to lose the wrong data. You can create a new table based on the 2 tables: create table t1 as select * from t; Then you must disable the constraints and ...


2

Go to folder C:\wamp\bin\mysql\MYSQL_VERSION\bin Hit shift + right-click in that folder and select "Open command window here" from the menu that pops up. This will open a command window in the path you are already in, so C:\wamp\bin\mysql\MYSQL_VERSION\bin Enter any of the MySQL commands you like with any of the executables you find in that folder. To ...


2

I'm assuming based on the post that the PostgreSQL database server and the process restoring the dump are on the same machine. If so: Is it possible to pause the import of the database? Not really. You could SIGSTOP the pg_restore or psql process and/or the corresponding postgres backend, but I wouldn't consider that my first-choice option. If not, ...


2

You wrote 12c db has the exact data of 11g (expdp/impdp) But are you sure? 1) Did you make a consistent export of your tables? This means all table data is from the same point in time. For this it is sufficient that you stop all other write activity on the database during export. Or you set the flashback_scn parameter when using expdp or the ...


2

Of course you can put it up on Dropbox - or anywhere else. Depends on how secure you want your data to be? Do you want the NSA looking at it? Dropbox employees? I would put up an encrypted version of your mysqldump and securely transmit the key to your colleague - either face to face or over encrypted email, or have the password/key sent by courrier. ...


2

As per documentation your error is: Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition ...


2

No, that is not a possible way: Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it. You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not work. Even if there is ...


2

CSV files are not included in the dacpac that you deploy so you will need to make sure that you copy it with the dacpac and that if you use relative paths the working directory is what you think it is. What I do for extra files is set the "Build Action" of the file to "Copy if Newer", when the dacpac is built in the bin folder you will also have a "Data" ...


2

You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files): Linux or Mac OS X free and open source sudo pip install csvkit Example: csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql It creates a CREATE TABLE statement based on the file content. Column names are taken ...


2

I agree with Nawaz. WorkBench migration tool works very well, at least between MS-SQL and Mysql. Another solution is to use a Modeling tool like PowerDesigner or Embarcadero Data Architect Studio. Reverse engineer your schema from INformix, then switch the schema to Mysql. You can then generate the script and apply it to Mysql. For data export, you'll need ...


2

Give something like the below a try... You'll obviously need to plug in your variables for your environment, check the data types (may need to add logic to keep leading zeros?), change from the final temp tables to your regular table(s), etc. Works fine for me for import from XML files to temp tables without deleting the files afterwards but adding logic ...


2

This is actually very simple to do via SQLCLR. A Stored Procedure can be set up t read any xml files in a particular directory (or just as easily check all sub-directories) and output a single result set with all of their contents. Doing this, you could populate your table with the following query: INSERT INTO dbo.R000000 (R00000010, R00000020, R00000030, ...


2

Unfortunately that's not possible if you insist on using the wizard, you would need to edit the package using Visual Studio. There is an option 'keep null's' when editing the package in Visual Studio Given this .csv file: StringField,IntField a,1 b,5 c, d,6 e, f,8 g, h, i,10 j, and this table definition CREATE TABLE [dbo].[NullTest]( [StringField] ...


2

As your XML is held in a table you can use the methods of the XML data-type ( eg .nodes, .value, .query etc ). The thing you might struggle with a bit is namespaces in XML. Start here to learn more about them: Understanding XML Namespaces. Here are a few example queries I did against a spreadsheet similar to yours. Try them and work through the Stairway ...


2

You can process the data using regular T-SQL as part of the bulk loading process, using the OPENROWSET(BULK... function and the XML format file. INSERT <target> WITH (TABLOCKX) <target_column_list> SELECT <columns_and_expressions> FROM OPENROWSET ( BULK <data_file>, FORMATFILE = <xml_format_file>, ... ...


2

I suggest that this is a bit cleaner than Vérace's answer: LOAD DATA INFILE 'apa.txt' INTO TABLE Test_Stock_Daily FIELDS TERMINATED BY ',' (symbol, @date, open, high, low, close, volume) SET date = STR_TO_DATE(@date, '%m/%d/%y'); (And yes, date is a poor choice for a column name.)


2

Here is what I settled on: DELETE FROM Stock_Daily where symbol = 'APA'; LOAD DATA INFILE 'APA.TXT' INTO TABLE Stock_Daily FIELDS TERMINATED BY ',' (@date, open, high, low, close, @volume) SET date = STR_TO_DATE(@date, '%m/%d/%Y'), symbol = 'APA', volume = @volume/100; SELECT * FROM Stock_Daily WHERE symbol = 'APA' and date >= ...


2

Testing with a 8.1 instance, it doesn't look that there is a problem. See these 3 different examples: Create data SHOW standard_conforming_strings ; standard_conforming_strings ----------------------------- off CREATE TABLE bla(t text); INSERT INTO bla VALUES('abc''def'); INSERT INTO bla VALUES(E'abc\\def'); 1. Dump of 8.1 data with 8.1 pg_dump $ ...


2

The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable. For the weird header row, you can always specify that there is no header row and the ...


1

Looks like your oracle directory doesn't exists. You will need to log into the database, create a directory, and retry your import export ORACLE_HOME=/path/to/oracle/home export ORACLE_SID=YOUR_SID sqlplus / as sysdba create directory DUMPS as '/path/to/your/dump/files'; exit; And retry your import. Pending on the user you are using to do the import, ...


1

The answer appears to be yes. Set members are accepted if they are separated by comma, however there should be no space between the comma and the next set element. Since commas are used to separate set members, columns must be separated by a different character. I have used tabs, and this works well. Performance is greatly improved (roughly by a factor of ...


1

Your table is like this: CREATE TABLE billables ( billable_id "nextval"('"dm"."billables_billable_id_seq"'::"regclass"), account_id bigint NOT NULL, <<<<---- Can't be NULL... code character varying(64) NOT NULL, info "text", m_unit "measurement_unit", m_unit_custom character varying(64), unit_cost numeric(16,4), ...


1

According to the PostgreSQL documentation on the COPY command the values must be separated by the delimiter character which is by default a tab (\t). If spaces are used instead I get a similar error on PostgreSQL 9.5. BTW: Which version do you use? Alternatively you might try to alter the delimiter character: nd1=> COPY address_types (game_id, ...



Only top voted, non community-wiki answers of a minimum length are eligible