Insert into from udf - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert into from udf

Author

Topic

GazNewt
Starting Member

14 Posts

Posted - 2007-06-12 : 17:32:53

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

63 Posts

Posted - 2007-06-12 : 18:02:25

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

GazNewt
Starting Member

14 Posts

Posted - 2007-06-12 : 18:08:19

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?

sfortner
Yak Posting Veteran

63 Posts

Posted - 2007-06-12 : 18:26:39

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

GazNewt
Starting Member

14 Posts

Posted - 2007-06-13 : 03:22:46

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

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-13 : 03:41:13

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

GazNewt
Starting Member

14 Posts

Posted - 2007-06-13 : 05:16:13

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-13 : 06:30:27

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources