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_PasswordRetrievalQuestion] [varchar](200) NULL,
      [ccm_PasswordRetrievalAnswer] [varchar](200) NULL,
      [ccm_PasswordRetrievalLastUpdatedDT] [smalldatetime] NULL,
      [ccm_PasswordRetrievalLastResetDT] [smalldatetime] NULL,
      [ccm_PasswordRetrievalLastResetRequestDT] [smalldatetime] NULL,
      [ccm_PasswordRetrievalResetRequestCount] [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)
RichardsoetAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:
ahh.. 1 change
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(cast(newid() as 
varchar(40))+cast(newid() as varchar(40)) as varbinary(100)), 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

Open in new window

0
 
ShogunWadeCommented:
Where is your source data coming from ?
0
 
Goodangel MatopeSoftware ArchitectCommented:
where do you want to insert this data from?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RichardsoetAuthor Commented:
I just want to have a sql script that would create a dummy data of 20000 into the the above tables
0
 
imitchieCommented:
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(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
0
 
imitchieCommented:
completely RANDOM + absolutely JUNK data
0
 
imitchieCommented:
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

Open in new window

0
 
RichardsoetAuthor Commented:
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
0
 
RichardsoetAuthor Commented:
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(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
0
 
imitchieCommented:
The 2nd one, i.e. http:#20409956

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

Open in new window

0
 
RichardsoetAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.