Category Archives: Database

SQL – Fill a column with a value

update [table] set [column] = ‘value’

 

Example:

alter table PI_15_0_0_SV add Language nchar(2) not null default ‘SV’ with values

insert into pi_15_0_0 select * from PI_15_0_0_SV

Advertisements

List stored procedures in a DB

select *
 from db_name.information_schema.routines
 where routine_type = 'PROCEDURE'

Insert from a select statement – SQL

insert into dest_table
SELECT col1_value, b.LableID 
 FROM source_table a with (nolock)
 where a.col = val
 and a.DateOfCreation >= '01.01.2013'

Count duplicate values – SQL

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Copy Table with 1 line of SQL

This will copy a table structure and its data into a new table:

select * into wdsProductList_20130405 from wdsProductList

This will copy a table definition only (in MySQL) without data:

create table foo like bar;

Find a column by name (SQL Server 2008)

How to find column within a database with searching by name:

SELECT * FROM Information_Schema.Columns c WHERE  c.COLUMN_NAME like 'manu%'

Generating SQL with SQL

I used this script today to add a column ‘FileID’ to all the tables which have the column ‘processStatus’, to generate sql statements which I can run.

select 'ALTER TABLE [' + c.TABLE_NAME + '] ADD FileID int NULL'
 FROM Information_Schema.Columns c
 WHERE c.COLUMN_NAME = 'processedStatus'
/* Generated sql to execute
 ALTER TABLE [productMain] ADD FileID int NULL
 ALTER TABLE [composer] ADD FileID int NULL
 ALTER TABLE [manufacturer] ADD FileID int NULL
 ALTER TABLE [productSupplier] ADD FileID int NULL
 ALTER TABLE [artist] ADD FileID int NULL
 ALTER TABLE [productType] ADD FileID int NULL
 ALTER TABLE [properties] ADD FileID int NULL
 */