Tag Info

8

A .bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human). You can rename the .bacpac to .zip to see the actual contents. You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically. It is a snapshot that includes User data + Schema from SQL Server or Azure SQL ...

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 ( ...

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 ...

5

Without getting too involved in specific details on the language required to do this in Sybase SQL Anywhere 8, which is now more than 15 years old, I'd recommend something like the following: create a table on the laptop database that contains a column which uniquely identifies rows that have already been exported to the main database. the export function ...

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: sysrscols,3,...

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

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 ...

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 ...

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

You are doing a cartesian product here: ... FROM dbo.[DataSource], @ids i you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it: INSERT INTO dbo.[Users] ( first_name, surname , address_id ) SELECT first_name, surname, ...

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

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

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

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

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

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

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

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

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

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 ...

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 >= "2016-...

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

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

From the symptoms, it seems that you are using InnoDB engine for your tables. If so, it is normal not to have accurate number of rows shown by clients (Like HeidiSQL or PHPMyAdmin). It is also worth it to mention that SHOW TABLE STATUS LIKE 'table_name' does not provide accurate row count for InnoDB tables. InnoDB estimates the number of rows in the ...

2

Let's do some calculation: uuid: 36 bytes (Add 1 because it is varchar) ==> 37 uuid_tags_codes: 4 bytes When indexed, full length of uuid field: 100 bytes (37+4+100)bytes * 162,281,544 rows = 21.3 G So, 21G without adding the overhead! Therefore, your first problem is the space. To answer your questions: It is more than 6-8G. To ba safe you need ~...

1

It has to do with the max_allowed_packet on the MySQL server. It was set to 16M and when I upped it to 64M in my.cnf located in /private/etc/ (on Mac El Capitan) which was a copy of the my-huge.cnf. After stopping the MySQL server and Apache and restarting them made my site work locally again and the error messages went away.

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