Basic T-SQL

Question

Post reply

Basic T-SQL

darryl-715744

SSC Enthusiast

Points: 144
More actions
December 12, 2008 at 3:02 pm

#128360

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

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.

darryl-715744 SSC Enthusiast Points: 144 More actions · Answer 3

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.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

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.

darryl-715744 SSC Enthusiast Points: 144 More actions · Answer 5

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.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

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.

darryl-715744 SSC Enthusiast Points: 144 More actions · Answer 7

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

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.

ps. One Orange Chip Points: 29255 More actions · Answer 8

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

Pradeep Singh

darryl-715744 SSC Enthusiast Points: 144 More actions · Answer 9

darryl-715744

SSC Enthusiast

Points: 144

December 15, 2008 at 9:28 am

#912559

thanks. did the trick.

Now I need to learn t-sql.