Dear All,
I have some problems with the assignment from my professor.
Suppose there are two tables storing first names and last names, one table with column of first name and gender, the other with last name.
how to write stored procedures to get a random first name + random last name for Males or Female. like you got a list of all M & F first name and last name, then you try to match first name with any other last name (I believe this is random).
and the task is to write a script so each time when a female or male full name is need, it will return a random name for the requested gender.
I am totally struck and no idea how to deal with it, in fact I am just a beginning in SQL server, and I know there is a stored procedure to generate random number, however, I can't see the use of it here,,,,:eek:
Could anyone help me out ? thanks very much !!!:beer:
I need to generate random strings each of length 7 characters and update it in a table that contains employee data.For example. There is table called as Employee. It has Id, FirstName, LastName & Email as the columns. I need to mask the real names. So i have to generate random characters between a-z unique for individual records and update it in the table.
I went through some websites for generating random characters. These provide a 7 or 8 character random letters. URL...But i do need to update 2 columns of an entire table with random characters.
How to create random number for the value in other colum. Please help and urgent
I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)
If Ori_quantity < 5000 then Rand_quanty as qty = I want Random number within 100 Else If Ori_quantity = 0 or < 10 then Rand_quanty as qty = I want Random number within 7 End if
From tblname where Location = 'DAS'
I have around 2500 field. So when I run the query I expect the result should be like below
I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?
I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?
If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.
hi I have a field call: password, I want to generates some random chars as default password for users (so user got something to login, and update later).
Current Solution I have: I made myself a trigger and it does generate password on insert, but the problem with that is I have to allow NULL on that field. I don't want NULL to be allowed, and if I don't allow null... sql server won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be more applicaions using the same field from the same database. Just in case if the other developer gets sloopy, I don't want other application(s) allow user put to enter a null or leave it blank on my password field.
My question is: 1) can I put a function or some sort at the "Default" field under "Design Table", if so...how? 2) any suggestions for a better implementation? 3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
I have three tables that are joined together to retrieve a piece of data.Table One has the columns pid1(PK), idTable Two has the columns pid1, qid1, idTable three has columns qid1, question, answerHere is my sql statement:SELECT Table1.pid1, Table2.qid1, Table3.Question FROM Table1 JOIN Table2 ON Table1.pid1=Table2.pid1 JOIN Table3 ON Table2.qid1=Table3.qid1 WHERE Table1.pid=...(N)I put an elipsis because I'm stuck on how to proceed from here. See I need to randomly select a value from column pid1 in table1 then use this value to replace N in the sql statement, then have the statement run and randomly return exactly one piece of data instead.Is this possible? How can you randomly get one piece of data from joining three table without a WHERE CLAUSE.
I wanted to check and see if anyone else has experienced an issue with querying sys.databases and not getting all the database rows returned?
I have a query that pulls back a list of databases so I can do backups on them. Randomly I will miss a row or two of databases. If I immediately (in code time) re-issue the same query, I'll get the missing databases. Oddly it seems a more prevalent problem on our Itanium servers, but all our 2005 servers seem to have the same symptom. Also odd, but the master database seems to be most likely to not be in the list (i.e. it happens the most frequently).
It's not a huge deal, but it's a pain to have to run the same query twice, compare the two lists and then double check that the adding or removing of a database should have happened.
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp. Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
On my company's website, we have a quote of the day. I would like to be able to type a hundred or so quotes into a mdf file. Then, I would like to have code that randomly selects one of the quotes every day and posts it.
....What I want is very similar to the "Image Of the Day" section on many websites.
I'm importing comma-delimited text files into a SQL table. The data imports in a seemingly random order. One time I import and the lines appear one way and the next time I import they import another way.
Is there a way to force the text files to import in the same order the data is found in the file?
I am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??
Thanks in advanced,
Edit: User needs to fill his tables with sample data. (Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).
I'm a network admin who understands sql but have no reason to write it everyday so I have no idea how to approach this problem.
I am trying to generate usage statistics of our computer labs. I have written a few scripts that populate a sql 2005 database with the following information:
[datetime] [username] [computer] [event] 2007-10-10 15:25:03 tom earth logon 2007-10-10 15:39:09 john mars logon 2007-10-10 15:41:13 dave pluto logoff 2007-10-10 15:47:29 john mars logoff 2007-10-10 15:59:48 tom earth logoff
Each time a user logs on or logs off one of our lab computers the information is inserted into this database.
It seems that was the easy part. Lacking any local sql experts, I'm pondering where to go from here to generate usage statistics.
What I'm thinking is a SQL query (which I don't know how I'd write) that would calculate the session time and insert it into a new table. A session is defined as the time between a logon and logoff where the username and computer match.
The new table would look something like this:
[user] | [computer] | [sessionTime] | [day] tom | earth | 00:34:45 | 2007-10-10 john | mars | 00:08:20 | 2007-10-10
The logic of the query finds the first logon event and matches it with the first logoff event in which the username and computer match. It then subtracts the logon datetime from the logoff to calculate session time.
Is this a reasonable approach? Is the query easy to write?
Can Anyone tell me how to generate script with all table data from one database.I can generate script with table structure but not with table data.I want to generate with Table data.
I have a question regarding generating SQL script from a database. If you use SQL Server Management Express, it lets you generate SQL script that can be used to recreate table structure, stored procedures, etc. But it does not include the actual data stored in the tables.
i'm not able to generate a sql script with the data in the tables! using sql express. i need this script to ganerate the same database with its datas in a new sql server. thanks in advance
It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
'code'- varchar 255 (Unique number) data : chr456Umx 'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns, The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen @imglink nvarchar(255)
AS
Select code as sku, PdtBarCode as [standard-product-id], 'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title, make as manufacturer,'
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs) exec msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail' @recipients = 'asdfasdfsdf@aafas.com' @from_address = 'asdfasdfacasca@cc.com' @query = 'Select * from failedjobs' @subject = 'List Of Failed Jobs' @attach_query_result_as_file = 1;
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
I've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.
