Transact SQL :: Script Update For Each Record

May 21, 2015

I have a csv file that contains numbers like

OtherName, AddNO
name1,NewV001
name2,NewV002
name3,NewV003

and there is sql table which contains below values
ID, AddN0
1,  OldV001
2,  OldV002
3,  OldV003

I need a sql query that reads New AddNo v001..V003 and update in Sql table for given range ID 1-3
OUtput
ID, AddN0
1,  NewV001
2,  NewV002
3,  NewV003

View 2 Replies


ADVERTISEMENT

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Update A Record Based Of A Record In The Same Table

Aug 16, 2006

I am trying to update a record in a table based off of criteria of another record in the table.

So suppose I have 2 records

ID owner type

1 5678 past due

2 5678 late

So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?

View 5 Replies View Related

Lookup && Update Record && Insert Record

Mar 26, 2008

Hi All,

I am trying to create package something like that..

1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table

I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)

I will be appreciated if you can help me...

View 3 Replies View Related

Trigger To Update One Record On Update Of All The Tables Of Database

Jan 3, 2005

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

View 10 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

Transact SQL :: Getting Last Record In Aggregation

Jul 17, 2015

I have daily data that I wish to summarise into monthly. The field [Close] will return the last day of the month that has a value.The query below summarises the data as I want it but using the max of [Close] in the month instead rather than the Last value for the month. In Access I used to use Last function that worked perfectly. From googling, Last doesn't seem to exist in T-SQL but there must be a way to do it.

    D.YearMonth_Number,max(AT.[Close])
FROM
      tbl_Date
ASD
INNERJOINtbl_ASXStats
ASAT
OND.YearMonthDay_Number
=AT.YearMonthDay_Number

[code]...

View 5 Replies View Related

Transact SQL :: Get One Record By ID Which Has Max Date

May 4, 2015

I have records like this

id      address1          date
1      xyz                 01/01/2013
1       abd                01/01/2014
2       dfg                 01/03/2015

Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.

So here result should be

1       abd                01/01/2014
2       dfg                 01/03/2015

View 12 Replies View Related

Transact SQL :: Query To Select Particular Record?

Sep 21, 2015

I have created a sample Table with inserting below data,

ID    Subject    CreatedDate
1      Test         2015-09-20 22:59:07.373
2      Test         2015-09-21 09:16:58.290
3      Test         2015-09-21 09:18:17.500

I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query  for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate? 

View 14 Replies View Related

Transact SQL :: Duplicate Record Search

Jul 23, 2015

I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.

Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.

So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.

View 15 Replies View Related

Transact SQL :: Previous Log Date For Each Record

Sep 22, 2015

I have one table :

file _target

which has below records..

file_target_ID is identity column which will repeat per files_ID

Now, i just shown Target log  for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date  column

Like this ..

Target Date                     New Column
2015-09-09 00:00:00.000   2015-09-16 00:00:00.000
2015-09-16 00:00:00.000   2015-09-25 00:00:00.000
2015-09-25 00:00:00.000   New Target date after 25-9-2015

how to do this in SQL Server ??

using Cross Apply ?? Row_Number() ??

View 3 Replies View Related

Transact SQL :: How To Find A Non Matched Record In 2 Tables

Jul 15, 2015

I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this

Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.

And this query produces wrong results, its giving me match and also non match records.

how to write a query for my non match condition?

View 5 Replies View Related

Transact SQL :: Select Between Days Record Of Every Month

Jun 29, 2015

I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?

View 8 Replies View Related

Transact SQL :: Return One Of Two Existing Values For A Record With Same ID

Apr 28, 2015

I have been researching BOL and other online resources but cannot seem to get a definitive answer.

Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest

Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest

Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,

[Code] ...

but cannot seem to be able to return one record with its corresponding value criteria.

View 21 Replies View Related

Transact SQL :: Remove Duplicate Records But Keep At Least 1 Record

Dec 3, 2010

I have a table that "Geography" that  has the following columns: city, state, zip

There are tons of duplicate cities in this table.  I ran this query and it shows me the number of occurrences of each city.  I want to delete all the duplicates except for 1.  I don't want to do this manually as there are a lot of records.

What would the SQL look like to delete the duplicate records but keep at least one?

View 9 Replies View Related

Transact SQL :: Select First And Last Record For Certain Date Range

Jun 16, 2015

I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:

DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,

[Code] ....

AGENTNAME          BALANCE BEFORE  BALANCE AFTER          DATE
DOUGLAS              9738.75                9782.75                     2015-05-11
DOUGLAS              9782.75                9804.75                     2015-05-12
DOUGLAS              9804.75                9837.75                     2015-05-13

In the sample data above, ideally I would like my query to return data as follow:

AGENTNAME          BALANCE BEFORE                  BALANCE AFTER
DOUGLAS              9738.75 (from first activity)    9837.75 (from last activity)

Not sure how I can write sql query to accomplish this.

View 7 Replies View Related

Transact SQL :: How To Search Duplicate Name Record In Table

Jun 2, 2015

I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.

Student_name   
_____________
Rocky
Albert
Rocky
Williams
Albert
Robert

The query will show

Student_name   
______________
Rocky
Rocky
Albert
Albert

View 4 Replies View Related

Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

Nov 17, 2015

I need to assign a random number between 0 and 1 to all eligible cases of cancer. 

I have a file of records like:
patientid
tumid
site

How can I assign a random number to each record?

View 10 Replies View Related

Transact SQL :: Query Is Trying To Insert Duplicate Record?

May 18, 2015

My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.

-- Inserting new preferred supplier into R5CATALOGUE

DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)

[code]....

View 5 Replies View Related

Transact SQL :: Pull All Records From One Table And Just A Single Record From Another

Aug 7, 2015

I'm trying to pull all records from one table and just a single record from another.  I have this join, (see below).  It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return.  The end result should be the blog record and a single corresponding image record.  But always a blog record.

SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],

[code]...

View 6 Replies View Related

Transact SQL :: Query To Insert Over 1000,0000 Record

May 8, 2015

How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is

  DECLARE @d DateTIme = GETDATE()
  DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
    SET @c = @c + 1
END

View 7 Replies View Related

Transact SQL :: Add Position Of Record Based On Certain Date Of Transaction

Aug 4, 2015

I built the following query to add the position of a record based on a certain date of the transaction of a customer:

select
CustomerID
, Product
, purchasedate

[code]....

View 6 Replies View Related

Transact SQL :: Insert Missing Record Based On A Condition

Sep 29, 2015

How do I get the below scenario:

EmpID DepID
12 2
17 3
17 2
13 3

Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.

Desired Output:

EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2

View 5 Replies View Related

Transact SQL :: Send Changed Database Record To Computer

Apr 21, 2015

I have an online SQL Server database, and my web site registered clients can change the records of this database. My need: if a record of this database is changed, whole the changed record send to me and to the person who changed it immediately. I thought it could be possible through Transact-SQL,

View 2 Replies View Related

Transact SQL :: Last Record Not In Past 9 Months From Todays Date?

Aug 20, 2015

I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.

MC refers to Maintenance.

MCLog data:

carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15

[Code] .....

Here is my Table:

CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));

[code]....

View 6 Replies View Related

Transact SQL :: How To Do Procedural Updates Based On Record String

Aug 16, 2015

How to do some procedural updates based on a record string. What I wish to do.

If the value (of type string) in my column contains the ‘/’ character I wish to ‘do something’
If the value (of my string) in my column contains the ‘-‘ character I wish to ‘do something else’.

Assume my column contains both ‘/’ and ‘-‘ in the same table.column, but not within the same record.

The data in question is in

tbl_Quotes.tLastDateValue
 
 I’ve found I can use something like this to search my string.
 
