Richardsoet
asked on
SQL2005 Insert
Please write a sql script that would insert 20,000 records at a go on the below table
CREATE TABLE [dbo].[tb_CCMemberTest](
[ccm_SID] [int] IDENTITY(10234851,1) NOT FOR REPLICATION NOT NULL,
[ccm_ccl_SID] [int] NOT NULL,
[ccm_title] [varchar](50) NULL,
[ccm_FirstName] [varchar](50) NOT NULL,
[ccm_MiddleName] [varchar](50) NULL,
[ccm_LastName] [varchar](50) NOT NULL,
[ccm_DateOfBirth] [smalldatetime] NULL,
[ccm_HomeAddress1] [varchar](50) NULL,
[ccm_HomeAddress2] [varchar](50) NULL,
[ccm_HomeAddress3] [varchar](50) NULL,
[ccm_HomeAddress4] [varchar](50) NULL,
[ccm_HomeAddressTown] [varchar](50) NULL,
[ccm_HomeAddressCounty] [varchar](50) NULL,
[ccm_HomeAddressPostCode] [varchar](10) NULL,
[ccm_HomeTelephoneNumber] [varchar](25) NULL,
[ccm_WorkFaxNumber] [varchar](25) NULL,
[ccm_P4UUsername] [varchar](25) NULL,
[ccm_P4UPassword] [varchar](25) NOT NULL,
[ccm_MainContact] [tinyint] NOT NULL DEFAULT (0),
[ccm_P4UAdministrator] [tinyint] NOT NULL DEFAULT (0),
[ccm_EnteredDT] [smalldatetime] NOT NULL,
[ccm_Active] [tinyint] NOT NULL ,
[ccm_AuditDT] [datetime] NOT NULL,
[ccm_AuditESID] [int] NOT NULL,
[ccm_wpb_SID] [int] NULL,
[ccm_JobTitle] [varchar](255) NULL,
[ccm_WorkTelephoneNumber] [varchar](25) NULL,
[ccm_TempMember] [tinyint] NOT NULL DEFAULT (0),
[ccm_InPPScheme] [tinyint] NOT NULL DEFAULT (0),
[ccm_PPAdministrator] [tinyint] NOT NULL DEFAULT (0),
[ccm_tempperksvalue] [varchar](25) NULL,
[ccm_CorpName] [varchar](10) NULL,
[ccm_HomeAddressState] [varchar](20) NULL,
[ccm_UserPassword] [varbinary](256) NULL,
[ccm_ChangePassword] [tinyint] NULL,
[ccm_LastLoggedOnDT] [smalldatetime] NULL,
[ccm_IsFirstTime] [tinyint] NOT NULL DEFAULT (1),
[ccm_MobileNo] [varchar](25) NULL,
[ccm_UserUsername] [varchar](50) NULL,
[ccm_CorpSiteAccess] [tinyint] NULL DEFAULT (0),
[ccm_InternalAdminNotes] [varchar](255) NULL,
[ccm_PasswordHint] [varchar](100) NULL,
[ccm_AuditCCM_SID] [int] NULL,
[ccm_ExternalID] [varchar](50) NULL,
[ccm_ImportedByCCM_SID] [int] NULL,
[ccm_CompanySetupMember] [tinyint] NOT NULL DEFAULT (0),
[ccm_CompanySetupExpiryDT] [smalldatetime] NULL,
[ccm_Gender] [varchar](50) NULL,
[ccm_BuildingsInsRenewal] [datetime] NULL,
[ccm_ContentsInsRenewal] [datetime] NULL,
[ccm_CarInsRenewal] [datetime] NULL,
[ccm_ExistingRSAMember] [tinyint] NULL,
[ccm_SelfRegistrationFlag] [tinyint] NOT NULL DEFAULT (0),
[ccm_PasswordRetrievalQues tion] [varchar](200) NULL,
[ccm_PasswordRetrievalAnsw er] [varchar](200) NULL,
[ccm_PasswordRetrievalLast UpdatedDT] [smalldatetime] NULL,
[ccm_PasswordRetrievalLast ResetDT] [smalldatetime] NULL,
[ccm_PasswordRetrievalLast ResetReque stDT] [smalldatetime] NULL,
[ccm_PasswordRetrievalRese tRequestCo unt] [int] NULL,
[ccm_CoExternalRef1] [varchar](255) NULL,
[ccm_CoExternalRef2] [varchar](255) NULL,
[ccm_CoExternalRef3] [varchar](255) NULL,
[ccm_CoExternalRef4] [varchar](255) NULL,
[ccm_CoExternalRef5] [varchar](255) NULL,
[ccm_JoinCompanyDT] [datetime] NULL,
[ccm_LeaveCompanyDT] [datetime] NULL,
[ccm_LeaveCompanyAdvisedDT ] [datetime] NULL,
[ccm_InvalidLoginCount] [int] NOT NULL DEFAULT (0),
[ccm_MemberDeniedAccess] [tinyint] NOT NULL DEFAULT (0),
[ccm_PasswordChangedDT] [datetime] NULL,
[ccm_ld_SID] [int] NULL,
[ccm_NINumber] [varchar](20) NULL,
[ccm_SelfRegCompanyName] [varchar](100) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL)
CREATE TABLE [dbo].[tb_CCMemberTest](
[ccm_SID] [int] IDENTITY(10234851,1) NOT FOR REPLICATION NOT NULL,
[ccm_ccl_SID] [int] NOT NULL,
[ccm_title] [varchar](50) NULL,
[ccm_FirstName] [varchar](50) NOT NULL,
[ccm_MiddleName] [varchar](50) NULL,
[ccm_LastName] [varchar](50) NOT NULL,
[ccm_DateOfBirth] [smalldatetime] NULL,
[ccm_HomeAddress1] [varchar](50) NULL,
[ccm_HomeAddress2] [varchar](50) NULL,
[ccm_HomeAddress3] [varchar](50) NULL,
[ccm_HomeAddress4] [varchar](50) NULL,
[ccm_HomeAddressTown] [varchar](50) NULL,
[ccm_HomeAddressCounty] [varchar](50) NULL,
[ccm_HomeAddressPostCode] [varchar](10) NULL,
[ccm_HomeTelephoneNumber] [varchar](25) NULL,
[ccm_WorkFaxNumber] [varchar](25) NULL,
[ccm_P4UUsername] [varchar](25) NULL,
[ccm_P4UPassword] [varchar](25) NOT NULL,
[ccm_MainContact] [tinyint] NOT NULL DEFAULT (0),
[ccm_P4UAdministrator] [tinyint] NOT NULL DEFAULT (0),
[ccm_EnteredDT] [smalldatetime] NOT NULL,
[ccm_Active] [tinyint] NOT NULL ,
[ccm_AuditDT] [datetime] NOT NULL,
[ccm_AuditESID] [int] NOT NULL,
[ccm_wpb_SID] [int] NULL,
[ccm_JobTitle] [varchar](255) NULL,
[ccm_WorkTelephoneNumber] [varchar](25) NULL,
[ccm_TempMember] [tinyint] NOT NULL DEFAULT (0),
[ccm_InPPScheme] [tinyint] NOT NULL DEFAULT (0),
[ccm_PPAdministrator] [tinyint] NOT NULL DEFAULT (0),
[ccm_tempperksvalue] [varchar](25) NULL,
[ccm_CorpName] [varchar](10) NULL,
[ccm_HomeAddressState] [varchar](20) NULL,
[ccm_UserPassword] [varbinary](256) NULL,
[ccm_ChangePassword] [tinyint] NULL,
[ccm_LastLoggedOnDT] [smalldatetime] NULL,
[ccm_IsFirstTime] [tinyint] NOT NULL DEFAULT (1),
[ccm_MobileNo] [varchar](25) NULL,
[ccm_UserUsername] [varchar](50) NULL,
[ccm_CorpSiteAccess] [tinyint] NULL DEFAULT (0),
[ccm_InternalAdminNotes] [varchar](255) NULL,
[ccm_PasswordHint] [varchar](100) NULL,
[ccm_AuditCCM_SID] [int] NULL,
[ccm_ExternalID] [varchar](50) NULL,
[ccm_ImportedByCCM_SID] [int] NULL,
[ccm_CompanySetupMember] [tinyint] NOT NULL DEFAULT (0),
[ccm_CompanySetupExpiryDT]
[ccm_Gender] [varchar](50) NULL,
[ccm_BuildingsInsRenewal] [datetime] NULL,
[ccm_ContentsInsRenewal] [datetime] NULL,
[ccm_CarInsRenewal] [datetime] NULL,
[ccm_ExistingRSAMember] [tinyint] NULL,
[ccm_SelfRegistrationFlag]
[ccm_PasswordRetrievalQues
[ccm_PasswordRetrievalAnsw
[ccm_PasswordRetrievalLast
[ccm_PasswordRetrievalLast
[ccm_PasswordRetrievalLast
[ccm_PasswordRetrievalRese
[ccm_CoExternalRef1] [varchar](255) NULL,
[ccm_CoExternalRef2] [varchar](255) NULL,
[ccm_CoExternalRef3] [varchar](255) NULL,
[ccm_CoExternalRef4] [varchar](255) NULL,
[ccm_CoExternalRef5] [varchar](255) NULL,
[ccm_JoinCompanyDT] [datetime] NULL,
[ccm_LeaveCompanyDT] [datetime] NULL,
[ccm_LeaveCompanyAdvisedDT
[ccm_InvalidLoginCount] [int] NOT NULL DEFAULT (0),
[ccm_MemberDeniedAccess] [tinyint] NOT NULL DEFAULT (0),
[ccm_PasswordChangedDT] [datetime] NULL,
[ccm_ld_SID] [int] NULL,
[ccm_NINumber] [varchar](20) NULL,
[ccm_SelfRegCompanyName] [varchar](100) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL)
Where is your source data coming from ?
where do you want to insert this data from?
ASKER
I just want to have a sql script that would create a dummy data of 20000 into the the above tables
set nocount on
declare @i int set @i = 0
while @i < 20000
begin
insert into tb_CCMemberTest
select ascii(newID()), ascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), cast(newID() as varchar(255)), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), ascii(newID()), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),20), cast(newid() as varchar(40))+cast(newid() as varchar(40)), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), left(cast(newID() as varchar(50)),25), cast(newID() as varchar(50)), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(100)), ascii(newID()), cast(newID() as varchar(50)), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), cast(newID() as varchar(200)), cast(newID() as varchar(200)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), left(cast(newID() as varchar(50)),20), cast(newID() as varchar(100)), newid()
set @i = @i + 1
end
set nocount off
declare @i int set @i = 0
while @i < 20000
begin
insert into tb_CCMemberTest
select ascii(newID()), ascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi
set @i = @i + 1
end
set nocount off
completely RANDOM + absolutely JUNK data
sorry, made a mistake with the IDENTITY column, leave that out, so
set nocount on
declare @i int set @i = 0
while @i < 20000
begin
insert into tb_CCMemberTest
selectascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), ascii(newID()), cast(newID() as varchar(255)), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), ascii(newID()), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),20), cast(newid() as varchar(40))+cast(newid() as varchar(40)), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), left(cast(newID() as varchar(50)),25), cast(newID() as varchar(50)), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(100)), ascii(newID()), cast(newID() as varchar(50)), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), cast(newID() as varchar(50)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), ascii(newID()), cast(newID() as varchar(200)), cast(newID() as varchar(200)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), left(cast(newID() as varchar(50)),20), cast(newID() as varchar(100)), newid()
set @i = @i + 1
end
set nocount off
ASKER
Which out of the two should i used for teh insert , the second on eposted or first one
If the first one posted ,I can not copy and paste please help
If the first one posted ,I can not copy and paste please help
ASKER
Error message :when use the below script
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
set nocount on
declare @i int set @i = 0
while @i < 100
begin
insert into tb_CCMemberTest
select ascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), cast(newID() as varchar(255)), left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), ascii(newID()), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),20), cast(newid() as varchar(40))+cast(newid() as varchar(40)), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), left(cast(newID() as varchar(50)),25), cast(newID() as varchar(50)), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(100)), ascii(newID()), cast(newID() as varchar(50)), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), cast(newID() as varchar(200)), cast(newID() as varchar(200)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newi d())*16+as cii(newid( )),getdate ()), ascii(newID()), left(cast(newID() as varchar(50)),20), cast(newID() as varchar(100)), newid()
set @i = @i + 1
end
set nocount off
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
set nocount on
declare @i int set @i = 0
while @i < 100
begin
insert into tb_CCMemberTest
select ascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), dateadd(day,128-ascii(newi
set @i = @i + 1
end
set nocount off
The 2nd one, i.e. http:#20409956
Is it too long?
I put line breaks in it
Is it too long?
I put line breaks in it
set nocount on
declare @i int set @i = 0
while @i < 20000
begin
insert into tb_CCMemberTest
selectascii(newID()), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)),
cast(newID() as varchar(50)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), cast(newID() as varchar(50)),
cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)), cast(newID() as varchar(50)),
cast(newID() as varchar(50)), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),25),
left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25), left(cast(newID() as varchar(50)),25),
ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), ascii(newID()), cast(newID() as varchar(255)),
left(cast(newID() as varchar(50)),25), ascii(newID()), ascii(newID()), ascii(newID()), left(cast(newID() as
varchar(50)),25), left(cast(newID() as varchar(50)),10), left(cast(newID() as varchar(50)),20), cast(newid() as
varchar(40))+cast(newid() as varchar(40)), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
ascii(newID()), left(cast(newID() as varchar(50)),25), cast(newID() as varchar(50)), ascii(newID()), cast(newID() as
varchar(255)), cast(newID() as varchar(100)), ascii(newID()), cast(newID() as varchar(50)), ascii(newID()), ascii(newID()),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), cast(newID() as varchar(50)),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()), ascii(newID()), cast(newID() as varchar(200)),
cast(newID() as varchar(200)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
ascii(newID()), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID() as varchar(255)), cast(newID()
as varchar(255)), cast(newID() as varchar(255)), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()),
ascii(newID()), ascii(newID()), dateadd(day,128-ascii(newid())*16+ascii(newid()),getdate()), ascii(newID()),
left(cast(newID() as varchar(50)),20), cast(newID() as varchar(100)), newid()
set @i = @i + 1
end
set nocount off
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Error message when execute the above script please modify
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.