Update Tables From A Csv File?

Jul 20, 2005

Hello and thank you for your time. I have never worked with sql
server and currently have a project updating the database.

I have a CSV file as follows:
id
keywords (this field contains commas within)
date

I have a sql server table as follows with only the id populated.
id
keywords
date

How do I update the sql server fields from the CSV file? I'm assuming
I would use an UPDATE statement. What SQL server utility would I use
(I'm very new at this, please be basic)?

Thank you once again,
Sunshine

View 3 Replies


ADVERTISEMENT

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

Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables

Dec 7, 2006

We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process

View 3 Replies View Related

Saving Tables That Are Generated By Queries As HTML File Or Sub-tables

Oct 17, 2006

I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.

The table has columns like Commodity, Unit, Quantity, Value, Month, Country

A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"

The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.

It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.

I wanted to know if there is an alternate way to pull the data from server ?

I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??

OR

Can I write a script that creates a html files for each table for all input combinations save them ?

OR

Is there exists any other solution ?

View 1 Replies View Related

Meta Data Synchronization With Excel File Source Once We Update The File

Dec 28, 2007



hi all;

1. Excel file Source--> monthly Revenue details
2. Derived Colum Transoformations
3. Oledb Destination

its my flow in one of my packates (ETL job)
Excel file contains monthly revenue details, i wanna import the excel data to my database staging table, so i've created the package.
its working fine...

Problem
if we change the new data for the next month and running the package its not running;
the same file, same format, only we delete the contents, of the file except first row of the excel sheet,
and pasting the new data;
new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us)

i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized
Do you want to Fix this issue automatically with the available external column's meta data

Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to.

now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the package Execution wizard,

ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the asp.net webpage, that is executing the package in an On_click event)

The package is not running from the server, its due to the meta data change in the Excel file( i guess)

please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta data should not change when we have new updates in it;

otherwise suggest me some solutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kind of Data Profiling activity;

i know its some what crazy, but i need to maintain the system with permanent solution, instead of facing this meta data mismatch issue!!!

some what lenthy explanation--> its needed for my dear powerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries, i'll try my level best.

View 3 Replies View Related

Generate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?

May 16, 2008

Hey,



I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?



I have table1 contain account numbers and output-filename for each account,

I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.



Here is my code using bcp (bulk copy)

DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)

SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath

WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END

View 7 Replies View Related

Update Two Tables

Jul 25, 2006

This is my curren code for updaing ESN number. But this is incorrect.
<asp:SqlDataSource ID="ESNTrackingDataSource" UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId" OnInit="ESNTrackingDataSource_Init" OnUpdating="ESNTrackingDataSource_Updating" OnUpdated="ESNTrackingDataSource_Updated" runat="server">  <UpdateParameters>   <asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>  </UpdateParameters></asp:SqlDataSource>
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumberFROM         dbo.ESNTrackingWHERE     (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))

View 1 Replies View Related

Update 2 Tables At Once

Jul 18, 2007

Hi,

I have 2 database tables with column "city". When I update one of these two I want to be updated all the records in the other table. For example:
In table "a", column "city" contains the record "NY"; in table "b", column "city" also contains the record "NY" several times. When I change "NY" to "New York" in table "a" I want that also in table to be changed all the records. The condition is not to use foreign key, but just the text of the records.
Any help would be appreciated,thanks.

View 16 Replies View Related

Update SQL Tables

Jan 16, 2004

I have an package that goes out and downloads information from an Informix Database. This Information needs to be replicated in another table. However I cant just delete and insert the information into the table, There are a number of clients updating from this table once a minute. How can I make SQL Serve 2000 update the information based on another sql table ( Sort of like a local replication?). Is there a simply query I can use or do I need to set up another packeage stored procedure. Right now the information is placed in a tempory table, the actual table needing to be updated is cleared of its data, and the information is moved. However this causes latency issues in my program. Also, since the Infromix query can take up to 30 seconds to complete, this leaves some of my users retrieving blank data.

View 2 Replies View Related

Update 2 Tables In One Sp

Oct 5, 2005

I want to update 2 tables in one sp as the parameters are the same. Here's my sp and I don't get any syntax errors in Ent manager, but the error 'Incorrect syntax near 'spRB_CancelFacMenuBooking' when I run it.

CREATE PROCEDURE spRB_CancelFacMenuBooking

@strBookingNo integer,
@strCancelled bit,
@strCancelledDate datetime,
@strCancelledBy nvarchar(100)


AS
BEGIN
UPDATE tblRB_FacilitiesBookings SET
FB_Cancelled =@strCancelled,
FB_CancelledDate=@strCancelledDate,
FB_CancelledBy=@strCancelledBy
WHERE FB_BookingNo = @strBookingNo
END

BEGIN
UPDATE tblRB_MenuBookings SET
MB_Cancelled =@strCancelled,
MB_CancelledDate=@strCancelledDate,
MB_CancelledBy=@strCancelledBy
WHERE MB_BookingNo = @strBookingNo
END
GO

View 9 Replies View Related

Update With Two Tables

Sep 2, 2007

hi
i have two table and would like to update a one of them from the other.
i have to fill the column "num" of the CLIENT table , from the TAB1 table. with the condition CLIENT.id = TAB1.id
UPDATE [client] INNER JOIN nom_tab1 ON [client].id = tab1.id SET [client].num = [tab].[num];
but it didn't worked for me ,can u give me a hand ?

View 12 Replies View Related

How To Update Tables Using ADP

Aug 9, 2006

Hi,
I have a problem with adding new rows to a table in access ADP file.
Most of my tables i have created using SQL Server Management Studio and i cant edit those with ADP
When i create table using ADP project file I can easly edit add delete rows the way i want.The problem is that most of them are already created using SQL Server Management Studio so i cant change them in ADP. i was thinking that the properties are different when its created by adp project but they loook the same.
Dont anybody knows what to do to be able to edit tables in adp.

View 1 Replies View Related

How Can I Update Relationship Tables?

May 14, 2007

<----------I
have 2 tables are: 'customers (parent)' and 'open_ac (child)'

