Sqlserver2000建表语句
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestVar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestVar]
GO
CREATE TABLE [dbo].[TestVar] (
[aInt] [int] IDENTITY (1, 1) NOT NULL ,
[aBigint] [bigint] NULL ,
[aBinnary] [binary] (50) NULL ,
[aBit] [bit] NULL ,
[aChar] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[aDatetime] [datetime] NULL ,
[aDecimal] [decimal](18, 0) NULL ,
[aFloat] [float] NULL ,
[aImage] [image] NULL ,
[aMoney] [money] NULL ,
[aNChar] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[aNText] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[aNumeric] [numeric](18, 0) NULL ,
[aNvarchar] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[aReal] [real] NULL ,
[aSmalldatetime] [smalldatetime] NULL ,
[aSmallInt] [smallint] NULL ,
[aSmallMoney] [smallmoney] NULL ,
[aSql_Variant] [sql_variant] NULL ,
[aText] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[aTimesTamp] [timestamp] NULL ,
[aTinYint] [tinyint] NULL ,
[aUniQueidentifier] [uniqueidentifier] NULL ,
[aVarBinary] [varbinary] (50) NULL ,
[aVarchar] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
插入1条记录示例
INSERT INTO
[testBase].[dbo].[TestVar]( [aBigint],
[aBinnary], [aBit], [aChar], [aDatetime],
[aDecimal], [aFloat], [aImage], [aMoney],
[aNChar], [aNText], [aNumeric], [aNvarchar],
[aReal], [aSmalldatetime], [aSmallInt], [aSmallMoney],
[aSql_Variant], [aText],
[aTinYint], [aUniQueidentifier], [aVarBinary], [aVarchar])
VALUES( 1,
CAST( 123456 AS BINARY(4) ), '0101', 'aChar', getdate(),
132.00, 123.00,'abcdefghijk' , 123.00,
'aNChar', 'aNText,ntext,>', 12300.00,'<aNvarchar,nvarchar(50),>',
CAST(300 AS real) , getdate(), 123.123, 123.00,
convert(nchar(20), N'AbCdEfGhIjKlMnOpQrStUvWxYz'), '<aText,text,>',
CAST(3 AS TinYint),NEWID() , CAST( REPLICATE( 0x20, 128 ) AS varbinary(32) ),
'<aVarchar,varchar(50),>')
循环插入10000条数据示例
While (select Count(*) from TestVar)<10000
begin
INSERT INTO [testBase].[dbo].[TestVar]( [aBigint],
[aBinnary], [aBit], [aChar], [aDatetime],
[aDecimal], [aFloat], [aImage], [aMoney],
[aNChar], [aNText], [aNumeric], [aNvarchar],
[aReal], [aSmalldatetime], [aSmallInt], [aSmallMoney],
[aSql_Variant], [aText],
[aTinYint], [aUniQueidentifier], [aVarBinary], [aVarchar])
VALUES( 1, CAST( 123456 AS BINARY(4) ), '0101', 'aChar', getdate(),
132.00, 123.00,'abcdefghijk' , 123.00,
'aNChar', 'aNText,ntext,>', 12300.00,'<aNvarchar,nvarchar(50),>',
CAST(300 AS real) , getdate(), 123.123, 123.00,
convert(nchar(20), N'AbCdEfGhIjKlMnOpQrStUvWxYz'), '<aText,text,>',
CAST(3 AS TinYint),NEWID() , CAST( REPLICATE( 0x20, 128 ) AS varbinary(32) ),
'<aVarchar,varchar(50),>')
CONTINUE
End
