Hi,
I have a 2 tables called
1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)
2.tblDept which has Ref(Pk) & Department
How do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept
i am using SQL Server 2000
best regards
I need to know who is entering data in my SQL database. I use Access Project as a front end and would like to know if it is possible to have an 'ID' field auto-populate with a user NT Login. For example, if user checks a check-box or enters data in field, I would like an ID field next to the checkbox or data field to populate with his user ID.
Any assistance is much appreciated!
Thanks.
I have 2 tables in a parent->child relationship. The primary key for the parent table is moving to a new column (that is an Identity). I'd like to relink the tables using the identity field.
Here's the structure:
Parent
refid int (Identity) - NEW PK
orderid char(9) - OLD PK
Child
orderdetailid int - PK
orderid char(9) - Old link to parent
refid int - New link to parent
I've populated refid in the parent. I just need to populate the refid column in the child. I have a VFP background. In VFP I would have looped through the child, found the matching record in the parent, then transfered the data over. I don' t have a clue how to do this in TSQL.
Brian
I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB
Go to the books DB and update it with this classcode
Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field.
I will greatly appreciate your feedback you can provide.
Mike.
UPDATE [dbo].[Books]
SET dbo.Books.Classcode = [dbo].[classcode].[classcode]
From dbo.classcode
On [dbo].[Books].[classcode]= [NULL]
Populate DB field from list in “storage� DB
Mike Campos - CA
record.
For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.
Example Recordset
Record 1, From=0, To=500, Lithcode=AB1
Record 2, From=500, To=1000, Lithcode=CR4
Record 3, From=1000<- trying to figure out how to auto populate previous record's from value here
TIA
Todd
Hi folks:
I have added a new field to an established table, and am having trouble figuring out how to populate its values:
Two tables are involved: Jobs and Parts
There is a one-to-one relationship between each JobID and its PartID
Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.
The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:
UPDATE Jobs
SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))
WHERE (JobID = [the job in question])
My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,
Matt
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
TIA
Todd
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies View RelatedThe following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
I have a dropdownlist that is populated from a DB table. I would like to also include any other values that might be in another table. How do I combine these two queries, so I can get distinct values from both tables combined?SELECT Category
FROM dbo.TABLE1
GROUP BY Category
SELECT Category
FROM dbo.TABLE2
GROUP BY Category
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
Hi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks
I would like to find a utility that can scan through an existing SqlServer 2000 database and create scripts to re-insert/re-populate thedata into another DB with the same table structures. Like a back-uputility.Can anyone recommend one (or tell me which ones to avoid)? Iwould like one that takes dependencies between tables intoconsideration.Thanks
View 1 Replies View RelatedI am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
Â
- First_Name
 - Last_Name
 - Email
 - User_Field_2
 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
