a small program for generating bulk data.

Plz help me write a small prog for generating random data

It should create a table,
field 1 : ID (should be filled with 5000records of random numbers)
field 2 : name (should be filled with 5000records of random character data, 15length)

Actually i'm confused on whether i should use cursors or what..

Thank you.

The following query may help you...But there is no garnetee about unique values..

Create table #Data

(

Id int,

Name varchar(20)

);

Set NOCOUNT ON;

Declare @.I as Int;

Set @.I = 0;

While @.I<5000

Begin

Insert Into #Data

Select

Cast(Rand() * 100000000as float)

, Char(65 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

Set @.I = @.I + 1;

End

Select * from #Data

Drop table #Data

|||Why is

Set NOCOUNT ON;

|||

To suppress the Row Inserted feedback from your server.. (1 row(s) affected)

It will consume unnecessary time.. It is one of the tuning tips. You can use this wherever required.

a Null check for a single record

Hi,
I have a table that may contains Null filled columns.
I have a SP that do Update and insert.
How can i do a null check to avoid trying to update null fields?
let say Type_1 column is null, I need a condition that will do a quick check (single record is sufficient) to see that it has null value and to skip the update command:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @.Single_Rec int
Begin

-- ??? HERE comes the condition --

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Thanks

Yossi--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END|||Originally posted by eschapir
--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END
Thaks for the reply
but its not working,
maybe i missed some thing|||/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1|||Originally posted by ispaleny
/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Works great...
Thanks mate