August 6, 2013 at 1:09 pm
Hi,
I have this function that is used to fill a SQL table - which is then used for a data export.
ALTER FUNCTION [dbo].[itfAPVEND]
(
-- Add the parameters for the function here
)
RETURNS
@APVEND TABLE
([CO] [char](5) NOT NULL,
[A] [char](12) NOT NULL,
[char](60) NULL,
[C] [char](30) NULL,
[D] [char](30) NULL,
[E] [char](30) NULL,
[F] [char](30) NULL,
[G] [char](20) NULL,
[H] [char](20) NULL,
[char](20) NULL,
[J] [char](20) NULL,
[K] [text] NULL,
[L] [char](15) NULL
)
AS
BEGIN
insert into @APVEND
select 'WSIAP', * from
openquery(WSIAP, 'select VendorKey, Vendorname, ContactName,
Vendoraddress1, Vendoraddress2, Vendoraddress3,
VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND')
insert into @APVEND
select 'ASSFD', * from
openquery(ASSFD, 'select VendorKey, Vendorname, ContactName,
Vendoraddress1, Vendoraddress2, Vendoraddress3,
VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND')
RETURN;
END
Problem is - I now need to execute this function every night. What is the best way to accomplish this? I was thinking of using a SQL job and call it from a query. BUt I can't seem to get the function to execute from a query. How would I execute this function from a query?
August 6, 2013 at 1:32 pm
select * from dbo.itfAPVEND()
Be careful here though. You have a table valued function but it is a multi-statement function. The performance can end up even worse than scalar functions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 1:36 pm
Sean,
Running that returns records..but doesn't exec and fill the vendor table.
August 6, 2013 at 1:38 pm
If I do this it works..
insert into Vendors
SELECT * FROM dbo.itfAPVEND()
Thanks for your help Sean!
August 6, 2013 at 1:40 pm
Sean Lange (8/6/2013)
select * from dbo.itfAPVEND()
Be careful here though. You have a table valued function but it is a multi-statement function. The performance can end up even worse than scalar functions.
Your function only has 2 statement and they both insert into the local table variable. There is no insert into a vendors table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 1:40 pm
krypto69 (8/6/2013)
If I do this it works..insert into Vendors
SELECT * FROM dbo.itfAPVEND()
Thanks for your help Sean!
Glad you got it working. I must have been typing my last response when you posted this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy