Dear All,
Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?
Thanks for your kind attention
Yours faithfully,
Benny
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
Hi,
I have a table with 1000000 records i try to add a field with following`spec.
[mkey] [int] IDENTITY(1,1) NOT NULL
I get the following meassage:-
yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Then I Have the following error:-
Time out error
- How is the best way to copy a large table from one to anther.
regards
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
View 3 Replies View RelatedHow can I alter a table turning ON or OFF an IDENTITY field ?
for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?
+
Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
Thanks in advance!
Hello everyone,
I have a table X that has two columns (names have been changed to protect the guilty and innocent alike):
ID which is an identity column, and
Data which is a varchar.
I am trying to DTS data from a text file into this table. I want the identity column ID to auto-populate and each row of information from the file to be stored in the Data field.
I am creating the DTS package using the wizard in Enterprise Mgr. No matter what I try, I keep getting the following error:
Cannot insert the value NULL into column 'ID', table 'X'; column does not allow nulls. INSERT fails.
Any advice on what I need to do to get this working? It seems like such a simple thing and I'm getting very frustrated. :eek:
Thanks in advance.
Cathy
Hi all,I have 2 tables: Applicant_Table, Applicant_Resume_Table
In my web application, after applicant click "submit" for his job application, the information will be saved to Applicant_table in SQL Server. But in a SQL statement, how to get his Applicant_ID, so that I can save his resume to another table on his Applicant_ID?Thank you.
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.
I tried:
SELECT *
FROM tabMediaTransaction
WHERE (TransactionDate =
(SELECT MAX(TransactionDate) AS Expr1
FROM tabMediaTransaction AS tabMediaTransaction_1))
But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
hi, please help me, i have a data driven site, i made a field that i call "Status"so this is how it should happens, there are three different gridviews in a single page, gridview1 filter is [status] = ok, gridview2 filter is [status] = fine, gridview3 filter is [status] = complete, so all gridview has its own <asp:hyperLinkField> at first the value of "Status" is ok, so it will appear in the gridview1, so when the user click on the <asp:HyperLinkField> of gridview1 it will go to a new page with a form view, this page has a button that when click will change the value of the status field to "fine", so that it will no longer appear to the gridview1 but it will now appear to gridview2 and same with the next <asp:HyperLinkField> click until it appears to the final gridview. so my question is how can i make the button that is capable of changing (update) the value my database field (status) to a specific value, that is not visible to the user thru codebehind or plain asp.net codes. please help me, if you need more info just reply.ThanksSALAMAT PO!
View 1 Replies View RelatedI've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
View 2 Replies View RelatedI am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.
$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))
[code]....
I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
19 08/13/2010Y
1901/10/2013N
1902/03/2014Y
If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS
I get the two latest records since STATUS is different:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1901/10/2013N
1902/03/2014Y
I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?
DECLARE @TEMPMAX
TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)
SELECT CLIENT_NO, max(EFFECTIVE_DATE)
[Code] ....
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1902/03/2014Y
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.
How can I set this field with the IDENTITY properties?
Thanks
I have a table (InfoTable) with a field with XML info like this:
<Status>
<IdStatus>1<IdStatus>
<NomStatus>Status 1<NomStatus>
<IdStatus>2<IdStatus>
<NomStatus>Status 2<NomStatus>
]Code] ...
I want to be able to select then NomProcess where IdProcess=3, for instance.
How can I do it?
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
I'm sure there's a way to do this, but I can't figure it out. I want to see if a field has a semi-colon in it and if it does display one thing, if not display another. But it doesn't just have a semicolon. It's a list of names, and if there is more than one name there will be a semi-colon between them.
I was hoping this would work:
=IIF(Fields!ID.Value LIKE "%;%", "yes", "no")
but it doesn't... I'm looking for something like:
=IIF(Fields!ID.Value CONTAINS ";", "yes", "no")
Any ideas?
I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975 ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656 ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712 ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output
id Startdate
1 2014-10-01
2 2014-01-01
How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline?, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName? ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE GetLatestDate
(@UserName VARCHAR(50)
AS
SELECT Deadline
FROM CustomerItems
WHERE UserName = @UserName
I think you experts must know how to do this.. I just can`t find in any book a good example or related information....
1) I have a time-logged table like this
Product Status Datetime Where
A Received 1/1/90 23:00 Chicago
A Sold 1/7/90 08:30 New York
A Returned 1/8/90 04:30 Paris
B Bought 1/12/91 06:39 Dallas
B Received 1/13/91 04:30 Chicago
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
etc..
All A`s are the same unique product as B and C are too, above is thus a history log of each product item. The records are not updated, just insertions only. (a DELETE on some record in time is likely, but no UPDATES...) I use the history log for several analisys purposes
2) Now, I want by one single SQL query get ONLY latest status of all products,
Like this (No I do not want to keep a separate "status" table each time I insert to the above table)
Product Status Datetime Where
A Returned 1/8/90 04:30 Paris
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
how to do such QUERY. It tried many things without exactly what I need.
I am not a good SQL programmer as U may know now..
the QUERY also should run on MS-ACCESS MDB
3) Replication
The history table is updated by each location and SQL server must keep all table in same condition using replication. Anybody has a suggestion how to setup replication in SQL server for this. It means each insertion into the log is send to each individual site. Can I publish to the same table from each site to each site , so that each change in the log gets reflected in the other site`s history table ?
Thanks a lot !
Hi all,
I want to have a query that will return me a single row for each user
from a table where the table has many rows for each user. The single
returned row for each user must be the most recently dated entry
([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.select DISTINCT([I1-Customer-Ref]) AS Cust,([i7-w-fixed-amnt]) as WaterFixedAmt, ([i7-w-rv-amnt]) as WaterRVAmt, ([i7-s-fixed-amnt]) as SewerageFixedAmt, ([I7-Change-Date]) AS [Date]from r07UnMeasuredBills ORDER BY Cust, [I7-Change-Date] DESC
I am using MS SQL Server 7 for this.
Thanks
Tryst
hi friends,i have an identity column in my table.(e.x : id )and it's identity increment is 1.when i insert a row the id field is 1, and in next record the field is 2.....now , i delete second record(id=2)and now when i insert a record again , the id column is 3.i want to record be 2 instead 3.plz help me.thanks
View 2 Replies View RelatedHi:
I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?
Can you assist?
Thanks
Hello friends,
I had created a web application and uploaded the application. Now the problem is with the DB. I had created the DB on the server (using script). But the fields that have identity field yes is not been set Now how I can set the fields identity field to yes. Fields are already there. Only I want to set there identity field to yes.Let me know how this can be done.
Thanks & RegardsGirish Nehte