SELECT
Count(CHARINDEX('/',tbl_Quotes.tLastDateValue))
FROM tbl_Quotes
WHERE
CHARINDEX('/',tbl_Quotes.tLastDateValue)>0
 
And similar for ‘-‘ records.
 
I’m thinking something like this could be used as the test.
 
How do I put this into play, I’m thinking of an IF or CASE but not sure how to best test and which program flow to use in SQL.

View 6 Replies View Related

Transact SQL :: Insertion Procedure To Insert A Record In More Than One Table

Nov 17, 2015

Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .

View 8 Replies View Related

Transact SQL :: How To Select A Single Record In A Left Join

Sep 16, 2015

My tables look like this:

 Users //
table
 UserID // pk
 UserName // varchar
UserFamilyName // varchar
User_Friends //
table
 FriendsID // pk
 UserID // fk
 FamilyName // varchar

MY query:

 SELECT
U.UserFamilyName, F.FamilyName
 FROM  
Users U LEFT
JOIN User_Friends
F ON U.UserID = 
F.UserID
 WHERE     
U.UserName = ‘JOHN’

How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.

View 10 Replies View Related

Update Record

Jun 6, 2007

I am looking for some guidence on the following.
when i click the save button, The record should be updated to the table. I have produced the code below which does'nt seem to work. Please guide me
It works fine when i hard code the value for "textbox value" in line    SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = "Textbox value"
 Protected Sub SaveRecord(ByVal sender As Object, ByVal e As System.EventArgs)       If Page.IsValid Then                                    SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = Textbox value            SqlDataSource1.Update() end ifend sub
<form id="form1" runat="server">    <div>        &nbsp;</div>        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BmsConnectionString %>"                    SelectCommand="SELECT [PrmGrp], [PrmCde], [PrmVal], [PrmValArb], [GrpDsc] FROM [PrmDef] WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))"        UpdateCommand="UPDATE PrmDef SET  PrmVal=@PrmVal, PrmValArb=@PrmValArb, GrpDsc=@GrpDsc,RecSts=1 WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))">                    <SelectParameters>                <asp:QueryStringParameter   Name="PrmGrp" QueryStringField="PrmGrp" Type="String" />                <asp:QueryStringParameter   Name="PrmCde" QueryStringField="PrmCde" Type="String" />                                            </SelectParameters>                        <Updateparameters>                   <asp:parameter Name="PrmGrp"   />                <asp:parameter Name="PrmCde"  />                                <asp:parameter Name="PrmVal"  />                <asp:parameter Name="PrmValArb"   />                <asp:parameter Name="GrpDsc" />                   <asp:parameter Name="RecSts" />                </Updateparameters>                    </asp:SqlDataSource>        <asp:FormView ID="FormView1" runat="server" DataKeyNames="PrmGrp,PrmCde"            DataSourceID="SqlDataSource1" DefaultMode="Edit" Width="670px" style="left: 12px; position: relative; top: 12px; z-index: 100;" Height="359px" BorderStyle="Double" BorderWidth="5px" CaptionAlign="Top" GridLines="Both" BorderColor="Maroon" CellPadding="2">            <EditItemTemplate>                <br />                Parameter Group:                <asp:Label ID="PrmGrp" runat="server" Text='<%# Eval("PrmGrp") %>' style="left: 34px; position: relative; top: 0px" Width="125px"></asp:Label><br />                <br />                Parameter Code:                <asp:Label ID="PrmCde" runat="server" Text='<%# Eval("PrmCde") %>' style="left: 40px; position: relative; top: 0px" Width="125px"></asp:Label>                <br />                <br />                English description: &nbsp;                <asp:TextBox ID="PrmValTextBox" runat="server" Text='<%# Bind("PrmVal") %>' style="left: 17px; position: relative; top: 0px" Width="318px"></asp:TextBox>&nbsp;                                <br />                Arabic description:                <asp:TextBox ID="PrmValArbTextBox" runat="server" Text='<%# Bind("PrmValArb") %>' style="left: 25px; position: relative; top: 0px" Width="317px"></asp:TextBox><br />                <br />                Group description:                <asp:TextBox ID="GrpDscTextBox" runat="server" Text='<%# Bind("GrpDsc") %>' style="left: 29px; position: relative; top: 4px" Width="316px"></asp:TextBox><br />                <br />                &nbsp;                <asp:Button ID="CmdSave" runat="server" OnClick="SaveRecord" Style="left: 134px; position: relative; top: 49px"                    Text="Save" Width="72px" />&nbsp;                <asp:Button ID="CmdDelete" runat="server" OnClick="DeleteRecord"  Style="left: 145px; position: relative; top: 50px"                    Text="Delete" Width="79px"  />                <asp:Button ID="CmdClose" runat="server" OnClick="CloseWindow" Style="left: 160px; position: relative; top: 48px"                    Text="Close" Width="73px" /><br />                <br />                <asp:Label ID="ErrLabel" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#C00000"                    Style="left: 148px; position: relative; top: -38px" Text="Fields marked as (*) are required"                    Visible="False" Width="318px"></asp:Label><br />            </EditItemTemplate>                             <ItemTemplate>                PrmGrp:                <asp:Label ID="PrmGrpLabel" runat="server" Text='<%# Eval("PrmGrp") %>'></asp:Label><br />                PrmCde:                <asp:Label ID="PrmCdeLabel" runat="server" Text='<%# Eval("PrmCde") %>'></asp:Label><br />                PrmVal:                <asp:Label ID="PrmValLabel" runat="server" Text='<%# Bind("PrmVal") %>'></asp:Label><br />                PrmValArb:                <asp:Label ID="PrmValArbLabel" runat="server" Text='<%# Bind("PrmValArb") %>'></asp:Label><br />                GrpDsc:                <asp:Label ID="GrpDscLabel" runat="server" Text='<%# Bind("GrpDsc") %>'></asp:Label><br />            </ItemTemplate>            <HeaderStyle BorderStyle="Double" BackColor="#FFC0C0" BorderWidth="5px" BorderColor="Black" HorizontalAlign="Center" VerticalAlign="Middle" />            <HeaderTemplate>                Business parameter(Edit)            </HeaderTemplate>            <RowStyle BorderWidth="5px" />        </asp:FormView>        &nbsp;&nbsp;&nbsp;&nbsp;    </form>

