Solved

SQL Syntax

Posted on 2009-04-01
7
201 Views
Last Modified: 2012-05-06
I have a table on 1 database that i want to recover data so that i can update my other database but with the same table

I want to change the date_received  of  all my payment_numbers that match the data_recieved of the recovered table and update another table from a different database I know what my payment_numbers are

The statement i use to select  my data is and I use the same statement for database A and database B  only thing different on each database is the date_received is all the same with 3/16/2009 and i want to change what to what i get on my recovered table


SELECT      payment_number, date_received
FROM         ar_receipts
where receipt_number IN(105
,106
,229
,307
,330
,333
etc...)
 
0
Comment
Question by:Jiggens
  • 3
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 475 total points
ID: 24042563
Are the two databases stored on the same SQL Server?

you could try this (select statement only to see if the data view works)

select a.payment_number,a.date_received,b.payment_number,b.date_recieved from database1..dbo.ar_reciepts a inner join database2..dbo.ar_reciepts b on a.payment_number=b.payment_number

This should give you a query with 4 columns 2 from each database.  

Note you need to change database1 and database2 to the names of the two databases in question.

(IF this works we can write an update script to do something similar)
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24042789
So the syntax would be like so

select a.payment_number,a.date_received,b.payment_number,b.date_recieved from Accounting_EOM..dbo.ar_reciepts a inner join p21play..dbo.ar_reciepts b on a.payment_number=b.payment_number


The databases are on the same server.  Did i mess something up ?

i got an error

Msg 7202, Level 11, State 2, Line 1
Could not find server 'Accounting_EOM' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24042826


select a.payment_number,a.date_received,b.payment_number,b.date_recieved from database1..dbo.ar_reciepts a inner join database2..dbo.ar_reciepts b on a.payment_number=b.payment_number

What oes database2..dbo.ar_receipts B mean  I also have never seen  the .. in database..dbo.ar_receipts

thanks
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 25 total points
ID: 24043034
you do not need the .. (two dots) in your statement.  Replace
Accounting_EOM..dbo.ar_reciepts with Accounting_EOM.dbo.ar_reciepts
and
p21play..dbo.ar_reciepts with p21play.dbo.ar_reciepts
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24043069
Thanks CG! i made a typo
0
 
LVL 1

Author Comment

by:Jiggens
ID: 24043434
So would the syntax be

UPDATE    p21play.dbo.ar_receipts
SET              date_received = a.date_received
FROM         ar_receipts AS a INNER JOIN
                      p21play.dbo.ar_receipts ON a.payment_number = p21play.dbo.ar_receipts.payment_number
WHERE     (p21play.dbo.ar_receipts.last_maintained_by = 'cjiggens')

or is there another step i am missing ?

0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24043458
That looks correct to me.

You may also want to make sure you have a backup of the table before you do the update

use p21play
select * into ar_reciepts_040109 from ar_reciepts

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET return row from SQL to DataSet and get column values ? 3 49
Using MERGE to UPDATE a third table 14 36
convert in derived column 7 27
sql server insert 12 28
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now