<--------I
have tried to insert and update data into sql database by using textboxes
(don't use datagrid)

<--------My
tables details are below
 

<-------this
table uses for keeping user data 

customers
fields:  

Column
name          
        type  
        length     
                 
             Description

cu_id  
               
             int  
             4  
         Primary key     
      Identifiers

cu_fname
                
    nvarchar   
       20        
  allow null          
     first name       
                 
     

cu_lname
                 
   nvarchar         
40            allow null 
              last name

cu_nat
                
        nvarchar      
  
20            allow
null               
nationality

cu_add  
               
      nvarchar      
  
40            allow null             
  address

cu_wplace
            
      nvarchar      
  
40            allow
null          
     workplace

cu_tel
             
           nvarchar   
      10
           allow
null               
telephone

cu_fax
             
          nvarchar   
      10
           allow null          
         fax

cu_email
             
       nvarchar        
10            allow
null             
     email

 <----the
open_ac uses for keeping register date/time of customers

open_ac
fields:  

Column
name           type  
        length           
Description

cu_id  
               
    int           
     4           
      link key

op_date  
            date/time          
8                
register date

 

<----------my
code 

Imports
System.Data.SqlClient

Public Class cus_reg
    Inherits System.Web.UI.Page
    Dim DS As DataSet
    Dim iRec As Integer   'Current Record
    Dim m_Error As String = ""

    Public Property MyError() As String
        Get
            Return
m_Error
        End Get
        Set(ByVal Value As String)
            m_Error =
Value
            If
Trim(Value) = "" Then
               
Label3.Visible = False
            Else
               
Label3.Text = Value
               
Label3.Visible = True
            End If
        End Set
    End Property

    Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            Dim C1 As
New MISSQL
            'DS =
C1.GetDataset("select * from customers;select * from open_ac;select * from
accounts")
            DS =
C1.GetDataset("select * from customers;select * from open_ac")
           

   
        Session("data") = DS
            iRec = 0
           
Viewstate("iRec") = iRec
           
Me.MyDataBind()

            Dim Dtr As
DataRow = DS.Tables(0).NewRow
           
DS.Tables(0).Rows.Add(Dtr)
            iRec =
DS.Tables(0).Rows.Count - 1
           
viewstate("iRec") = iRec
           
Me.Label2.Text = DateTime.Now
           
Me.MyDataBind()
        Else
            DS =
Session("data")
            iRec =
ViewState("iRec")
        End If
        Me.MyError = ""
    End Sub


    Public Function BindField(ByVal FieldName As String) As
String
        Dim DT As DataTable = DS.Tables(0)
        Return DT.Rows(iRec)(FieldName)
& ""
    End Function
    Public Sub MyDataBind()
        Label1.Text = "Record : "
& iRec + 1 & " of " & DS.Tables(0).Rows.Count
        txtcu_id.DataBind()
        txtcu_fname.DataBind()
        txtcu_lname.DataBind()
        txtcu_add.DataBind()
        txtcu_occ.DataBind()
        txtcu_wplace.DataBind()
        txtcu_nat.DataBind()
        txtcu_tel.DataBind()
        txtcu_fax.DataBind()
        txtcu_email.DataBind()  
    End Sub
   

Here is
update code


    Private Sub bUpdate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles bUpdate.Click
        Dim DT As DataTable = DS.Tables(0)
        Dim DR As DataRow = DT.Rows(iRec)
        'Dim DR1 As DataRow = DT1.Rows(iRec)

        If DR.RowState = DataRowState.Added
Then
            If
txtcu_id.Text.Trim = "" Then
               
Me.MyError = "please enter your  id"
               
Exit Sub
            End If
            DR("cu_id")
= txtcu_id.Text
        End If

        If txtcu_fname.Text.Trim =
"" Then
            Me.MyError =
"please enter your name"
            Exit Sub
        Else
           
DR("cu_fname") = txtcu_fname.Text
        End If

        If txtcu_lname.Text.Trim =
"" Then
            Me.MyError =
"please enter your last name"
            Exit Sub
        Else
           
DR("cu_lname") = txtcu_lname.Text
        End If

        If txtcu_add.Text.Trim =
"" Then
            Me.MyError =
"please enter your address"
            Exit Sub
        Else
           
DR("cu_add") = txtcu_add.Text
        End If

        If txtcu_occ.Text.Trim =
"" Then
            Me.MyError =
"please enter your occupation"
            Exit Sub
        Else
           
DR("cu_occ") = txtcu_occ.Text
        End If

        If txtcu_wplace.Text.Trim =
"" Then
            Me.MyError =
"please enter your workplace"
            Exit Sub
        Else
           
DR("cu_wplace") = txtcu_wplace.Text
        End If

        If txtcu_nat.Text.Trim =
"" Then
            Me.MyError =
"Please enter your nationality"
            Exit Sub
        Else
           
DR("cu_nat") = txtcu_nat.Text
        End If

        If txtcu_tel.Text.Trim =
"" Then
           
DR("cu_tel") = DBNull.Value
        Else
           
DR("cu_tel") = txtcu_tel.Text
        End If

        If txtcu_tel.Text.Trim =
"" Then
           
DR("cu_fax") = DBNull.Value
        Else
           
DR("cu_fax") = txtcu_fax.Text
        End If

        If txtcu_email.Text.Trim =
"" Then
           
DR("cu_email") = DBNull.Value
        Else
           
DR("cu_email") = txtcu_email.Text
        End If
        
        Dim Strsql As String
        If DR.RowState = DataRowState.Added
Then
            Strsql =
"insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)
values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)"
        Else
            Strsql =
"update customers set
cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10
where cu_id =@P1"
        End If
        Dim C1 As New MISSQL
        Dim cmd As SqlCommand =
C1.CreateCommand(Strsql)
        C1.CreateParam(cmd,
"ITTTTTTTTT")
       
cmd.Parameters("@P1").Value = DR("cu_id")
        cmd.Parameters("@P2").Value
= DR("cu_fname")
       
cmd.Parameters("@P3").Value = DR("cu_lname")
       
cmd.Parameters("@P4").Value = DR("cu_add")
       
cmd.Parameters("@P5").Value = DR("cu_occ")
       
cmd.Parameters("@P6").Value = DR("cu_wplace")
        cmd.Parameters("@P7").Value
= DR("cu_nat")
       
cmd.Parameters("@P8").Value = DR("cu_tel")
       
cmd.Parameters("@P9").Value = DR("cu_fax")
       
cmd.Parameters("@P10").Value = DR("cu_email")
      

        Dim Y As Integer = C1.Execute(cmd)
        If Y > 0 Then
           
DR.AcceptChanges()
        Else
            Me.MyError =
"Can not register"
        End If
<---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update 
        Dim DT1 As DataTable = DS.Tables(1)
        Dim DR1 As DataRow = DT1.Rows(iRec)
        If DR1.RowState = DataRowState.Added
Then
            If
txtcu_id.Text.Trim = "" Then
               
Me.MyError = "Please enter id"
               
Exit Sub
            End If
            DR1("cu_id")
= txtcu_id.Text
        End If
        If Label2.Text.Trim = ""
Then
           
DR1("op_date") = Label2.Text
        End If

        Dim StrSql1 As String
        If DR1.RowState =
DataRowState.Deleted Then
            StrSql1 =
"insert into open_ac (cu_id,op_date) values (@P13,@P14)"
        Else
            StrSql1 =
"update open_ac set op_date=@P14 where cu_id=@P13"
        End If
        Dim C2 As New MISSQL
        Dim cmd2 As SqlCommand =
C2.CreateCommand(StrSql1)
        C2.CreateParam(cmd2, "ID")
       
cmd2.Parameters("@P1").Value = DR1("cu_id")
       
cmd2.Parameters("@P2").Value = DR1("op_date")

        Dim Y1 As Integer = C2.Execute(cmd2)
        If Y1 > 0 Then
           
DR1.AcceptChanges()
        Else
            Me.MyError =
"Can not register"
        End If
    End Sub
End Class 

<------this
is class  I  use for connecting to database and call parameters....

MISSQL
class

Imports
System.Data.SqlClient
Public Class MISSQL
    Dim PV As String =
"Server=web_proj;uid=sa;pwd=sqldb;"
    Dim m_Database As String = "c1_itc"
    Public Strcon As String
    Public Sub New()
        Strcon = PV &
"database=" & m_Database
    End Sub
    Public Sub New(ByVal DBName As String)
        m_Database = DBName
        Strcon = PV &
"database=" & m_Database
    End Sub
    Public Property Database() As String
        Get
            Return
m_Database
        End Get
        Set(ByVal Value As String)
            m_Database =
Value
            Strcon = PV
& "database=" & m_Database
        End Set
    End Property

    Public Function GetDataset(ByVal Strsql As String, _
        Optional ByVal DatasetName As String
= "Dataset1", _
        Optional ByVal TableName As String =
"Table") As DataSet
        Dim DA As New SqlDataAdapter(Strsql,
Strcon)
        Dim DS As New DataSet(DatasetName)
        Try
            DA.Fill(DS,
TableName)
        Catch x1 As Exception
           
Err.Raise(60002, , x1.Message)
        End Try
        Return DS
    End Function

    Public Function GetDataTable(ByVal Strsql As String, _
         Optional ByVal TableName As
String = "Table") As DataTable
        Dim DA As New SqlDataAdapter(Strsql,
Strcon)
        Dim DT As New DataTable(TableName)
        Try
            DA.Fill(DT)
        Catch x1 As Exception
           
Err.Raise(60002, , x1.Message)
        End Try
        Return DT
    End Function

    Public Function CreateCommand(ByVal Strsql As String) As
SqlCommand
        Dim cmd As New SqlCommand(Strsql)
        Return cmd
    End Function

    Public Function Execute(ByVal Strsql As String) As
Integer
        Dim cmd As New SqlCommand(Strsql)
        Dim X As Integer = Me.Execute(cmd)
        Return X
    End Function

    Public Function Execute(ByRef Cmd As SqlCommand) As
Integer
        Dim Cn As New SqlConnection(Strcon)
        Cmd.Connection = Cn
        Dim X As Integer
        Try
            Cn.Open()
            X =
Cmd.ExecuteNonQuery()
        Catch
            X = -1
        Finally
            Cn.Close()
        End Try
        Return X
    End Function

    Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal
StrType As String)
        'T:Text, M:Memo, Y:Currency,
D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
        Dim i As Integer
        Dim j As String
        For i = 1 To Len(StrType)
            j =
UCase(Mid(StrType, i, 1))
            Dim P1 As
New SqlParameter
           
P1.ParameterName = "@P" & i
            Select Case
j
               
Case "T"
                   
P1.SqlDbType = SqlDbType.NVarChar
               
Case "M"
                   
P1.SqlDbType = SqlDbType.Text
               
Case "Y"
                   
P1.SqlDbType = SqlDbType.Money
               
Case "D"
                   
P1.SqlDbType = SqlDbType.DateTime
               
Case "I"
                   
P1.SqlDbType = SqlDbType.Int
               
Case "S"
                   
P1.SqlDbType = SqlDbType.Decimal
               
Case "B"
                   
P1.SqlDbType = SqlDbType.Bit
               
Case "P"
                   
P1.SqlDbType = SqlDbType.Image
            End Select
           
Cmd.Parameters.Add(P1)
        Next
    End Sub
End Class
               
           
       

<-------Thank you in advance<-------and Thank you very much for all help

   
 

 

View 2 Replies View Related

Update And Select From Two Tables.

Mar 10, 2008

Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help.
Thanks

View 1 Replies View Related

Insert / Update 2 Tables

May 12, 2008

Hi,
 
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details.
Is it possible for me to update both pages in 1 web update page?????
 
Thanks in advance.

View 3 Replies View Related

Using Two Or Three Tables In An Update Command

Jun 15, 2004

i want to update a table where a linked table is a certian value
for example

i have a table that queues up calls and has whether they have been contacted or not as a boolean value.

but if the call is closed in another way i would like to update that table where the linked location table has a certian value

but in the query analizer it will only let you use one table to update is there another way to do this?

View 2 Replies View Related

Update Two Tables With The Same Statement

Sep 16, 2005

hi,does any know how to update two tables with the same statement?is it possible?here is my unsuccessful try:UPDATE A,BSET A.VAL='BLUH1', B.VAL='BLUH2'from #T A, #T2 BWHERE  A.LOANID=B.LOANID

View 3 Replies View Related

T-sql To Update 2 Tables With 1 Query?

Nov 30, 2005

is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.

View 4 Replies View Related

Update Of Records From Other Tables

Sep 11, 2000

I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.

thanks
Jason

View 1 Replies View Related

Update Time Of Tables

Nov 1, 2004

Hi

After running some queries, I want to know which tables have been updated in the database in sql server.

Is there a way to find out the last updated time of all the tables in the database?

Thanks

Madhukar Gole

View 1 Replies View Related

UPDATE 2 Tables In One Statment

Sep 26, 2007

Hi guys,

Does anyone who how I update two tables with one UPDATE statment (i.e. joining them with an INNER JOIN). I'm using SQL Server.

These are going to be very large tables, so I'd rather not have to run an UPDATE twice as it's going to be s l o w...

Thank all

Dâi

View 1 Replies View Related

Update Field In Two Tables

Jul 25, 2006

This is my curren code for updaing ESN number. But this is incorrect.

<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>

this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.

This is my query that returns the ones are not assigned to and
Asset

SELECT DISTINCT EsnId, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))

View 1 Replies View Related

Update Statistics On All Tables

Dec 8, 2006

I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.

Below is the script that I executed to defrag all the tables in my database if anyone needs this.



/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 20.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

View 4 Replies View Related

Compare Update On Two Tables?

Feb 27, 2014

I have 2 tables. They both have the same fields [OFO_Code] and [Description]

Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.

I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.

Their are 1417 items in Table 2 and 77000 items in table 1.

There are NO errors when executing, just no update

Here is my code so far:

Code:
Update Qualification SET [Description] = B.Description FROM Qualification A INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"

View 7 Replies View Related

Update 2 Tables In One Statement

May 1, 2008

this code is not working.
I can’t update 2 tables in one statement?

update pd, p
set pd.show = 0 , pd.seashow = 0
FROM ProductDetail pd
join Products p on p.itemid = pd.ItemID
where pd.show = 1
and pd.site in (1,4,6)

View 3 Replies View Related

Update Temp Tables

Aug 19, 2013

how to update a temp table with another temp table for example I have #tempdashboard that has info in it.I also have #tempappscount of which I want to insert the info inside it into #tempdashboard...I believe the column in #tempdashboad is DailyAppsInINT,

I did a query that gave me the number of daily apps created. I was told to create #tempappscount and my query to dump into #tempappsaccount. Then dump the info from #tempappsaccount into #tempdashboard.

View 2 Replies View Related

How To JOIN In UPDATE - Across 2 Tables

Oct 15, 2014

I have a customers CRM DB that I need to run an update query on, affecting around 14,000 records. The fields used in the entity in question are split across two tables. I need to update a field in one tabled based off of the result of a field in the other table.

So this is what I started with:

USE db1
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This didn't work, SQL complained about being unable to bind tbl1.CreatedOn. I assume because it's in a different table to the one I'm updating.

I attempted a JOIN to the best of my limited SQL knowledge, thinking I could just shove the two tables together and it might be happy.

USE db1
INNER JOIN tbl1 ON tbl2.Id=tbl1.Id;
UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';

This also didn't work, complaining of syntax error near 'INNER'

I'm obviously missing something, but IU don't know what it is.

View 5 Replies View Related

Update From Multiple Tables

May 7, 2006

Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?

UPDATE Disk
SET price = price *.20
WHERE Interpreter.name = ‘Joe Smith;


MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)

View 3 Replies View Related

Compare Two Tables And Update Or ????

Aug 7, 2006

ans writes "I’m using SQL Server
• I’ve got 3 tables: tblOne, tblTwo, tblThree
• Using the stored procedure(let call this stored procedure sp_Result) I generate result using all the above tables(tblOne, tblTwo, tblThree).
• The problem is, it takes too long to execute(around 15 sec), which is not acceptable as I’m using in the result in the web application
• I’m thinking of scheduling to run the above stored procedure to run once every hour and write the result in a new table, tblResult.
• now the user only queries in the tblResult tables. Since it is a straight forward query it will take less than 1 sec. Perfect !!!
• Now the tblResult already exist, and the user are queries only to this table.
• How would I compare the result produced by stored procedure sp_result and the table tblResult?
• How would I update tblResult with the produced result?
• Would be a good idea to write the result in a new table called temp, then drop the table tblResult and then rename the table temp to tblResult?"

View 6 Replies View Related

How To Insert & Update Two Tables .?

Oct 11, 2006

Hi,
I am using sqlServer 2000.I want to insert as well as update Two Tables at the Same Time using storedprocedure.Both My Tables Have the Same columns only difference being the name.My Tables are,
ADDRESS AND ADDRESSBOOK and their values are Name, Address, Place,
Date,City.
How can achieve this task using stored Procedures.?

Any Help would be appreciated....

Thanks...

View 8 Replies View Related

Sql Commands Don't Update Tables

Mar 28, 2007

Visual Studio 2005
i have insert and update statements. they don't produce any errors but no
changes happen in my tables. are there any possible reason for this. i used
sqlcommand and even stored procedures but still the same.

View 18 Replies View Related

Update From Multiple Tables

Mar 28, 2008

I have a mess:

Table1(T1) contains Column1 (C1) which must be appended to Column2 (C2) in Table2 (T2) where an identifying code column(ID) matches in each table. An example of what I want is, where T1.C1 = 'Aluminum' and T2.C2 = 'Material', I would like it to be updated to T2.C2 = 'Material: Aluminum'. I tried this:

UPDATE T2
SET T2.C2 =
(SELECT T1.C1
FROM T1
WHERE T1.ID = T2.ID) + ':' + T2.C2

This did not work out. How should I fix this?

View 6 Replies View Related

Update All Tables In Database

Jul 20, 2005

HiMy database contains , lets say, 100 tables. Out of these 100 tables,60 tables contain both columns; Column1 and Column2.I want to:Update (All tables with BOTH of these columns)Set Column1 = Column2Is there a better way of doing it? OR do I have to do it manually foreach table.Thanks for your help.

View 5 Replies View Related







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