none
How to Insert Multiple Records into a Table

    Question

  •  

    I need to insert multiple records into a test/dummy table (about 8000 records).The table has the following 4 columns

     

    EmpID (Int, Not null)

    User (Varchar(50), null)

    UpdatedBy (Varchar(30), not null)

    UpdatedTime (datetime, not null)

     

    The EmpID column is an identity column with a Identity seed of 1 and an Idenity increment of 1.

     

    I need a script that can insert 8000 records into the test table. The UpdatedTime column can use the gatedate ( ) function.

     

    I am using SQL Server 2005. Thanks

    Wednesday, October 01, 2008 7:02 PM

Answers

  • You can use a radom number in conjuction with the char function to get random characters for a user and update user.

     

    If you use just getdate() all the updated time values will be the same.  If you are okay with that then use getdate(); otherwise use the formula I have provided.

     

    Code Snippet

    INSERT INTO Test (user,updatedby,updatedtime)

    SELECT TOP 8000

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) as [user],

     

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) as [updatedby],

     

    dateadd(day,-1*ABS(CHECKSUM(NEWID())) % 10,getdate()) as UpdatedTime

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2

     

     

    Edit: The below is for a static insert.

    Code Snippet

    INSERT INTO Test (user,updatedby,updatedtime)

    SELECT TOP 8000

    'GregJones' as [user],

    'gjones' as [updatedby],

    getdate() as UpdatedTime

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2

     

     

    Wednesday, October 01, 2008 7:34 PM
  •  

    Code Snippet

    declare @Temp table(UserID int IDENTITY(1,1),[User] varchar(50),UpdatedBy varchar(50),UpdatedTime datetime)

    declare @Cur int

    set @Cur=1

    while @Cur < 8001

    begin

    insert into @Temp ([User],UpdatedBy,UpdatedTime)

    select 'GregJones' + cast(@Cur as varchar(5)) ,'JSmith' + cast(@Cur as varchar(5)),getdate()

    set @Cur=@Cur+1

    end

     

    select * from @Temp

     

     

    Wednesday, October 01, 2008 8:02 PM
  •  

    try

     

    Code Snippet

     

    declare @str int

    set @str=0

     

    while @str<8000

    begin

    insert into test ([User],UpdatedBy,UpdatedTime) values ('GregJones', 'JSmith', getdate() )

    set @str = @str+1

    end

     

     

    Wednesday, October 01, 2008 8:12 PM

All replies

  •  

    you need to insert the data from where?

    Where the data is stored? in another table?

     

    Wednesday, October 01, 2008 7:24 PM
  • Try somthing like :

     

    Code Snippet

    insert into dummy table (User,UpdatedBy,UpdatedTime) select Ccol1,col2,getdate() from sourcetable

     

     

    Wednesday, October 01, 2008 7:27 PM
  • You can use a radom number in conjuction with the char function to get random characters for a user and update user.

     

    If you use just getdate() all the updated time values will be the same.  If you are okay with that then use getdate(); otherwise use the formula I have provided.

     

    Code Snippet

    INSERT INTO Test (user,updatedby,updatedtime)

    SELECT TOP 8000

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) as [user],

     

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) +

    CHAR(ABS(CHECKSUM(NEWID()))%26+65) as [updatedby],

     

    dateadd(day,-1*ABS(CHECKSUM(NEWID())) % 10,getdate()) as UpdatedTime

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2

     

     

    Edit: The below is for a static insert.

    Code Snippet

    INSERT INTO Test (user,updatedby,updatedtime)

    SELECT TOP 8000

    'GregJones' as [user],

    'gjones' as [updatedby],

    getdate() as UpdatedTime

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2

     

     

    Wednesday, October 01, 2008 7:34 PM
  •  

    EmpID (Int, Not null)

    User (Varchar(50), null)

    UpdatedBy (Varchar(30), not null)

    UpdatedTime (datetime, not null)

     

     

    The Data can be derived by:

     

    EmpID = is an identity column

    User  = GregJones

    UpdatedBy = JSmith

    UpdatedTime = getdate( )

     

    What i need is a script that can  insert the above data using a loop or something of thaty nature. Thanks

    Wednesday, October 01, 2008 7:36 PM
  •  

    correct me if I'm wrong

    you want to insert same values 

    User  = GregJones

    UpdatedBy = JSmith

     800 times?
    Wednesday, October 01, 2008 7:38 PM
  •  SQLScript wrote:

     

    EmpID (Int, Not null)

    User (Varchar(50), null)

    UpdatedBy (Varchar(30), not null)

    UpdatedTime (datetime, not null)

     

     

    The Data can be derived by:

     

    EmpID = is an identity column

    User  = GregJones

    UpdatedBy = JSmith

    UpdatedTime = getdate( )

     

    What i need is a script that can  insert the above data using a loop or something of thaty nature. Thanks

    If you want static values you can use the code I posted above and replace the values with static ones, such as GregJones.

    Wednesday, October 01, 2008 7:40 PM
  • Yes 8000 times. Thanks.

     

    Wednesday, October 01, 2008 7:43 PM
  •  

    Code Snippet

    declare @Temp table(UserID int IDENTITY(1,1),[User] varchar(50),UpdatedBy varchar(50),UpdatedTime datetime)

    declare @Cur int

    set @Cur=1

    while @Cur < 8001

    begin

    insert into @Temp ([User],UpdatedBy,UpdatedTime)

    select 'GregJones' + cast(@Cur as varchar(5)) ,'JSmith' + cast(@Cur as varchar(5)),getdate()

    set @Cur=@Cur+1

    end

     

    select * from @Temp

     

     

    Wednesday, October 01, 2008 8:02 PM
  •  

    try

     

    Code Snippet

     

    declare @str int

    set @str=0

     

    while @str<8000

    begin

    insert into test ([User],UpdatedBy,UpdatedTime) values ('GregJones', 'JSmith', getdate() )

    set @str = @str+1

    end

     

     

    Wednesday, October 01, 2008 8:12 PM