Tag Info

Hot answers tagged

11

In SSMS you need to change some options: Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.


9

I see that you clearly stated your looking for a solution in SSMS but I thought I would provide a PowerShell solution in case it helps. (SQLPS is accessible from inside of SSMS 2008 & SSMS 2008R2) You can use SQLPS (or regular PowerShell with the SQL cmdlet snapin) to run something like this: Invoke-Sqlcmd -Query "sp_databases" -ServerInstance ...


8

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example: SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Text; HDR=YES; Database=C:\Text', 'SELECT * FROM ...


8

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by SELECT * FROM staging_table EXCEPT SELECT * FROM real_table ; Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, ...


4

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm postgres@dexter:/home/peter/PyPacks$ cd phm bash: cd: phm: Permission denied Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work. Try at ...


4

You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter: The default is a tab character in text format. (source)


3

It could be normal if there are newlines in certain text fields. Newlines are allowed when the value in the field is enclosed by double quotes. And obviously that makes the number of lines in the file greater than the number of records. Example : $ cat file.csv 1,"ab cd",2 3,"efgh",4 $ wc -l file.csv 3 file.csv => create table csvtest(a int, b text, c ...


3

REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs. Here is something else you can do. Suppose your table is called name_city and it looks like this: CREATE TABLE name_city ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, city VARCHAR(30) NOT NULL, PRIMARY KEY (id) ); and you want to do the LOAD ...


3

If you like the PowerShell script approach, I have a script that Exports to CSV from SSMS via PowerShell. I like it in so far you can have a dynamic SQL Script, heck you can select whatever text and SSMS passes it to the script as an argument. Only downside is I haven't found a clever way to pass along the current window's connection. My current ...


3

The COPY FROM will not violate integrity of database. When a constraint fails, an error is throw, and none of data are copied (it is rolled back). This behavior is unchangeable. You can do a script in a programming language to help you in a copy.


3

I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example). Consult Oracle Utilities Guide for detailed info. What follows is my sample of how you can update tables from flat files. First ...


3

Simple approaches: If your tables are indexed, drop the indexes and put them back after you load the data. It's generally going to be faster for MySQL to build the index on a table in one pass than to continually index the incoming data as you add it. Run more than one copy of your script, each with a subset of the data source files, doing inserts in ...


3

The sequences are designed like that, the are not supposed to produce contiguous numbers. However, you can reset them before (re)trying the COPY with SELECT setval('sequence_name', (SELECT max(id) FROM your_table));


2

I also wrote an article about comparing two data sets using Excel, text editor or database engine. For your volume of data it is clear that, from the three options, only the database engine would be suitable. Unless you use some SSIS feature (I don't have any knowledge about SSIS), then you have to compare the two data sets after they are loaded in the ...


2

You could email them with UTL_MAIL or POST them to another server with UTL_HTTP Alternatively, save the complete reports as a clob in a table and query from any client - this is a little more work as you'll have to rewrite your reporting procedures, but is still easier than starting from scratch on the client.


2

This question was answered on Stackoverflow. By default its one transaction per disk rotation (roughly), http://www.sqlite.org/faq.html#q19 The way to speedier writes is to wrap multiple ones inside a transaction.


2

Since you are not allowed to script but SQL try the following: CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM; LOAD DATA LOCAL INFILE 'whatever_importfile.txt' INTO TABLE importweirddata LINES TERMINATED BY '\r\n'; UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"'); SELECT * FROM importweirddata INTO 'improved_importfile.txt'; DROP ...


2

Consider ETL software (eg Pentaho kettle) as it is intended to extract data from databases


2

I know it would be easier to find something pre-built, but you could write a small application in your favorite language to handle the simple task of asking the database for the data and writing it to a file. For this you should take advantage of the suggestion from Jack Douglas to modify the reporting procedures to create CLOBs. Your app could then ...


2

Both the approaches already suggested appear to be unnecessarily complicated. Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths. Because it's a psql backslash command you omit the trailing semicolon, eg: \copy products TO '/tmp/products.csv' CSV ...


2

The Linux command is: psql -h 127.0.0.1 -U username -o file.csv -c 'select id, name from clients;'


2

Use TO STDOUT instead of TO filename and redirect the output to a local file. If you are in psql, first specify the output file with \o nice_file_name.csv then do the COPY: COPY products TO STDOUT DELIMITER ','; This will hopefully create the file and populate it with all products data. This solution is applicable if using psql interactively, ...


2

Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication. When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement ...


2

Well, since you have the CSV files, they can serve as a backup to the staging tables, so I see no benefit to keeping them in the primary database (or maintaining the staging tables after the import is done). If those staging tables can be discarded / truncated once the import is finished, then they really should be separate and have as minimal impact on the ...


2

Based on my experience with ccure 800 in particular, you do need to have MS Excel and all of the supporting MS Office components installed on the same PC as the ccure Administration client. The OCX libraries are required to support the export function.


2

From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting. # added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html # to allow for abrowse to load data! loose-local-infile = 1 local-infile = 1 Now, it was either ...


2

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes). Save your package to a file then open it in SQL Server Data Tools. Go into the data flow and open the source. In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in ...


2

OCX files are part of the ActiveX control. There's something wrong with the application and you need to call the vendor to get it fixed.


1

Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one Something along the lines: update real_table set ... where exists (select 1 from staging_table where ...) insert into ... select from stage_table left join real_table where real_table.some_col is NULL delete real_table ...


1

Based on your need, I can suggest couple of options : Use SSIS Import/Export; or Use BCP OUT and BCP IN Each method has its own benefits and it all depends on your requirement which one to choose.



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