View 4 Replies View Related

Not Able To Update A Record!

Jul 18, 2007

Hi everybody,I am using SQL Server 2005. I have a table which currently has only 1 record. I am unable to update any field for this particular record and SQL server is timing out and giving an error message saying No row was updated. I created another record in the table and tried to update the fields in the new record without any problem. I am unable to update any field only for the 1 record in the table using my application, query window sql statement as well as directly changing the in the database.Can anybody please help me.thanks in advance,Murthy here 

View 3 Replies View Related

Update With Top 1 Record

May 15, 2008

I am in a situation in which I would like to update my one table three column with other table three column, The other table might have more then one record but I would like to have the TOP 1 record of that table for these column. How can I achive it. I know I will be able to achive by writing three statment like
Update abc
set a=(select top 1 a from xyz order by ),
b=( select top 1 b from xyz) and so but not sure that a and b using the same record and thats the requirment of update is
any help much apprecited

View 1 Replies View Related

Update Only One Record

Jul 23, 2005

Hello,update table set column = x where b is nullI have the above update statement in a transact sql file. I would liketo change it so that it will only update 1 of the records in the table,even if there are many records where b is null....Any ideas would be great.Many thanks,Allan

View 1 Replies View Related

How To Update Just One Record

Jul 23, 2005

HiI've a table with 2 columns, one for a client code and one for adate/time and could be more than one record with the same client codeand date/time. the 3rd column is another date/time, NULL by default.I need to check if exists records for a determinated client code anddate/time and place the current date/time in the 3rd column for just oneand only one record.Is this possible ? How ?Thanks in advanceJ

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved