BEGIN CREATE TABLE [MYDB].[dbo].[zz_customer_history] ( [id] int IDENTITY(1,1) PRIMARY KEY, [customer_id] int NOT NULL, [date] int NOT NULL, [kind_from] varchar(10) NULL, [kind_to] varchar(10) NULL ) END

http://technet.microsoft.com/en-us/library/aa258255%28v=SQL.80%29.aspx

with default value USE [SL_repl_trans] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[yz3_stock_h]( [yz3_location] [smallint] DEFAULT 0, [yz3_style] [varchar](50) NOT NULL, [yz3_qty] [int] DEFAULT 0, [date] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) GO 26.03.14 CREATE TABLE dbo.sl.zz_categories_main ( name varchar(255) PRIMARY KEY, meta_desc varchar(max) DEFAULT '' NOT NULL, h1 varchar(max) DEFAULT '' NOT NULL, title varchar(max) DEFAULT '' NOT NULL, description varchar(max) DEFAULT '' NOT NULL, ) GO 26.03.14 create with auto-increment BEGIN CREATE TABLE [SL_planning_module].[dbo].[supplier_orders_etas_log] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY, [supplier_id] varchar(255) NOT NULL DEFAULT '', [eta] int NOT NULL DEFAULT 0, [updated] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) END 26.03.14

https://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012

