Solved

SQL Syntax

Posted on 2009-04-01
7
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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