In the table, there is a record which has several field. every month, the function will create a same record. that means, the first month, one record. the secord month, two reocrds, ..... for a years. will have same 12 record. so what function can do this? Thanks.
View 2 RepliesHow can I automatically change records by a specific date specified inside the record. An example would be the way ebay sales work. How does ebay have the status of an item change to closed, at the time inside the item record of the database.
I know i could use triggers or something to check the current date against the enddate everytime the record is accessed, but is there a more efficient method?
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
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.
CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO
I want insert automatically records for Range that user definded
For example user define :
from to city
10000 12000 tehran
15000 19000 babol
I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
im running an INSERT statement and I need one of the fields (RecordID) to be automatically generated.
Its a unique identifier type.
INSERT INTO swdata.dbo.Reports_PI
(RecordID , SD1AverageSCResTime
)
VALUES
(,xxxxxxxxxxx
,@SD1AverageCallResolutionTime
,@SD2CountAllCalls
,@SD3PercentClosedIn24Hours
)
Is there a keyword or value I can put in xxxxxxxxx that will automiatically generate a unique identifier as the record is created?
Thanks,
Hello everybody!I'm using the 'Data Sources (ODBC)' program that comes with windows tocreate the odbc connections I need. Although that is quite fast and easy Iwould like to have it more automated, since I'm using the same parametersall the time.Can anybody tell me (or give pointers to) how that is done?regards--Johnny Ljunggren
View 2 Replies View Relatedhi,
i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences).
i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT.
hope this is possible .. or anyone have other suggestions?
thanks a lot!
Any new record inserted or update in source table SSIS package automatically run, how can we achieve without using service broker.
View 7 Replies View RelatedHi,
I need help in looking at sample script to perform automatic creation for weekly partitioned tables and also update the partitioned view accordingly.
Any helps are very much appreciated!
Thanks,
G
I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.
Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.
ddave
I need to deploy the trigger in database whenever new DB is creating on the server.
View 6 Replies View RelatedHow to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to automate this script means wants to create a job which runs on a Friday basis and save the output results of the query as a excel file and then automatically sends the mail to everyone.
View 9 Replies View RelatedIn SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies View Related
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Can I do something like this in RS -
I would like to call a function in a calculated field like
=code.GetNHW(Fields!id1.Value,Fields!date1.Value)
where the GetNHW() function would return a double value based on some conditions-
The code I would like to write in Code window-
Public Function GetNHW(ByVal ID As Integer, ByVal attDate As Date) As Double
Dim dsDTConfig As New DataSet
Dim NHW As Double
dsDTConfig = GetDS("tblEmployee_DailyTimingsConfig", "id=" & ID & " and '" & attDate & "' between configStartDate and configExpiryDate", False)
If dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = True Then
NHW = 0.01
ElseIf dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = False Then
NHW = 0.05
End If
Return NHW
End Function
What I am looking for is - I would want to get the record(dataset) within the function from the database
and based on the values got, I would check few conditions and calculate NHW.
--Anand
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedHello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS
View 5 Replies View RelatedHello all,
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column.
What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located.
Can anybody help me achieve this? My code is:
Code Block
SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK
FROM Sources AS FT_TBL
INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
What am I doing wrong?
Thanks in advance,
Chris
is there a way that when a record is entered into a table, that I can get it to timestamp itself?
Davids Learning
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey
[code]....
Hello,
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
Thank you,
Nathan
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
I have the following data,
CustomerID EngID EngCount
1 A11 2
2 B12 1
3 C10 3
I need to display it as,
CustomerID EngID EngCount
1 A11 1
1 A11 1
2 B12 1
3 C10 1
3 C10 1
3 C10 1
Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.
How to do?
Dear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View 1 Replies View RelatedHello
I'm reading a XML file and the next operation need a column with the row count.
I don't want to know how many rows there is in the recordset, but, the row count of each record.
How can I implement this?
tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal
I need to create records based on date range on monthly basis, please help.
Here is an example:
ID Startdate EndDate
1 20070301 20070522
and I need to create the following data set based on start and end date range
ID Startdate EndDate
1 20070301 20070331
1 20070301 20070430
1 20070301 20070522
I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium
View 4 Replies View RelatedHello!How can I make this function in MS SQL:CREATE FUNCTION id_name() RETURNS INTEGER AS 'SELECT MAX(ID)+1 FROM Test;'Thanks!
View 2 Replies View RelatedI have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.
USE database1
GO
CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output =CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
GO
prt_Mark | dwg_Seq
12 | 12a,23b
25c | 1b,5e,8d,100as
I get the following errors
Msg 325, Level 15, State 1, Line 2
Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@prt_Mark".
Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'To'.