Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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)
0
Richardsoet
Asked:
Richardsoet
1 Solution
 
ShogunWadeCommented:
Where is your source data coming from ?
0
 
Goodangel MatopeSoftware ArchitectCommented:
where do you want to insert this data from?
0
 
RichardsoetAuthor Commented:
I just want to have a sql script that would create a dummy data of 20000 into the the above tables
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
imitchieCommented:
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now