June 10, 2018 at 10:25 pm
Hi,
I have a table on database A one field has an IP address of a host, sql 2016.
I have another database B, where I have a stored procedure that where if I give it an IP address it will give me host name, OS information type, version, etc, and resource related information from one SP query.
I would like to extend the table on database A with a number of columns from the result of an SP call against database B.
Database A and B can be on the same server if needed.
The only way that I can think of doing this is by splitting the SP into multiple functions, one for each field and do a calculated field, perhaps persistent. But I think that this will exponentially increase the number of queries to database B.
There are new records added to the DB A, every 30 minutes.
Could someone think of any suggestions on other ways of doing this, perhaps more efficient.
Thanks.
June 11, 2018 at 6:50 am
picante - Sunday, June 10, 2018 10:25 PMHi,
I have a table on database A one field has an IP address of a host, sql 2016.
I have another database B, where I have a stored procedure that where if I give it an IP address it will give me host name, OS information type, version, etc, and resource related information from one SP query.
I would like to extend the table on database A with a number of columns from the result of an SP call against database B.
Database A and B can be on the same server if needed.
The only way that I can think of doing this is by splitting the SP into multiple functions, one for each field and do a calculated field, perhaps persistent. But I think that this will exponentially increase the number of queries to database B.
There are new records added to the DB A, every 30 minutes.
Could someone think of any suggestions on other ways of doing this, perhaps more efficient.
Thanks.
First order of business is to at least add the columns to the table on database A. For now, they can allow NULL values, and you just need to know what the data types are that get returned by the stored procedure... Having added the columns, the next step is to see if it's possible to take the code from the stored procedure and re-write it into an inline table-valued function. If that's possible, you could then use that function to directly perform the update using CROSS APPLY in the update query. If not, then you could at least take the code of the stored procedure and re-write it to not limit the output to that from a single IP address, and instead allow all the rows to be returned, and insert those rows into a temp table. Then you do the update by joining to the temp table on the column with the IP address. You don't need separate queries. If you aren't familiar with CROSS APPLY, go ahead and search for it online and read up on the APPLY operator, as it provides some really cool capabilities that traditional JOINs do not.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2018 at 12:33 pm
Create a new table on database A to hold database B's data: host name, OS info, etc. For the sake of argument, let's say the new table is named "ip_details". The table should be uniquely clustered on ip (you can naturally also make it a pk if you prefer, but the critical thing is that it clustering and, in this case, unique).
If you need to keep the ip_details table always up to date, you can set up full replication. Not likely that data will change that much, so maybe just copy the data from table B periodically to ip_details.
Then simply join from table A table(s) to the ip_details table whenever you need that info.
A separate table would also allow other processes to easily get to that data if they needed it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 12, 2018 at 5:49 am
ScottPletcher - Monday, June 11, 2018 12:33 PMCreate a new table on database A to hold database B's data: host name, OS info, etc. For the sake of argument, let's say the new table is named "ip_details". The table should be uniquely clustered on ip (you can naturally also make it a pk if you prefer, but the critical thing is that it clustering and, in this case, unique).If you need to keep the ip_details table always up to date, you can set up full replication. Not likely that data will change that much, so maybe just copy the data from table B periodically to ip_details.
Then simply join from table A table(s) to the ip_details table whenever you need that info.
A separate table would also allow other processes to easily get to that data if they needed it.
Thank you for your reply, I am no sure if this approach will work as the data in database B can't be copied, it is in multiple tables, it needs to be queried using an SP.
June 12, 2018 at 5:54 am
sgmunson - Monday, June 11, 2018 6:50 AMpicante - Sunday, June 10, 2018 10:25 PMHi,
I have a table on database A one field has an IP address of a host, sql 2016.
I have another database B, where I have a stored procedure that where if I give it an IP address it will give me host name, OS information type, version, etc, and resource related information from one SP query.
I would like to extend the table on database A with a number of columns from the result of an SP call against database B.
Database A and B can be on the same server if needed.
The only way that I can think of doing this is by splitting the SP into multiple functions, one for each field and do a calculated field, perhaps persistent. But I think that this will exponentially increase the number of queries to database B.
There are new records added to the DB A, every 30 minutes.
Could someone think of any suggestions on other ways of doing this, perhaps more efficient.
Thanks.First order of business is to at least add the columns to the table on database A. For now, they can allow NULL values, and you just need to know what the data types are that get returned by the stored procedure... Having added the columns, the next step is to see if it's possible to take the code from the stored procedure and re-write it into an inline table-valued function. If that's possible, you could then use that function to directly perform the update using CROSS APPLY in the update query. If not, then you could at least take the code of the stored procedure and re-write it to not limit the output to that from a single IP address, and instead allow all the rows to be returned, and insert those rows into a temp table. Then you do the update by joining to the temp table on the column with the IP address. You don't need separate queries. If you aren't familiar with CROSS APPLY, go ahead and search for it online and read up on the APPLY operator, as it provides some really cool capabilities that traditional JOINs do not.
Thank you for your reply, I think this option will work well for me, I expanded the table to with new fields, including a new field called enriched_last that will have the date it was last enriched with in the intention of being able to update the record when enriched_last is null. I have managed to convert the SP to a function and I have now learned to do a cross apply. I now just need to figure out how to update the records on the table, but I'm sure i'm not that far from that.
June 12, 2018 at 2:25 pm
picante - Tuesday, June 12, 2018 5:54 AMsgmunson - Monday, June 11, 2018 6:50 AMpicante - Sunday, June 10, 2018 10:25 PMHi,
I have a table on database A one field has an IP address of a host, sql 2016.
I have another database B, where I have a stored procedure that where if I give it an IP address it will give me host name, OS information type, version, etc, and resource related information from one SP query.
I would like to extend the table on database A with a number of columns from the result of an SP call against database B.
Database A and B can be on the same server if needed.
The only way that I can think of doing this is by splitting the SP into multiple functions, one for each field and do a calculated field, perhaps persistent. But I think that this will exponentially increase the number of queries to database B.
There are new records added to the DB A, every 30 minutes.
Could someone think of any suggestions on other ways of doing this, perhaps more efficient.
Thanks.First order of business is to at least add the columns to the table on database A. For now, they can allow NULL values, and you just need to know what the data types are that get returned by the stored procedure... Having added the columns, the next step is to see if it's possible to take the code from the stored procedure and re-write it into an inline table-valued function. If that's possible, you could then use that function to directly perform the update using CROSS APPLY in the update query. If not, then you could at least take the code of the stored procedure and re-write it to not limit the output to that from a single IP address, and instead allow all the rows to be returned, and insert those rows into a temp table. Then you do the update by joining to the temp table on the column with the IP address. You don't need separate queries. If you aren't familiar with CROSS APPLY, go ahead and search for it online and read up on the APPLY operator, as it provides some really cool capabilities that traditional JOINs do not.
Thank you for your reply, I think this option will work well for me, I expanded the table to with new fields, including a new field called enriched_last that will have the date it was last enriched with in the intention of being able to update the record when enriched_last is null. I have managed to convert the SP to a function and I have now learned to do a cross apply. I now just need to figure out how to update the records on the table, but I'm sure i'm not that far from that.
Glad I could help. I suspect doing the update is just a matter of either a JOIN to your function (on the IP address value), or a CROSS APPLY to it. If you'll always get a single record for a given IP address, then it may not matter which technique you use, provided that said function provides all the new values in the row it returns.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply