Post reply

Sample Data Required

  • June 26, 2008 at 6:30 am

    #208991

    Afternoon all,

    I'm studying for my 70-431 exam and would like to do a bulk insert of data but do not have any sample data. Does anyone have a simple list, i.e. id, firstname, lastname that they could provide me with?

    It would be MUCH appreciated.

    Thanks in advance,

    Richard

  • June 26, 2008 at 7:03 am

    #834603

    You could grab some out of the AdventureWorks database that ships with SQL

  • June 26, 2008 at 8:13 am

    #834691

    Thats exactly what I did 😉

    Cheers

  • June 26, 2008 at 9:00 am

    #834750

    aside from that but on the same topic, redgate has a new tool that will generate test data for you, i have only used it once but it seems quite good.

    Gethyn Elliswww.gethynellis.com

  • June 26, 2008 at 12:21 pm

    #834919

    If you don't need the data to actually mean anything, you can generate unlimited random data by doing something like:

    ;with Numbers (Number) as

    (select row_number() over (order by t1.name)

    from sys.all_objects t1

    cross join sys.all_objects t2)

    select checksum(new_id())

    from Numbers

    Put a "top X" in the CTE to get a specific number of rows. Add more columns as desired. Cast the data to specific formats if you like.

    Once you've generated that data, save it to a text file, and use it for bulk importing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 26, 2008 at 7:40 pm

    #835083

    In that same vein...

    DROP TABLE JBMTest

    GO

    --===== Create and populate a 1,000,000 row test table.

    -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

    -- for all rows.

    -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- Jeff Moden

    SELECT TOP 1000000

    SomeID = IDENTITY(INT,1,1),

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

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

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

    SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

    --===== A table is not properly formed unless a Primary Key has been assigned

    -- Takes about 1 second to execute.

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (SomeID)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply