Blog: Tag Archives: Database

SQL – Create a table if it doesn’t exist

Posted On:

strSQL="IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TBLTempPrices]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [TBLTempPrices]( " &_
				"[VersionCode] [varchar](25) NULL," &_
				"[Quantity1] [int] NULL," &_
			"[Price1] [float] NULL," &_
			"[Quantity2] [int] NULL," &_
			"[Price2] [float] NULL," &_
			"[Quantity3] [int] NULL," &_
			"[Price3] [float] NULL," &_
			"[Quantity4] [int] NULL," &_
			"[Price4] [float] NULL," &_
			"[Quantity5] [int] NULL," &_
			"[Price5] [float] NULL," &_
			"[Quantity6] [int] NULL," &_
			"[Price6] [float] NULL)"

SQL – Add Primary Key to Table

Posted On:

'** MSSQL / MYSQL 
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));


'** MSSQL - Identity Specification

'** IDENTITY [ ( seed , increment )]

CREATE TABLE TBLPindarNewsItems (News_ID int IDENTITY (1,1), News_Item nvarchar(2000))

'** Access

strSQL="ALTER TABLE TBLPindarWebUser ALTER COLUMN Web_ID COUNTER"

'** MYSQL - Alter table - Change INT to Primary Key

alter table `test`.`testidtbl` change `TestID` `TestID` int (11)   NOT NULL AUTO_INCREMENT , add primary key (`TestID` )

SQL – Alter Table

Posted On:

ALTER TABLE table_name 
ADD column_name datatype

ALTER TABLE table_name 
DROP COLUMN column_name


'** Add Memo Column

ALTER TABLE MyTable ADD COLUMN NewColumn MEMO

'** Add Integer Column 

strSQL="Alter Table TBLPoints ADD column  GamesAgainst Integer"


'*** Change Data Type of Column

ALTER TABLE table_name
 MODIFY column_name column_type;