SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert into from udf
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GazNewt
Starting Member

14 Posts

Posted - 06/12/2007 :  17:32:53  Show Profile  Reply with Quote
Hi all,

I have a table which I need to insert multiple rows into from a table-valued user defined function. Half the columns I need to insert are fixed values for this "batch" and the other half come from the udf.

Table-valued functions and 'insert into' are new to me but all the examples I've seen use select * from the udf.

How does select * it know which fields to map from the udf to the target table?

Is it possible to do what I want? I need to define which fields from the udf go into which fields in the target table.

Thanks

sfortner
Yak Posting Veteran

USA
63 Posts

Posted - 06/12/2007 :  18:02:25  Show Profile  Reply with Quote
Can you use a union? All a union does is concatenate the result sets from two or more different sources into one result set, which you can then use to insert into your table. For example,

insert into <table>(<column1, column2>)
select column1, column2 from <table_that's_fixed>
union
select * from udf (assuming it returns the same columns you're expecting for the insert)

Be aware that when you're using a union the first subquery statement must have the same type of columns, as well as the same number of columns, as the second and/or subsequent subqueries, or it throws a syntax error.

Or, if you're truly batching, just call your insert statement for all the fixed values, e.g.,

insert into <table>(<columns>) values(<values>) or insert into <table>(<columns>) select @value1, @value2, etc.
insert into <table>(<columns>) values(<values>) or insert into <table>(<columns>) select @value1, @value2, etc.

followed by the single call to the udf that inserts those values, e.g.,

insert into <table>(<columns>) select * from udf



--Steve

Edited by - sfortner on 06/12/2007 18:07:02
Go to Top of Page

GazNewt
Starting Member

14 Posts

Posted - 06/12/2007 :  18:08:19  Show Profile  Reply with Quote
quote:
Originally posted by sfortner

Can you use a union? All a union does is concatenate the results from two different sources into one or more result sets, which you can then use to into your table. Or, if you're truly batching..



Thanks for the reply. When I say "batch" I mean that there are certain values that are stamped for this data set such as the date from, date to and a group number, the rest of the values come from the result set from the udf.

Even with a union, how does sql know which values from the udf map to the fields available in the target field? Do they have to be named the same in the udf as the target table's columns?
Go to Top of Page

sfortner
Yak Posting Veteran

USA
63 Posts

Posted - 06/12/2007 :  18:26:39  Show Profile  Reply with Quote
quote:

Even with a union, how does sql know which values from the udf map to the fields available in the target field? Do they have to be named the same in the udf as the target table's columns?



If I understand your question properly, the udf is going to return columns just like a query would. Another words, Kristen has a split function I've used for a couple of stored procedures I wrote, and it returns two columns, item and value. It doesn't matter what these column names are, just that they are an int and a varchar(8000), and so if the table you are inserting into will take these datatypes, you're good. It's the same as if I were to write a query

select item, value from <some_table>

and insert that into your table. When you're writing your insert statements, your udf and your queries will need to return the same data types (or at least are compatible (implicitly cast?) with the data type in the table) that you specify in your

insert into <table>(column1, column2)

Column1 and column2 would have to match the data types returned by both the udf and the queries, in the example I'm using column1 would be an int and column2 would be varchar(8000). To make that more clear, <table> would have an int column/field and a varchar(8000) and those are the two fields you're inserting your data into. Hope that helps, and hopefully I didn't ramble too much...

--Steve
Go to Top of Page

GazNewt
Starting Member

14 Posts

Posted - 06/13/2007 :  03:22:46  Show Profile  Reply with Quote
I have done a test using insert into which uses partly fixed values and partly the results of the udf call but I get this error :

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns

Makes sense when you look at the code, the first three values are fixed for each record I want to insert and the rest come from the udf but the parser probably sees the udf call as a single value even though it is returning a table :

insert into
   leaderboard_photographers
   (year, month, position, accountid, gardenid, score, numberofphotos, numberofratings)
select
   2000,10,1,dbo.myfunction_photographers_top_forperiod__worker_1(100,@monthtocheck,@monthtocheck+30)

Here's my udf which returns the rest of the values :

ALTER FUNCTION [dbo].[myfunction_photographers_top_forperiod__worker_1]
	(@numbertoretrieve int,
	@startdate datetime,
	@enddate datetime)
RETURNS @tempphotographers TABLE
	(accountid bigint,
	gardenid bigint,
	score float,
	photocount bigint,
	numberofratings bigint)
AS
begin 
	insert @tempphotographers
	select top(@numbertoretrieve)
		photo.accountid as accountid,
		photo.gardenid as gardenid,
		avg(photo.rating) as score,
		count_big(photo.id) as photocount,
		sum(photo.numberofratings) as numberofratings
	from
		photo
	join
		account on account.id=accountid
	where
		dateused >= @startdate
	and
		dateused < @enddate
	and rating is not null
	group by
		accountid, gardenid
	order by score desc
return
end

And here's the table I'm trying to insert records into :

CREATE TABLE [dbo].[leaderboard_photographers](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[year] [int] NOT NULL,
	[month] [tinyint] NOT NULL,
	[position] [int] NOT NULL,
	[accountid] [bigint] NOT NULL,
	[gardenid] [bigint] NOT NULL,
	[score] [float] NOT NULL,
	[numberofphotos] [int] NOT NULL,
	[numberofratings] [bigint] NOT NULL
)


There must be a way of doing this I just don't know enough about sql to figure out what to do. Any pointers will be greatly appreciated.

Thanks
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 06/13/2007 :  03:41:13  Show Profile  Reply with Quote
insert into
leaderboard_photographers
(year, month, position, accountid, gardenid, score, numberofphotos, numberofratings)
select
2000,10,1,accountid,gardenid,score,photocount,numberofratings
from
dbo.myfunction_photographers_top_forperiod__worker_1(100,@monthtocheck,@monthtocheck+30)

K. Matsumura
Go to Top of Page

GazNewt
Starting Member

14 Posts

Posted - 06/13/2007 :  05:16:13  Show Profile  Reply with Quote
quote:
Originally posted by Koji Matsumura

insert into
leaderboard_photographers
(year, month, position, accountid, gardenid, score, numberofphotos, numberofratings)
select
2000,10,1,accountid,gardenid,score,photocount,numberofratings
from
dbo.myfunction_photographers_top_forperiod__worker_1(100,@monthtocheck,@monthtocheck+30)



Thanks a lot Koji that's it, I'm learning. Knew it would be simple cheers!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/13/2007 :  06:30:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84946 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84743


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000