Tag Info

Hot answers tagged

8

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


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


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


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

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

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

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

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

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


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

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

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

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


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

You may want to try creating a trigger that will check for the presence of (col1,col2) existing as (col2,col1) Here is an example: use test drop table if exists ali; create table ali ( col1 int not null, col2 int not null, primary key (col1,col2) ); DELIMITER $$ CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW BEGIN DECLARE ...


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

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


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

You can convert the join to a semi-join, using either IN or EXISTS: SELECT employees.*, companies.* FROM companies LEFT JOIN employees ON companies.id = employees.id_company WHERE EXISTS ( SELECT 1 FROM company_technologies WHERE companies.id = company_technologies.id_company AND company_technologies.id_technology ...


1

One of many possible ways: SELECT avg(cnt) AS avg_per_user FROM ( SELECT usr, count(DISTINCT date) AS cnt FROM codes c WHERE actioncode in (61, 62, 700, 701) OR actioncode = 84 AND EXISTS ( SELECT 1 FROM codes c1 WHERE c1.date = c.date AND c1.actioncode = 85 ) OR actioncode = 10 AND EXISTS ( ...


1

I did something a little different SELECT DISTINCT AA.* FROM ( SELECT A.*,IF(IFNULL(B.ID,'')='','','Dup') DUP FROM table_a A LEFT JOIN table_a B ON a.ID <> b.ID AND IF(LENGTH(A.name)>LENGTH(B.name), INSTR(A.name,B.name)>0, INSTR(B.name,A.name)>0) ) AA; NOTE : I basically plagiarized Leigh's answer and augmented it ...


1

In the spirit of @yercube's answer, I have an answer that has an added twist. CREATE TABLE stage ( id int not null auto_increment, name varchar(20), primary key (id) ); CREATE TABLE stage2 LIKE stage; INSERT INTO stage (name) SELECT name FROM item; INSERT INTO stage2 (id) SELECT min_id FROM (SELECT MIN(id) min_id,name FROM stage GROUP BY name) ...


1

This would depend on the table's layout. Suppose you have the following table CREATE TABLE `mydata` ( `A` varchar(19) NOT NULL, `B` varchar(9) NOT NULL, KEY `A` (`A`), KEY `B` (`B`) ) ENGINE=InnoDB; Before you insert 1000 rows into mydata, you could do preload them into another table called mynewdata like this: CREATE TABLE ...


1

I reached the max possible value of an int coloumn. Problem resolved by altering table and setting big int than int. The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215 Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html



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