When creating a new row or record in the Master table (by an Adapter update), how do I then populate new records in 15 different Child tables?
These Child tables are attributes of the Master table. I am sure there is more than one way to do this...
Thanks
I'm coding in a black hole that has a moving target in it.
Ok, I now need to call an stored procedure and with the results from the stored procedure I need to populate my tables on my handheld.
Any ideas on how I can do this? I was using inline sql "select col1, col2, from table1" and doing my rda pull from that, but now I need to call stored procedures to do this and one sp takes a parameter. How can I accomplish this on handheld ?
Dear Friends,
I created my BI Blog and I need your feedback to my post SSIS-Populating Dimension. Do you think is a good approach? In my project works perfectly!
http://pedrocgd.blogspot.com/
Thanks!
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
Hi,
I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?
Thank you very much!
This is related to one of my previous posts.
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
Any ideas?
Thanks,
BIG
Mike writes "I am trying to learn SQL programming and would like to see if you can provide me with some direction on a small project.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and tag the assigned codes from the storage DB as used (“Y�)
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL).
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB
Go to the books DB and update the first record that does not contain a code with the above classcode
Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field. The existing data would look something like:
dbo.Books
BookidClasscode
A123400001
B123400002
C123400003
D1234
E1234
dbo.classcode
classcodeUsedDate
00001Y5/17/2006 12:00:00 AM
00002Y5/17/2006 12:00:00 AM
00003Y5/17/2006 12:00:00 AM
00004
00005
00006
00007
00008
I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"
Hopefully this will make some sense, I will greatly appreciate any feedback.
Mike."
Guys,
New to SQL and I'm trying to create a new field, entitled “Name� and see all employee names from my "Employee" table using the following format:
LastName, FirstName, jobcode, emailaddress and hire date.
after much effort my code is as follows:
CREATE VIEW 'Name'
AS
SELECT LastName, FirstName, JobID, Email, Hiredate AS Relationship
FROM 'Name'
Create View 'Name' as Select LastName, FirstName, JobID, Email, Hiredate From EMPLOYEE
Group By LastName, FirstName, JobID, Email, Hiredate Order By LastName
But i'm still getting the following errors:
Msg 111, Level 15, State 1, Line 196
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 199
Incorrect syntax near 'Name'.
Msg 111, Level 15, State 1, Line 200
'CREATE VIEW' must be the first statement in a query batch.
Any assistance is greatly appreciated
I posted a similar question in the SQL7 forum but am not getting any replys.
"I need to move 65 tables onto their own filegroup. Is there anyway to do this without scripting the tables first and BCP etc? My concern is the dependant stored procedures, I don't want to have to script these also as there are hundreds of them."
If I wanted to drop and recreate some tables, how would I do so without upsetting the dependant stores procedures on these tables?
If I inserted the names into a table and wrote a cursor to pull out the dependant id's from the sysdepends table, then put them back after the drop and recreate (matching the old dependants with the new object ids) what kind of trouble am getting myself into? Thanks Patrick
Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.
For exameple:
Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
This column can be an auto-incrementing IDENTITY column.
Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.
The contents of the table would look like this
ClientID OrderID
------- -------
1 0
1 1
2 0
1 2
2 1
2 2
1 3
and so on....
MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
I know this can be done manually using triggers, but I was wondering if there was a better way...
Thanks in advance
Hi All,
It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!
I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.
The Problem?
Tables:
1.tblRisk: Ref(pk), subject, status(fk), staff(fk), Dept(fk)
2.tblDept: Ref(pk), Department
The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.
Many Thanks,
Kunal
CREATE PROCEDURE dbo.ShowMe @yourInputValue INTAS SELECT tblDept.Department FROM tblDept JOIN tblRisk ON tblDept.Ref = tblRisk.Dept WHERE tblDept.Ref = @yourInputValue RETURN 0GO
Hi all,
Sorry if it looks a little cluttered!I have these two tables: CAMPAIGN(MEDIACODE varchar(10) UncheckedSPECIALOFFERCODE varchar(15) UncheckedLAYOUT varchar(10) UncheckedHEADERTEXT varchar(100) CheckedSORTORDER varchar(10) UncheckedSORTORDERCOLUMN varchar(50) UncheckedWIDTH varchar(50) Checked)andPROMORATEVIEW(MEDIACODE varchar(10) CheckedSPECIALOFFERCODE varchar(15) CheckedCAMPAIGNCODE varchar(15) CheckedNUMBEROFISSUES smallint CheckedRATE decimal(9, 0) CheckedDESPATCHMETHODCODE varchar(50) Checked)CAMPAIGN HAS ONLY ONE ROW:(CE DG8398 GRID ASC NUMBEROFISSUES NULL)ANDPROMORATEVIEWTOO MANY AND HERE A VERY SMALL RANGE OF RECORDSMEDIACODE SPECIALOFFERCODE CAMPAIGNCODE NUMBEROFISSUES RATE DESPATCHMETHODCODE(...CE CER1R02 CER1 12 429 WCE CER1R03 CER1 24 829 WCE CER1R03 CER1 12 429 WCE DG8398 DG8398 12 411 FCE DG8398 DG8398 12 405 1CE DG8399 DG8399 12 399 WCE DG8399 DG8399 12 735 1CE DG8399 DG8399 12 756 ACE DG8400 DG8400 12 756 ACE DG8400 DG8400 12 396 W...)Now the question:Why these two OUTER JOINS RETURN the same number of rows in My Sql2000 & 2005 express???1.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE2.SELECT DISTINCT P.MEDIACODE, P.SPECIALOFFERCODE,CD.MEDIACODEFROM CAMPAIGN AS CD RIGHT OUTER JOINPROMORATEVIEW AS P ON P.MEDIACODE = CD.MEDIACODE AND P.SPECIALOFFERCODE = CD.SPECIALOFFERCODE AND CD.SORTORDER IS NULLIf you still with me here is what I am trying to do:to right outer join the campaign with promorateview and the if it's not overriden in campaign( there is no record for MEDIACODE, SPECIALOFFERCODE in campaign ) I will use that view to override the default values.I suspect that the last statement makes no "influence" on right outer join.Another work around for me now is that if I create a view that has these values and then filter it with VIEW.SORTORDER IS NULL it does the job but I am trying to get rid of this extra view.Thanks for your time, avarair
Hello,
i am trying to subscribe to a report that has dependant parameters, for example Country->City and Year-> Month.
Those values are coming from a Analysis Services cube dimensions.
when i come to it in the subscription window, some of the parameters are populated and some are not.
I cannot detect a pattern.
Does anyone know what can be the problem?
thank you.
I have a problem.I should rename some tables which are referenced in numerous storedprocedures.Is there any way to automatically replace old table name with new one in alldependant stored procedures,instead of manual replacement in every stored procedure ehich would betime-consuming?Thank you in advance.
View 4 Replies View RelatedHello,
I hope someone can help me on this one, as I am getting a bit frustrated with my research..
I have procA on ServerA.DatabaseA and it uses an input parameter to get a value that determines which server to ping.
If this parameter is @paramA = 1, then query into LinkedSrvX. If @paramB = 2, then query into LinkedSrvY... etc.
Is there an easier way to do this or a best practice? I really don't want to go into repeating my queries for every server. Here is a tsql mini version of what I am trying to enhance as my original queries are 1000 lines long and I need to get away from the repeated queries. I do have queries that ping within the server and to other linked servers as well without issues.
All linked servers referenced below have the same structure of tables. I also need to avoid situations incase the server may be down, this proc should not return errors.
My Partial Procedure
CREATE PROC procA
@paramA varchar(1) AS
IF (@paramA = '1')
BEGIN
SELECT ColA, ColB
FROM LinkedSrvX.Database0.Owner.Table0
END
IF (@paramA = '2')
BEGIN
SELECT ColA, ColB
FROM LinkedSrvY.Database0.Owner.Table0
END
IF (@paramA = '3')
BEGIN
SELECT ColA, ColB
FROM LinkedSrvZ.Database0.Owner.Table0
END
GO
All ideas / suggestions are welcomed.
Thanks!
-Ashvi
1. I have a table with data like this:
PersonID
DateTime
Temperature
Pressure
2. I want to build a view into this table so that it shows up as follows:
PersonID DateTime1 DateTime2 DateTime3 .....
1 Pressure1 Pressure2 Pressure3 ......
1 Temperature1 Temperature2 Tempearture3 .....
2 :
:
how would I do this?
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies View Related
I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.
The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data.
I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.
What SQL code or script can i use to populate the 5 tables in ServerB
Below are the names of the 5 tables:
TABLE1: [ServerB].[ProdDB].dbo.[Orders]
TABLE2: [ServerB].[ProdDB].dbo.[Sales]
TABLE3: [ServerB].[ProdDB].dbo.[Employee]
TABLE4: [ServerB].[ProdDB].dbo.[Customer]
TABLE5: [ServerB].[ProdDB].dbo.[Region]
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
i have two tables
Table 1 - AccountRent
Fields are
RentAmount RentYear
100 2004
200 2004
300 2004
400 2005
500 2006
Table 2 - Insurance
Fields are
InsuranceAmount ReceivedYear
100 2002
200 2002
300 2003
400 2003
500 2005
600 2005
I Need to sum these two tables 'Amount' based on the Year The Result for
IncomeAmount Year
300 2002
700 2003
600 2004
1500 2005
500 2006
Regards,
Prabu R