sql update two databases on same server

[mdacoron]

Hey guys,

I am in no way any type of database admin but I have this dilemma with a sql server that I am trying to update some table information on.

We are running this imaging system that uses microsoft sql 2005. There are two separate databases on the server and I want to update information on one of the database tables from another database table.

In the object explorer tab when I open the databases folder I have 5 things listed. I only use two of them. One of them is called AX and another called Kfx. I know how to do searches for a specific column with certain data needed.

If I go into one of the databases then go into one of the tables, I can run a sql query for information in that table. Usually what I do to do this is to go into that table by right clicking and selecting open table. After doing that I can go and click on show sql pane and run a query on any column in that table fine. That is the extend to my knowledge.

My question now is how do I run a sql update to update information from one database table to another database's table?

 

[jbenson001]

Using the GUI will be more confusing than writing the SQL yourself. What is the strucure of the tables and give some sample data and desired results? This way, we can help you better.

 

[mdacoron]

ok

The database Kfx has a table called HarvestImport.
The contents of this table are Cust #, Cust Name, Ship To #, Ship To Name, Date, Invoice, Order # and Total.

The other database is called AX and has a table called ae_dt1. The contents of this table are pretty much the same as the HarvestImport table but are missing everything except the Invoice number.

I want to update ae_dt1 table based on the Invoice number. How can I update table ae_dt1 in database AX based on the invoice number that is in database Kfx in table HarvestImport.

 

[jbenson001]

Are both DBs on the same server?

 

[mdacoron]

yes

 

[mdacoron]

The only thing that I can thing of also is that the AX database table columns are called field1, field2, field3 and so on. They are not the same names as the columns in Kfx with actual names to the columns.

 

[jbenson001]

Sorry that was a stupid question considering the title of your post

Before you try the code, make sure you back up the db or move the data to a backup table.

Assuming you are in the AX DB

Update ae_dt1
Set <your col> = <some value>,
    <your col2> = <some value2>  --, ... more cols here
From ae_dt1 a
   Inner Join
      Kfx.<table owner>.HarvestImport h ON
      a.InvoiceNumber = h.InvoiceNumber

 

[mdacoron]

where do I actually type in this statement?

 

[mdacoron]

is there a way to copy the database to another newly created database that I can do the test on?

 

[philhege]

Go into Management Studio, connect to the instance, right click on the database to copy, select Tasks -> Copy Database, and follow the wizard.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SQLSister]

where do I actually type in this statement?

Click the button that says new query. Or select it from the file/new menu.

"NOTHING is more important in a database than integrity." ESquared

 

[mdacoron]

Update ae_dt1
Set <your col> = <some value>,
<your col2> = <some value2> --, ... more cols here
From ae_dt1 a
Inner Join
Kfx.<table owner>.HarvestImport h ON
a.InvoiceNumber = h.InvoiceNumber

on this coding part what do i type in for the
Set <your col1> = <some value>,

what is the value there that I am supposed to put?

all i know is that in
AX DB compared to Kfx DB
field1 = Invoice
field2 = Order #
field3 = Cust #
field4 = Cust Name
field5 = Ship to #
field6 = Ship to Name
field7 = Date
field8 = Total

Is that what I am supposed to use for the Set command?

 

[jbenson001]

a.field1 = h.Invoice,
a.field2 = h.Order# .... etc

 

[mdacoron]

I typed this in and get error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Order'.


Update ae_dt1
Set a.field1 = h.Invoice,
a.field2 = h.Order #,
a.field3 = h.Cust #,
a.field4 = h.Cust Name,
a.field5 = h.Ship To #,
a.field6 = h.Ship To Name,
a.field7 = h.Date,
a.field8 = h.Total
From ae_dt1 a
Inner Join
Kfx.<table owner>.HarvestImport h ON
a.InvoiceNumber = h.InvoiceNumber

 

[jbenson001]

If the column names have spaces in them, you need to surround them with [].

a.field2 = h.[Order #]

 

[mdacoron]

I just ran it and gave me 51,xxx row updated but it didnt update some of the info there. I have some rows that came up blank for all the columns except for invoice #.

I am actually trying to only update rows that show a column in the AX database in the ae_dt1 table with a value of 0.

 

[jbenson001]

This is why I said you have to provide sample data and desired results. You are giving me info after the fact. Please describe in "psuedo code" what you are trying to do.

 

[mdacoron]

here is an example

ae_dt1
invoice cust# custname shipto# shiptoname date total
12345 12 tes1 1 test1 080808 124.45
14567 13 tes2 2 test2 080908 122.11
13448 14 tes3 3 test3 081008 654.34

HarvestImport
invoice cust# custname shipto# shiptoname date total
12345 0.0
14567 0.0
13448 0.0

Here I have the ae_dt1 table full of information. I need to update the HarvestImport table with the information from ae_dt1 based on the fact that the total is zero but use the invoice number to reference both tables.

 

[jbenson001]

You just said you want to update the HarvestImport table, are you sure? In your original post you said you wanted to update the ae_dt1 table.

 

[mdacoron]

yes i need to update the ae_dt1 sorry