June 1, 2011 at 11:38 am
I have two temp tables: ##names and ##lastnames, now what i want to do is to create a stored procedure that generates 1,000 full names using ramdom combinations of names and lastnames from the above temp tables, i want those fullname combinations to be stored in a permanent table called Customers. From previous research, I found out that nested cursors might be the solution to my problem but Im quite new in this and I dont know even how to adapt a nested cursor to do what I need to do.
Basically this is the syntax for the nested cursor
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
but I still fail to understand how to use it according to my case..can anyone give me some alternatives to this or give me a lil push to start with the nested cursors, thanks
June 1, 2011 at 11:41 am
I found the syntax for the nested cursors here http://msdn.microsoft.com/en-us/library/ms180169.aspx
June 1, 2011 at 12:32 pm
this will be boatloads faster and is a single set based query, instead of using a cu..cur... i can't even say the word...it's so evil....
SELECT top 1000 *
FROM (
SELECT
A.FName,
B.LName
FROM FNAMES A
CROSS JOIN LNAMES B--generates every possible value
) MyAlias
ORDER BY NEWID()
Lowell
June 1, 2011 at 1:05 pm
SIMPLY AMAZING!! O.0, it brought my 1,000 names just like that, but I still need to send those names to a permanent table called Customer. This is the table design:
Customer(custId(pk), name, lastname, birthdate, birthplace)
I need to populate this table with the previous name combinations, is that possible?
June 1, 2011 at 1:17 pm
ok, an update to random is certainly possible... you were really thin on the actual firstname/lastname table definitions, so i cannot provide you with a perfect example...
but, based on your table definition, here's a way to update that definition with my sample data seen here:
--random first land last names, based on colors and 6 first names:
;With MyRandomNames(FirstName,LastName AS
( --whatever the actual query you used to join the two tables goes here
)
,MyMatchARownumberToARealTable AS
(
SELECT custId,
FirstName,
LastName,
ROWNUM = ROW_NUMBER() OVER (PARTITION BY custId ORDER BY NEWID())
FROM Customer --THE TABLE WITH THE KEY
CROSS JOIN MyRandomNames --THE TABLE WITH THE RANDOM VALUE
)
UPDATE Customer --THE JOINING TABLE
SET Customer.FirstName = MyMatchARownumberToARealTable.FirstName --THE RANDOM VALUE,
Customer.LASTNAME = MyMatchARownumberToARealTable.LastName --THE RANDOM VALUE
FROM MyMatchARownumberToARealTable
WHERE Customer.custId=MyMatchARownumberToARealTable.custId --FOR EACH KEY
AND ROWNUM = 1 --LIMITS TO ONE ARBITRARY VALUE DUE TO THE ORDER BY NEWID
Lowell
June 3, 2011 at 9:28 am
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!
June 3, 2011 at 10:24 am
yep...you've got to try to get away from cursor/loop line by line thinking.
does this work like you expect?
INSERT INTO #names([Name])
SELECT *
FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=TXT;','SELECT * FROM FIRSTNAMES.txt')
--get an example top 20
SELECT TOP 20 Name
FROM
#names
ORDER BY NEWID()
--go ahead and insert 20 random names into Customer.
INSERT INTO Customer([Name])
SELECT TOP 20
[Name]
FROM #names
ORDER BY NEWID()
Lowell
June 3, 2011 at 10:36 am
the answer its been in front of me since the beginning...
i just had to include, i just had to include this block:
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)
and this other:
DECLARE @selName varchar
SET @selName=
(SELECT Name FROM #names where IdName= @RandomN)
BOTH inside of the loop and thats it.. im done with names!! now lets see what happens with the ramdom dates and phone numbers..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply