It’s common to want to copy the contents of one table into another. While there are some guis out there that will do this for you, they often don’t allow you to select partial contents or a variety of extras that you may need.
What annoys me about having worked with numerous flavors of SQL and databases is that the syntax tends to differ slightly. On some dbs, this works just fine:
select *
into table1
from table2
where id = 1
But some dbs expect that table2 is some sort of variable rather than another table containing the same columns. So on others (like MySQL), you need to do it this way:
insert into table1
select *
from table2
where id = 1
I’m not exactly sure which way is “standard SQL”… you?
November 12th, 2009
Getting “????” in your database rather than your Arabic or Chinese character sets? Try this:
ALTER TABLE contents CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
October 19th, 2009
I often find the need to check for duplicates in the database. This is not an intuitive SQL command to create off the top of your head as it involves Group By and the rarely used Having clause. Since I’m always looking it up, I thought I’d post it here.
The code below will identify which rows have duplicate values based upon two primary key columns:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
If you need to go a step further and remove duplicate rows, there’s a great Microsoft blog article that will walk you through the process:
How to remove duplicate rows from a table in SQL Server
June 12th, 2008
