December 12, 2008 at 3:02 pm
Greetings,
Have just moved to Microsoft SQL Server 2005. Am starting to learn T-SQL. But I have an immediate need, so this request.
I have database1 with table customer. I have database2 with table ship. What I need to do is (pseudo code):
for each database1.customer:
for each database2.ship of database1.customer:
do something interesting.
Thanks for any help.
December 12, 2008 at 3:07 pm
Gotta be a little more specific with "do something interesting".
Are these databases on the same server?
If they are, you can extract the information like so:
SELECT *
From Database1.dbo.Customer C
INNER JOIN Databaes2.dbo.Ship S ON C.CustomerID = S.CustomerID
Does that help you? If not, please be more specific.
December 12, 2008 at 3:10 pm
Looks like you want to do an INNER JOIN between the two tables. That's about as much as I can tell from your psuedo code.
Can't tell if you just want to select some data, insert some data some where else, perform an update, or maybe even dlete some data. You really need to provide more information about what you are trying to accomplish. You should also read the first article I have linked below in my signature block.
December 12, 2008 at 3:15 pm
Sorry, should have been clear. Both db's are on the same server.
Database1.customer has 1 field called kit. It is text. Database2.ship has multiple fields but one of them is kit. I want to walk through database1.customer and find all records in database2.ship that have the same kit value. When I find a matching record in database2.ship, I need to change a field value.
That is what I am trying to do.
December 12, 2008 at 3:21 pm
Read the article that both Seth and I have in our signature block. It will walk you through the steps you need to do to get the best help possible.
I can tell you that you will be doing an update statement, but without the table DDL, sample data, expected results based on the sample data, and what you have done so far to solve your problem, there really isn't much more we can do at the moment.
If you follow the guidelines in the article I am referencing, you will get much better answers to your questions as we will be able to help you a lot easier.
December 12, 2008 at 3:33 pm
Thanks for the pointer. I will try to do it better.
First of all, the only code I tried was to select the records. If figured the first step was to have the conditions correct, then I could change from a select to an update.
Database1.customer
has one field named kit. It is text and contains text like: FF-00PD04.
Database2.ship has much bigger records and some data is not public. It has a field named kit. It contains values like FF-00PD04, FF-00PD05.
For every record in database1.customer table, I want to find all records in database2.ship where its kit field matches database1.customer.kit. When it matches, I want to assign to a field named flag in database2.ship the value 1.
Sorry but thats the best I can describe it right now.
December 12, 2008 at 3:39 pm
darryl (12/12/2008)
Thanks for the pointer. I will try to do it better.First of all, the only code I tried was to select the records. If figured the first step was to have the conditions correct, then I could change from a select to an update.
Database1.customer
has one field named kit. It is text and contains text like: FF-00PD04.
Database2.ship has much bigger records and some data is not public. It has a field named kit. It contains values like FF-00PD04, FF-00PD05.
For every record in database1.customer table, I want to find all records in database2.ship where its kit field matches database1.customer.kit. When it matches, I want to assign to a field named flag in database2.ship the value 1.
Sorry but thats the best I can describe it right now.
That is a good way to start, you are doing good. Regarding the sample data, if there is proprietary data, change it to some nonsense values that play the part of the proprietary data. Example, a customers SSN may be 123-45-6789 and you change it to 333-33-3333, or some other values but I think you get the gist.
Show us the code you have written so far to select the records that you will want to change later with an UPDATE statement.
December 13, 2008 at 7:53 am
ok, per the link here is data from database2.ship:
SELECT ' 66359','1','FV-WI0090', UNION ALL
SELECT ' 67109','1','FV-S2030V', UNION ALL
SELECT ' 68586','4','FS-00E500M', UNION ALL
SELECT ' 68586','1','FS-WSW500', UNION ALL
SELECT ' 68586','2','FS-WSW501', UNION ALL
SELECT ' 68586','3','FS-WSW502', UNION ALL
SELECT ' 76744','1','FC-JD0220', UNION ALL
SELECT ' 76744','1','FC-JD0220', UNION ALL
SELECT ' 76744','1','FC-JD0220', UNION ALL
First Column is order number, second is order line, third is kit.
What code I have tried. Truthfully, after having to write this down, I find the code I was trying to run, garbage. It was syntactically wrong. At this point, I don't know how to do it. A guess would be a cursor. Since this is a one time deal, performance is NOT an issue.
thanks.
December 13, 2008 at 8:11 am
I think the script written by Garadin should help you on the Inner Join that is required her.
SELECT *
From Database1.dbo.Customer C
INNER JOIN Databaes2.dbo.Ship S ON C.CustomerID = S.CustomerID
Modify it according to your table structure... something like this...
Update Database2.dbo.ship
SET flag=1 From Database2.dbo.ship, database1.dbo.customer
where
Database2.dbo.ship.kit=database1.dbo.customer.kit
December 15, 2008 at 9:28 am
thanks. did the trick.
Now I need to learn t-sql.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply