November 11, 2008 at 9:05 am
This feels like a basic question. I used a temp table inside a stored procedure for the first time recently to do an Insert to a SQL table. So Temp tables are still very new to me.
Now, after the Insert, I need to update another SQL Table, all from inside the same stored procedure.
The Temp table is filled from a BULK Insert inside the stored procedure. After the BULK Insert I do something like this to Insert:
INSERT MyTable (field1, field2, field3, field4, field5)
SELECT field1, field2, field3, field4, field5
FROM ##MyTempTable
Now, after the above Insert, I need to do an Update from ##MyTempTable on another SQL Table.
Something like this is my thought:
Update MyTable2 m
SET m.field1 = ##MyTempTable.field1
WHERE m.field2 = ##MyTempTable.field2
How do I do this? I'ved Googled this and I am not finding much.
I know it's a novice question, thanks for helping.
November 11, 2008 at 10:05 am
you are going to kick yourself...you did all the work, and are just missing the UPDATE ...FROM part:
Update MyTable2 m
SET m.field1 = ##MyTempTable.field1
FROM ##MyTempTable
WHERE m.field2 = ##MyTempTable.field2
Lowell
November 11, 2008 at 12:49 pm
Bingo, thanks
November 12, 2008 at 7:29 am
So my next challenge is this. The sql table that gets updated in the above UPDATE exists in 3 different databases. The temp table stores an id value which will allow me to do a lookup to get the database name.
In .NET code I would simply iterate over the temp table, grab the database name id value, go get the database name (may be able to use DB_NAME()), open my connection and do the update. How can I do this from within this stored procedure? I need to iterate over the temp table, get the db name id value, look up the db name, then use that db name in my UPDATE, do the UPDATE, move to the next row in the temp table.
Thanks
November 12, 2008 at 9:27 am
ok, since it appears the databases exist on the same server, you could do this:
Update DataBase1.dbo.MyTable2 m
SET m.field1 = ##MyTempTable.field1
FROM ##MyTempTable
WHERE m.field2 = ##MyTempTable.field2
Update DataBase2.dbo.MyTable2 m
SET m.field1 = ##MyTempTable.field1
FROM ##MyTempTable
WHERE m.field2 = ##MyTempTable.field2
Update DataBase3.dbo.MyTable2 m
SET m.field1 = ##MyTempTable.field1
FROM ##MyTempTable
WHERE m.field2 = ##MyTempTable.field2
Lowell
November 12, 2008 at 9:39 am
"since it appears the databases exist on the same server, you could do this:"
They do not, they exist on 3 different servers.
Each row in the temp table will store 1 of 3 db values, say "1" or "2" or "3". I can take this value, do a select on another table in the database the stored procedure is exectuing in to get the database name and server name.
So, lets take the 1st 3 rows in the temp table. One stores 1, the next one stores 2 and the next one stores 3. I need to iterate over each, grab the value 1, 2 or 3, then do my db name look up, then do an UPDATE.
November 13, 2008 at 8:18 am
gregarobinson (11/12/2008)
"since it appears the databases exist on the same server, you could do this:"They do not, they exist on 3 different servers.
Each row in the temp table will store 1 of 3 db values, say "1" or "2" or "3". I can take this value, do a select on another table in the database the stored procedure is exectuing in to get the database name and server name.
So, lets take the 1st 3 rows in the temp table. One stores 1, the next one stores 2 and the next one stores 3. I need to iterate over each, grab the value 1, 2 or 3, then do my db name look up, then do an UPDATE.
Are you saying that your field2 is actually the db name? Or a pointer to another (not previously shown) table that stores the server/db name?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 13, 2008 at 8:22 am
One of the fields in the .CSV file that is BULK INSERTED to a temp table stores a key, call it "theID" to a lookup table where I can select the database name per the value of theID field.
SELECT database name from lookup table WHERE lookuptable.key = temptable.theID
November 13, 2008 at 8:24 am
You can set up linked servers, which will be there and allow you to do updates. More admin work here.
you can set up an openquery/openrowset statement to do the work, but you have to then have credentials to make this happen.
Things inside SQL Server instances are not linked like AD or something. They are separate pieces of software. Essentially you need to do what you'd do in .NET. Make a connection to the other server and perform the update.
November 13, 2008 at 8:43 am
Yup, db admin is setting up linked servers. What I need to figure out is how to write the UPDATE statement in the stored procedure....update from the temp table to the different servers/same table name.
Thanks
November 13, 2008 at 8:49 am
Once it's linked, you can just add the server name on the front.
UPDATE [serverName].dbname.author.table
SET ....etc.
Not sure how you do that if you have to look up the names though, I guess dynamic sql and stuff the server name/db name into a variable.
Or you could hard-code it if you know what the server/dbname values will be each time, and just have multiple updates.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 13, 2008 at 9:11 am
I think I am missing something basic here jcrawf02.
How do I iterate over the temp table to retrieve the temp table id value? In .NET code i would simply do a For Each over the temp table, grab the id, lookup the dbname, build my update based on the values in the temp table, execute my command, move on to the next temp table row, do it again.
November 13, 2008 at 9:44 am
Ok, I'm not sure exactly what you mean when you state you are 'iterating over the temp table', so please correct me if I'm misinterpreting.
From what I understand, you are of the impression that in order to process your update, you are only using one row of the temp table at a time? So you are thinking you need to process each row until you reach the end of the table, like you would for an array?
If so, I've got great news for you, the SQL is going to process a *set* of data, not just one row at a time. You would only have to have three statements, one for each server where your database/table exists, and the UPDATE will set field1 = ##tempTable.field1 for *ALL* rows in one shot.
If that doesn't help, please let me know what I'm misinterpreting.
Thanks,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 13, 2008 at 9:47 am
That helps. It's one of those things I will need to 'see' work to 'get it'.
Thanks for the feedback.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply