Import data into tables ramdomly

[nicoalmontec]

Hi folks, this is my first post in danniweb, please excuse me if I am in the wrong place or the thread title lacks self explanation. I am very disoriented in the field since im new in MS SQL.

Before going into the problem, I'm going to give some context:

As an assigment, I'm required to create a small simple DB in MS SQL 2008 for a store. It is supposed to have 3 entities: Customers, Products and Sales. This is the design I came out with:

*Customer (customerId (PK), name, lastname, birthdate, birthplace)

*Customer-phone# (Customer-phone#Id(PK), customerId(FK), phone-number, phone#Id(FK))

*Phone# ( Phone#Id(PK), number, type )

*Products( productId(PK), name, department, expiration-date, price )

*Sales (saleId(PK), customerId(FK))

*Sales-details( sales-detailId(PK), saleId(FK), date, productId(FK), quantity, totalprice )

I was given four '.txt' files, each one of them have the data that I'm supposed to import into the database. I have one file with names, one file with lastnames, and three more files with product names: 'barsoap.txt' contain product names for soap, the same for 'mouthwash.txt', 'skincare.txt' and 'toothpaste.txt'.

Lets start with the names:

In my scheme, the customer name is divided in two fields: name and lastname. I have firstname.txt and lastname.txt with the data i have to import, the problem is that firstnames.txt and lastname.txt dont have the same amount of records, therefore, if I try importing them using the wizard, I wouldnt have enough names for the exeeding lastnames (I already tried it and thats what happened). My instructor told me I should be able to write a stored procedure that generates ramdom names combining the first and lastnames from the files, but I dont have even a clue on how to do so..

Now with the products:

Now if we take a look back again on my scheme, i got a 'department' field that is supposed to be filled according to the category of the product. Since i have 'barsoap.txt', 'mouthwash.txt', 'skincare.txt' and 'toothpaste.txt', i decided to divide them into two categories" mouthwash and toothpaste would be dental care and skincare and barsoap would be body care. Anyway I have to write another stored procedure that assigns their respective departments to each entry and i dont know how to do it either..

And finally, the sales issue:

This assignment was supposed to be about generating 100,000 ramdom sales using stored procedures, that means filling the sales table with ramdomly selected data from the previous tables (wich are supposed to be previously filled up with the data from the text files).

I would appreciate very much your help, and excuse me again if im not being clear on what i need to do.

Thanks,

Nicolas.

[BitBlt]

For homework help requests, no work posted usually = no effort made by poster, which in turn = no help given. You should post what you HAVE tried and then more assistance should be forthcoming.

However (at the risk of offending the purists) I will give one hint: load the names into temp tables, that way you can access them as needed instead of having to get them from the text file, then use a nested cursor through the tables to construct first/last combinations.

Hope this helps a little. Good luck!

[nicoalmontec]

I would've posted what I tried if I knew how to do something, however i know I have to put some effort into it, therefore im going to use your advice and start searching for temp tables and hit back as soon as i come up with something, and thanks for the fast reply

[BitBlt]

No problem. But, don't forget, that this board is really more to help people when they're stuck with something they're working on, not to teach from the ground up. Your instructor and Google are for that.

[nicoalmontec]

Okay, now i really have something to be stuck into, below is all the code i have done so far, once again im going to explain whats happening with some context so that you can see where the problem is..

I am creating a temp table called #names

CREATE TABLE #names(IdName int IDENTITY(1,1) NOT NULL, Name varchar(50))

Then, I insert the data from the .txt i was given:

GO
		INSERT INTO #names(Name)
		SELECT *
		FROM
		OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
		DEFAULTDIR=C:\;Extensions=TXT;','SELECT * FROM FIRSTNAMES.txt')

Now I create a structure that allows me to select a ramdom IdName making sure it is within the actual data.

DECLARE @LowerN int
       DECLARE @UpperN int
       DECLARE @RandomN INT
       SET @LowerN= (SELECT MIN(IdName) FROM #names)
       SET @UpperN= (SELECT MAX(IdName) FROM #names)
       SET @RandomN = ROUND(((@UpperN - @LowerN -1) * RAND() + @LowerN), 0)

Now, I want to insert those generated names to a permanent table i already have created, here is the design:

Customers(Id_Customer(int,PK), Name(varchar(50)))..it has some more fields, but for now these ones are the ones im working on and the only ones being affected by what im trying to do.

Before going into the insert, I decided to store the select query into a variable like this:

DECLARE @selName varchar
       SET @selName= 
       (select  Name from #names  where IdName=  @RandomN)

THEN this insert:

WHILE @counter < 20
      BEGIN
      SET @counter = @counter + 1
		  
		  INSERT INTO Customer(Name)
		  VALUES (@selName)
		   
	   END

NOTE: I did declare the variable @counter

This loop is supposed to insert 20 names into Customer, but all it does is insert either nothing or the same name over and over or ramdom single letters over and over like 'W', 20 Ws.. im stuck!

[nicoalmontec]

Self-answer:

I just had to put the 3rd and 4rth codeblocks i posted here inside of the insert loop and done, im good to go for now :p..lets see what happens with the ramdom dates