Tag Info

Hot answers tagged

9

I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead). I tried to make different results comparable by using more than one ...


8

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


6

Use the SQL Azure Migration Wizard: The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to analyzes, generates scripts, and migrate data (via BCP) from: SQL Server to SQL Azure SQL Azure to SQL Server SQL Azure to SQL Azure


6

Three ways. Either IGNORE duplicate errors: INSERT IGNORE ... ; -- without ON DUPLICATE KEY or try to do a redundant update when there is a duplicate: INSERT ... ON DUPLICATE KEY UPDATE id = id ; or check for duplicates before inserting: INSERT INTO requests (id, ctg, msg, nick, filled, dated, filldate) SELECT (NULL, ...


6

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


5

In my experience (and as shown in many tests) NOT IN like demonstrated by @gsiems is rather slow, particularly with long lists. This query (doing the same, exactly) should be much faster: DELETE FROM questions_tags q WHERE EXISTS ( SELECT 1 FROM questions_tags q1 WHERE q1.ctid < q.ctid AND q.question_id = q1.question_id AND ...


5

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them. One solution is to individually GROUP BY and aggregate first (one aggregation for the list ...


4

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names: INSERT INTO TargetTable (Emp_ID, Name) SELECT Emp_ID, MIN(Name) FROM SourceTable GROUP BY Emp_ID ; And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on ...


4

Compose a bcp script that exports the contents of all your tables to local files. Start by writing a query that will output a bcp command to export each table in your target database to a path on your destination machine: SELECT 'bcp ' + SCHEMA_NAME(schema_id) + '.' + name + ' out ' + ' D:\local_backup_directory\' + ...


4

Red Gate has an aptly named SQL Azure Backup Tool. It's free, but Red Gate has discontinued support for it.


4

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order. create table friends ( user_a integer not null, -- references users, not shown user_b integer not null, ...


4

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A". Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table. If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be ...


4

There is no way of knowing that a table in A has changed except by polling. You could consider Materialized Views, refreshing periodically, which can work over a dblink - but only a complete refresh is possible so this may only be practical if the tables are small.


4

Database duplication generally refers to restoring a physical backup of a database to a different server (preferably using RMAN). That is normally done periodically to refresh lower environments from production. Database replication generally refers to the process of copying a subset of data from one database to another on an ongoing basis. Replication ...


4

You can use the ctid to accomplish that. For example: Create a table with duplicates: =# create table foo (id1 integer, id2 integer); CREATE TABLE =# insert into foo values (1,1), (1, 2), (1, 2), (1, 3); INSERT 0 4 =# select * from foo; id1 | id2 -----+----- 1 | 1 1 | 2 1 | 2 1 | 3 (4 rows) Select the duplicate data: =# select ...


4

If my source database uses SPFILE then do I have create a PFILE from the SPFILE? Yes, you need to create a temporary PFILE to use while duplicating the database. You will later switch the new instance to use the SPFILE. Use CREATE PFILE = 'path/to/pfile' FROM SPFILE; You only need to create directories that are referenced in the PFILE or SPFILE. ...


3

Assuming the following table definition CREATE TABLE T ( Name VARCHAR(50) NOT NULL, IsDeleted BIT NOT NULL ) Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on. CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'


3

I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title. If I had to resolve your problem, I would build a query for each unique key of the form: SELECT ...


3

Tough one, given that you have no access beyond a SELECT in db_A. So here's a thought, but it requires some pretty strict assumptions that may (or may not) be met: Requirements: All tables being sync'd have either: A timestamp (the more resolution the better) A unique, sequential ID All table rows, once sync'd, don't change. Alternatively, if a change ...


3

This might be better placed on stack overflow. This isn't something you'll accomplish with MySQL. What you're talking about is referred to as 'stemming' in search. Similar to matching different conjugations of a regular word e.g. run => runs ,ran. I don't know of any such applications for proper names off hand but when you find one that will sit ...


3

First, let me start with a warning. Be very careful when you start messing with the data on the slave. It can really ruin the integrity of the replication setup. That being said, if you are absolutely certain you don't want the slave to execute that particular statement, you can issue the following command on the slave: Edit by @RolandoMySQLDBA (Just added ...


3

if you want to select unique record from other join table which has same FK in multiple row you can use your query as SELECT wpss_quesset.quesset, wpss_questions.* FROM wpss_quesset JOIN wpss_questions ON wpss_quesset.id = wpss_questions.ques_set_id GROUP BY wpss_quesset.id OR If you want to get all record that of ...


3

You shouldn't try to do too many things with one data store. If you have a table for transactions, then just keep transactions in that table. Don't mix in scheduled future transactions with current transactions. Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions. Keep the recurring/scheduled ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


2

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause. You can try this : CREATE TABLE tbl_fields_unique LIKE tbl_fields; ALTER TABLE tbl_fields_unique ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid); INSERT IGNORE INTO tbl_fields_unique SELECT * FROM tbl_fields; ALTER ...


2

I noticed there's a new tool in this list, it's Idera's Azure SQL Database Backup. It's free and they usually make good tools, so it's worth a try. Another way of exporting databases (but this time only schema, no data) to Azure is by using DAC packages (.dacpac files extracted from Management Studio or Visual Studio 2010). This works only from SQL 2008 R2 ...


2

These rules cannot be enforced by a unique index within MySQL, even when you use a reverse index. You need to enforce this rule within your application business logic or using a database trigger/function/procedure which checks the data before it is saved.


2

First a sample table mysql> drop database if exists ali; Query OK, 1 row affected (0.10 sec) mysql> create database ali; Query OK, 1 row affected (0.00 sec) mysql> use ali; Database changed mysql> CREATE TABLE test -> ( -> id int(11) unsigned NOT NULL AUTO_INCREMENT, -> external_id int(11), -> number smallint(5), ...


2

I would agree with @BillThor that what you want to do may create some problems but what you need is ON DUPLICATE KEY UPDATE statement so your query would be something like this: INSERT INTO table1 (`title`, `abr`, `name`) VALUES ('title', 'abr', 'name') ON DUPLICATE KEY UPDATE `title` = CONCAT(`title`, '-2'); You can change CONCAT to any other function ...


2

Your query can return the expected results by adding the reverse condition: SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP FROM persons A LEFT JOIN persons B ON a.ID <> b.ID AND (a.Name LIKE CONCAT ("%", b.Name, "%") OR b.Name LIKE CONCAT ("%", a.Name, "%")) ORDER BY ID; I don't know if it will be faster, but another way to do it would be to use ...



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