健康一贴灵，专注医药行业管理信息化

MSSQL 客户（CLIENT）建表SQL 及抽取语句；

建表SQL 及抽取语句；

--更新部门编号 
update tblsystemuserbase set businessunitid = b.businessunitcode 
from tblbusinessunitbase_bak as b 
where tblsystemuserbase.businessunitid = b.businessunitid

 

--用户表
-- ----------------------------
DROP TABLE IF EXISTS tblsystemuserbase;
CREATE TABLE tblsystemuserbase (
  tblsystemuserid varchar(40) ,
  username varchar(40) ,
  createdbyname text ,
  createdby varchar(200) ,
  modifiedbyname text ,
  modifiedby varchar(200) ,
  createdon text NOT NULL,
  modifiedon text NOT NULL,
  businessunitid varchar(50) ,
  businessunitidname text ,
  statecode varchar(40)  DEFAULT '0',
  statecodename varchar(200) ,
  usercode varchar(50) ,
  telephone varchar(100) ,
  emailaddress varchar(100) ,
  parentsystemuserid varchar(40) ,
  parentsystemuseridname text ,
  tblbusinessunitid varchar(50) ,
  tblbusinessunitidname text ,
  password varchar(200) ,
  privilegesmodifiedon text,
  passwordmodifiedon text,
  owningorganizationid varchar(40)  DEFAULT '00000000-0000-0000-0000-000000000000',
  owningorganizationidname text ,
  avatar text ,
  position varchar(100) ,
  rekcode varchar(100) ,
  canloginweb varchar(100),
  t1 varchar(100) ,
  isvisible varchar(1)  DEFAULT '1',
  isvisiblename text ,
  expiration_date text,
  canloginwebname text 
)

 

CREATE TABLE [dbo].[CLIENT](
    [ID] [int] IDENTITY(10000000,1) NOT NULL,
    [NAME] [nvarchar](100) NOT NULL,
    [REGISTEREDNumber] [nvarchar](50) NULL,
    [ALIAS1] [nvarchar](100) NULL,
    [ALIAS2] [nvarchar](100) NULL,
    [KEY1] [nvarchar](20) NULL,
    [KEY2] [nvarchar](20) NULL,
    [KEY3] [nvarchar](20) NULL,
    [KEY0] [nvarchar](20) NULL,
    [REGIONCODE] [nvarchar](20) NULL,
    [REGIONNAME] [nvarchar](100) NULL,
    [SAPID] [nvarchar](20) NULL,
    [CATEGORY] [nvarchar](20) NOT NULL,
    [LEVEL1] [nvarchar](20) NULL,
    [LEVEL2] [nvarchar](20) NULL,
    [DEPART1] [nvarchar](20) NULL,
    [DEPART2] [nvarchar](20) NULL,
    [DEPART3] [nvarchar](20) NULL,
    [DEPART4] [nvarchar](20) NULL,
    [DEPART5] [nvarchar](20) NULL,
    [PINYIN] [nvarchar](20) NULL,
    [ADDRESS] [nvarchar](100) NULL,
    [TELPHONE] [nvarchar](20) NULL,
    [EMAIL] [nvarchar](30) NULL,
    [POSTCODE] [nvarchar](6) NULL,
    [ISAGREEMENT] [nvarchar](6) NULL,
    [ISFLOW] [nvarchar](10) NULL,
    [ISCHAIN] [nvarchar](10) NULL,
    [PARENTID] [int] NULL,
    [TYPE1] [nvarchar](20) NULL,
    [TYPE2] [nvarchar](20) NULL,
    [TYPE3] [nvarchar](20) NOT NULL,
    [CREATEBY] [nchar](10) NULL,
    [CREATETIME] [smalldatetime] NULL,
    [MODIFYBY] [nchar](10) NULL,
    [MODIFYTME] [smalldatetime] NULL,
    [NOTE] [nvarchar](100) NULL
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'NAME'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'机构代码证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'REGISTEREDNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'曾用名1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ALIAS1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'曾用名2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ALIAS2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键字1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'KEY1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键字2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'KEY2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键字3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'KEY3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排除关键字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'KEY0'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地区编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'REGIONCODE'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地区全称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'REGIONNAME'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SAP编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'SAPID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'CATEGORY'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'级别1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'LEVEL1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'级别2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'LEVEL2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OTC部' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'DEPART1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'DEPART2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'DEPART3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'DEPART4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'DEPART5'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'拼音简写' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'PINYIN'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ADDRESS'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'TELPHONE'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮编' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'POSTCODE'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否协议' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ISAGREEMENT'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否发流向' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ISFLOW'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否连锁' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'ISCHAIN'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'PARENTID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'TYPE1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'TYPE2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'TYPE3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'CREATEBY'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'CREATETIME'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'MODIFYBY'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'MODIFYTME'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CLIENT', @level2type=N'COLUMN',@level2name=N'NOTE'
GO
insert client(
name,regionname,
category,level1,
level2,type1,type2,
isagreement,isflow,
ischain,address,
alias1,note,alias2,type3)
select name,countyid,
accountclassificationcode,right(accountclassificationcode2,10),
account_grade_level,st_type,account_grade,
0,acc_ispost,
0,address,
accountclassificationcode2 ,
account_tag ,parentaccountid,'' from  accountbase

 

 

CREATE TABLE [dbo].[LR_REGION](
    [countyid] [varchar](40) NULL,
    [countycode] [varchar](20) NULL,
    [parentcode] [varchar](20) NULL,
    [level] [int] null,
    [countyname] [varchar](50) NULL,
    [citycode] [varchar](20) NULL,
    [cityname] [varchar](50) NULL,
    [provincecode] [varchar](20) NULL,
    [provincename] [varchar](50) NULL,
    [fullname] [varchar](200) NULL,
    [depart_1] [varchar](20) NULL,
    [depart_2] [varchar](20) NULL,
    [depart_3] [varchar](20) NULL,
    [depart_4] [varchar](20) NULL,
    [depart_5] [varchar](20) NULL,
    [statecode] [int])


insert [LR_REGION] (countyid,countycode,countyname,citycode,cityname )
select countyid,code,name,city_code,cityidname from countybase

 

posted @ 2022-02-19 14:30  一贴灵  阅读(186)  评论(0)  编辑  收藏  举报
学以致用，效率第一