SQL Server Management Studio’s “Import Data” task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into. If the tables don’t exist it will create them for you, but you’ll probably have to recreate any indexes and such. If … Read more
You need to do the subselect based on a column name, not *. For example, if you had an id field common to both tables, you could do: SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2) Refer to the MySQL subquery syntax for more examples.
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here’s the script: CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname=”public”; BEGIN FOR stmt IN statements LOOP EXECUTE ‘TRUNCATE TABLE ‘ || quote_ident(stmt.tablename) || ‘ CASCADE;’; END LOOP; END; $$ LANGUAGE … Read more
They don’t exist in MySQL do they? Just use a temp table: CREATE PROCEDURE my_proc () BEGIN CREATE TEMPORARY TABLE TempTable (myid int, myfield varchar(100)); INSERT INTO TempTable SELECT tblid, tblfield FROM Table1; /* Do some more stuff …. */ From MySQL here “You can use the TEMPORARY keyword when creating a table. A TEMPORARY … Read more
SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’ To show only tables from a particular database SELECT TABLE_NAME FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ Or, SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_CATALOG=’dbName’ –(for MySql, use: TABLE_SCHEMA=’dbName’ ) PS: For SQL … Read more
There’s three ways to get this sort of count, each with their own tradeoffs. If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can … Read more
Postgres 10 or later (serial columns remain unchanged, see below.) Consider a standard-SQL IDENTITY column. Can be GENERATED BY DEFAULT or (stricter) GENERATED ALWAYS. Basics in the manual for CREATE TABLE. Details in this blog entry by its principal author Peter Eisentraut. Create table with IDENTITY column CREATE TABLE staff ( staff_id int GENERATED ALWAYS … Read more
Quote: I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.* SELECT * from dba_objects WHERE object_name like ‘%DTN%’ A column isn’t an object. If you mean that you expect the column name to be like ‘%DTN%’, the query you … Read more
To add a unique constraint, you need to use two components: ALTER TABLE – to change the table schema and, ADD UNIQUE – to add the unique constraint. You then can define your new unique key with the format ‘name'(‘column1’, ‘column2’…) So for your particular issue, you could use this command: ALTER TABLE `votes` ADD … Read more
What is the schema search path search_path? The manual: […] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. Bold emphasis mine. This explains identifier resolution. The “current … Read more