BEGIN CREATE TABLE [SL].[dbo].[zz_country_states] ( country_name VARCHAR(255) NOT NULL, alt_names VARCHAR(255) NOT NULL DEFAULT '', code2 VARCHAR(10) NOT NULL DEFAULT '', code3 VARCHAR(3) NOT NULL DEFAULT '', iso_cc INT NOT NULL DEFAULT 0, fips_code VARCHAR(10) NOT NULL DEFAULT '', fips_country_name VARCHAR(50) NOT NULL DEFAULT '', un_region VARCHAR(50) NOT NULL DEFAULT '', un_subregion VARCHAR(50) NOT NULL DEFAULT '', cdh_id INT NOT NULL DEFAULT 0, comments VARCHAR(255) NOT NULL DEFAULT '', lat VARCHAR(10) NOT NULL DEFAULT '', lng VARCHAR(10) NOT NULL DEFAULT '', ) END 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_country_states] ( country_name VARCHAR(50) NOT NULL DEFAULT '', subdiv VARCHAR(10) NOT NULL DEFAULT '', subdiv_name VARCHAR(100) NOT NULL DEFAULT '', level_name VARCHAR(50) NOT NULL DEFAULT '', alt_names VARCHAR(255) NOT NULL DEFAULT '', subdiv_star VARCHAR(10) NOT NULL DEFAULT '', subdiv_id INT NOT NULL DEFAULT 0, country_id INT NOT NULL DEFAULT 0, country_code_2 CHAR(2) NOT NULL DEFAULT '', country_code_3 CHAR(3) NOT NULL DEFAULT '', ) END 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_postal_codes] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY, country_code VARCHAR(255) NOT NULL DEFAULT '', city VARCHAR(255) NOT NULL DEFAULT '', postal_codeVARCHAR(255) NOT NULL DEFAULT '', latitudeVARCHAR(255) NOT NULL DEFAULT '', longitudeVARCHAR(255) NOT NULL DEFAULT '', admin1VARCHAR(255) NOT NULL DEFAULT '', admin1_codeVARCHAR(255) NOT NULL DEFAULT '', admin2VARCHAR(255) NOT NULL DEFAULT '', admin2_codeVARCHAR(255) NOT NULL DEFAULT '', admin3VARCHAR(255) NOT NULL DEFAULT '', admin3_codeVARCHAR(255) NOT NULL DEFAULT '', ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[stock_stones] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY, [date] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, stone_no INT NOT NULL DEFAULT 0, stone_code VARCHAR(255) NOT NULL DEFAULT '', shape VARCHAR(255) NOT NULL DEFAULT '', color VARCHAR(255) NOT NULL DEFAULT '', quality VARCHAR(255) NOT NULL DEFAULT '', qty INT NOT NULL DEFAULT 0, [value] FLOAT NOT NULL DEFAULT 0, year_usage INT NOT NULL DEFAULT 0, ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[backorders] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY, [date] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, order_no INT NOT NULL DEFAULT 0, line INT NOT NULL DEFAULT 0, style VARCHAR(255) NOT NULL DEFAULT '', order_date INT NOT NULL DEFAULT 0, item_type VARCHAR(10) NOT NULL DEFAULT '', customer VARCHAR(255) NOT NULL DEFAULT '', customer_code INT NOT NULL DEFAULT 0, ordered INT NOT NULL DEFAULT 0, qty_supplied INT NOT NULL DEFAULT 0, in_packing INT NOT NULL DEFAULT 0, date_delivery INT NOT NULL DEFAULT 0, customer_order_no VARCHAR(255) NOT NULL DEFAULT '', ready_to_ship INT NOT NULL DEFAULT 0, backorder INT NOT NULL DEFAULT 0, ordered_usd float NOT NULL DEFAULT 0, unit_usd float NOT NULL DEFAULT 0, ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[cimco_po] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[date] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,PONo VARCHAR(255) NOT NULL DEFAULT '' ,POItemNO INT NOT NULL DEFAULT 0 ,CatalogueNo VARCHAR(255) NOT NULL DEFAULT '' ,PartName VARCHAR(255) NOT NULL DEFAULT '' ,OrderQty INT NOT NULL DEFAULT 0 ,RemainQty INT NOT NULL DEFAULT 0 ,UOM VARCHAR(255) NOT NULL DEFAULT '' ,Warehouse VARCHAR(255) NOT NULL DEFAULT '' ,BinLocation VARCHAR(255) NOT NULL DEFAULT '' ,Conversion INT NOT NULL DEFAULT 0 ,Amount Float NOT NULL DEFAULT 0 ,Cost Float NOT NULL DEFAULT 0 ,UnitPrice Float NOT NULL DEFAULT 0 ,SupplierCode VARCHAR(255) NOT NULL DEFAULT '' ,SupplierName VARCHAR(255) NOT NULL DEFAULT '' ,Type VARCHAR(255) NOT NULL DEFAULT '' ) END 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_supplier_tracker] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,supplier VARCHAR(255) NOT NULL DEFAULT '' ,[date] INT NOT NULL DEFAULT 0 ,amount_usd Float NOT NULL DEFAULT 0 ) END 26.03.14 BEGIN CREATE TABLE [SL_customer].[dbo].[av0_style] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,_name VARCHAR(255) NOT NULL DEFAULT '' ,F19_av0_a VARCHAR(255) NOT NULL DEFAULT '' ,F21_av0_section VARCHAR(255) NOT NULL DEFAULT '' ,alternative_code VARCHAR(255) NOT NULL DEFAULT '' ,archive VARCHAR(255) NOT NULL DEFAULT '' ,autoclave VARCHAR(255) NOT NULL DEFAULT '' ,av0_Discount VARCHAR(255) NOT NULL DEFAULT '' ,av0_Promotion VARCHAR(255) NOT NULL DEFAULT '' ,av0_ball_size VARCHAR(255) NOT NULL DEFAULT '' ,av0_ball_size_imperial VARCHAR(255) NOT NULL DEFAULT '' ,av0_ball_size_name VARCHAR(255) NOT NULL DEFAULT '' ,av0_code VARCHAR(255) NOT NULL DEFAULT '' ,av0_color_1 VARCHAR(255) NOT NULL DEFAULT '' ,av0_color_2 VARCHAR(255) NOT NULL DEFAULT '' ,av0_ctree_qty VARCHAR(255) NOT NULL DEFAULT '' ,av0_description VARCHAR(255) NOT NULL DEFAULT '' ,av0_enter_date VARCHAR(255) NOT NULL DEFAULT '' ,av0_exlusive_item VARCHAR(255) NOT NULL DEFAULT '' ,av0_gauge VARCHAR(255) NOT NULL DEFAULT '' ,av0_gauge_imperial VARCHAR(255) NOT NULL DEFAULT '' ,av0_gauge_name VARCHAR(255) NOT NULL DEFAULT '' ,av0_group VARCHAR(255) NOT NULL DEFAULT '' ,av0_item_type VARCHAR(255) NOT NULL DEFAULT '' ,av0_karat VARCHAR(255) NOT NULL DEFAULT '' ,av0_length VARCHAR(255) NOT NULL DEFAULT '' ,av0_length_imperial VARCHAR(255) NOT NULL DEFAULT '' ,av0_length_name VARCHAR(255) NOT NULL DEFAULT '' ,av0_metal VARCHAR(255) NOT NULL DEFAULT '' ,av0_min_in_cust_order VARCHAR(255) NOT NULL DEFAULT '' ,av0_numerator VARCHAR(255) NOT NULL DEFAULT '' ,av0_part_color VARCHAR(255) NOT NULL DEFAULT '' ,av0_pic1 VARCHAR(255) NOT NULL DEFAULT '' ,av0_pic_2 VARCHAR(255) NOT NULL DEFAULT '' ,av0_pic_chart VARCHAR(255) NOT NULL DEFAULT '' ,av0_pic_exist VARCHAR(255) NOT NULL DEFAULT '' ,av0_picture_path_ VARCHAR(255) NOT NULL DEFAULT '' ,av0_place_in_catalog VARCHAR(255) NOT NULL DEFAULT '' ,av0_style VARCHAR(255) NOT NULL DEFAULT '' ,av0_surcharge VARCHAR(255) NOT NULL DEFAULT '' ,av0_web VARCHAR(255) NOT NULL DEFAULT '' ,av0_weight VARCHAR(255) NOT NULL DEFAULT '' ,av21_Description VARCHAR(255) NOT NULL DEFAULT '' ,av21_MaterialCode VARCHAR(255) NOT NULL DEFAULT '' ,av21_PicturePath VARCHAR(255) NOT NULL DEFAULT '' ,bestseller VARCHAR(255) NOT NULL DEFAULT '' ,category VARCHAR(255) NOT NULL DEFAULT '' ,_checksum VARCHAR(255) NOT NULL DEFAULT '' ,child_compatible VARCHAR(255) NOT NULL DEFAULT '' ,color_1_picture VARCHAR(255) NOT NULL DEFAULT '' ,color_2_picture VARCHAR(255) NOT NULL DEFAULT '' ,free_shipping VARCHAR(255) NOT NULL DEFAULT '' ,groups VARCHAR(255) NOT NULL DEFAULT '' ,imperial VARCHAR(255) NOT NULL DEFAULT '' ,_max VARCHAR(255) NOT NULL DEFAULT '' ,met_description VARCHAR(255) NOT NULL DEFAULT '' ,_min VARCHAR(255) NOT NULL DEFAULT '' ,new_arrival VARCHAR(255) NOT NULL DEFAULT '' ,online_release VARCHAR(255) NOT NULL DEFAULT '' ,part_color_picture VARCHAR(255) NOT NULL DEFAULT '' ,pics VARCHAR(255) NOT NULL DEFAULT '' ,pics_name VARCHAR(255) NOT NULL DEFAULT '' ,prices VARCHAR(255) NOT NULL DEFAULT '' ,sec_description VARCHAR(255) NOT NULL DEFAULT '' ,set_imperial VARCHAR(255) NOT NULL DEFAULT '' ,stock VARCHAR(255) NOT NULL DEFAULT '' ,stock1 VARCHAR(255) NOT NULL DEFAULT '' ,stock11 VARCHAR(255) NOT NULL DEFAULT '' ,suppliers VARCHAR(255) NOT NULL DEFAULT '' ,tkt_description VARCHAR(255) NOT NULL DEFAULT '' ,tkt_description2 VARCHAR(255) NOT NULL DEFAULT '' ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[hz2_order_ln_log] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,order_no INT NOT NULL DEFAULT 0 ,line INT NOT NULL DEFAULT 0 ,style VARCHAR(255) NOT NULL DEFAULT '' ,qty_orderd INT NOT NULL DEFAULT 0 ,line_total_price FLOAT NOT NULL DEFAULT 0 ,order_total FLOAT NOT NULL DEFAULT 0 ,currency VARCHAR(255) NOT NULL DEFAULT '' ,customer VARCHAR(255) NOT NULL DEFAULT '' ,line_status INT NOT NULL DEFAULT 0 ,order_status INT NOT NULL DEFAULT 0 ,updated int NOT NULL DEFAULT 0 ) END 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_slus_moq] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,av0_style VARCHAR(255) NOT NULL DEFAULT '' ,moq INT NOT NULL DEFAULT 0 ) END 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_code_stock] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,av0_style VARCHAR(255) NOT NULL DEFAULT '' ,stock INT NOT NULL DEFAULT 0 ,zstock INT NOT NULL DEFAULT 0 ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[customer_code_chnage] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[update] VARCHAR(8) NOT NULL DEFAULT '' ,[short_customer_name] VARCHAR(255) NOT NULL DEFAULT '' ,[old_code] VARCHAR(255) NOT NULL DEFAULT '' ,[new_code] VARCHAR(255) NOT NULL DEFAULT '' ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[table_size] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[update] VARCHAR(8) NOT NULL DEFAULT '' ,[server] VARCHAR(255) NOT NULL DEFAULT '' ,[database_name] VARCHAR(255) NOT NULL DEFAULT '' ,[size_mb] int NOT NULL DEFAULT 0 ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[zz_sp_log] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[server] VARCHAR(8) NOT NULL DEFAULT '' ,[db] VARCHAR(50) NOT NULL DEFAULT '' ,[stored_procedure] VARCHAR(255) NOT NULL DEFAULT '' ,[object_id] VARCHAR(20) NOT NULL DEFAULT '' ,[last_execution_day] VARCHAR(8) NOT NULL DEFAULT '' ) END 26.03.14 example to prevent error when upsert mssql row BEGIN CREATE TABLE [SL].[dbo].[zz_index] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[uri] VARCHAR(8) NOT NULL DEFAULT '' ,[tags] VARCHAR(50) NOT NULL DEFAULT '' ) END ALTER TABLE [SL].[dbo].[zz_index] ADD CONSTRAINT indexconst UNIQUE([uri]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_stone_extra] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[stone] VARCHAR(255) NOT NULL DEFAULT '' ,[1yr_usage] int NOT NULL DEFAULT 0 ) END ALTER TABLE [SL].[dbo].[zz_stone_extra] ADD CONSTRAINT stoneextra UNIQUE([stone]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL].[dbo].[zz_setting] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[key] VARCHAR(255) NOT NULL DEFAULT '' ,[value] VARCHAR(255) NULL DEFAULT '' ,[app] VARCHAR(255) NULL DEFAULT '' ,[comment] VARCHAR(255) NULL DEFAULT '' ) END ALTER TABLE [SL].[dbo].[zz_setting] ADD CONSTRAINT key_settings UNIQUE([key]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 create mssqltable BEGIN CREATE TABLE [SL_h].[dbo].[new_invoice_line] ( [id] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[last_update] VARCHAR(8) NOT NULL DEFAULT '' ,[invoice] int NULL DEFAULT 0 ,[customer] VARCHAR(255) NULL DEFAULT '' ,[supplier] VARCHAR(255) NULL DEFAULT '' ,[date] VARCHAR(8) NULL DEFAULT '' ,[line] int NULL DEFAULT 0 ,[order_no] int NULL DEFAULT 0 ,[order_ln] int NULL DEFAULT 0 ,[style] VARCHAR(255) NULL DEFAULT '' ,[price_list] VARCHAR(255) NULL DEFAULT '' ,[price_unit] float NULL DEFAULT 0 ,[yesterday_qty_item] int NULL DEFAULT 0 ,[today_qty_item] int NULL DEFAULT 0 ,[new_item] int NULL DEFAULT 0 ) END ALTER TABLE [SL_h].[dbo].[new_invoice_line] ADD CONSTRAINT key_settings UNIQUE([last_update],[invoice],[line]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 createmssql table BEGIN CREATE TABLE [SL_h].[dbo].[monthly_report] ( [date] int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[exchange_rate] VARCHAR(8) NOT NULL DEFAULT '' ,[total_sold] float NULL DEFAULT 0 ) END ALTER TABLE [SL_h].[dbo].[monthly_report] ADD CONSTRAINT date_id UNIQUE([date]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[slbj_prices] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[date] VARCHAR(8) NOT NULL DEFAULT '' ,[av0_style] VARCHAR(255) NOT NULL DEFAULT '' ,[sl_wholeful] float NULL DEFAULT 0 ,[surcharge_gold] float NULL DEFAULT 0 ,[surcharge_silver] float NULL DEFAULT 0 ,[cost] float NULL DEFAULT 0 ,[sold_qty_qb] int NULL DEFAULT 0 ,[price_qb_avg] int NULL DEFAULT 0 ) END ALTER TABLE [SL_h].[dbo].[slbj_prices] ADD CONSTRAINT av0_style_date_slbj UNIQUE(av0_style,[date]) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[slbj_cost] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[date] VARCHAR(8) NOT NULL DEFAULT '' ,[av0_style] VARCHAR(255) NOT NULL DEFAULT '' ,[cost] float NULL DEFAULT 0 ) END ALTER TABLE [SL_h].[dbo].[slbj_cost] ADD CONSTRAINT av0_style_date_slbj_cost UNIQUE(av0_style,[date],cost) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[web_order] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[date] VARCHAR(8) NOT NULL DEFAULT '' ,[status] NVARCHAR(255) NOT NULL DEFAULT '' ,[total] float NULL DEFAULT 0 ) END ALTER TABLE [SL_h].[dbo].[web_order] ADD CONSTRAINT sl_h_web_order UNIQUE(id) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[kpi] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[kpi_code] NVARCHAR(255) NOT NULL DEFAULT '' ,[section] NVARCHAR(255) NOT NULL DEFAULT '' ,[subsection] NVARCHAR(255) NOT NULL DEFAULT '' ,[source] NVARCHAR(255) NOT NULL DEFAULT '' ,[type] NVARCHAR(255) NOT NULL DEFAULT '' ,[dashboard] NVARCHAR(255) NOT NULL DEFAULT '' ,[responsibility] NVARCHAR(255) NOT NULL DEFAULT '' ,[kpi_name] NVARCHAR(255) NOT NULL DEFAULT '' ) END ALTER TABLE [SL_h].[dbo].[kpi] ADD CONSTRAINT sl_h_kpi_code UNIQUE(kpi_code) WITH (IGNORE_DUP_KEY = ON) 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[stats] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[name] NVARCHAR(255) NOT NULL DEFAULT '' ,[category] NVARCHAR(255) NOT NULL DEFAULT '' ,[value] NVARCHAR(255) NOT NULL DEFAULT '' ,[date] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) END 26.03.14 BEGIN CREATE TABLE [SL_h].[dbo].[supplier_tracker] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY ,[suppplier] NVARCHAR(255) NOT NULL DEFAULT '' ,[date] varchar(8) NOT NULL DEFAULT '' ) END 26.03.14

• Home
• Notebook
• Contact us

• Privacy Statement
• 

Copyleft ☠ 2023