Insert question - 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 question

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 12:13:36

Hi,

I am trying to do the following. I have a sproc that accepts 2 parms.
@parm1 could be just one value or comma delimited value
@parm2 is the same could be just one value or comma delimited value
So I am using a function str_CreateTableFromList I got from this site (awesome function!) that creates a @temp table from the passed in paramaters. Using this I want to INSERT INTO a table as such

INSERT INTO [dbo].[VMyTable]
(Number)
SELECT X AS Number
FROM dbo.str_CreateTableFromList(@Parm1, DEFAULT)

My problem is @parm1 could have one or more values so could @parm2 so how can write it so that even if @parm1 has one value but @Parm2 has 2 values, it creates two rows. So if @parm1 is 'Cheese' and @parm2 is 'Shelf1, Shelf2' I am able to create 2 rows
Field1 Field2
--------------------------
Cheese Shelf1
Cheese Shelf2

Help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 12:16:37

Why are you putting the parameter values into table. You can use the function directly in joins for each of parameter with your data and filter your results. If you want detailed solution, post what you're trying to get here with tables involved.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 13:31:34

Ok I think I got it, this bring the desired result
SELECT tag.strJoinerField,
wo.strJoinerField ,
@UserName
FROM dbo.str_CreateTableFromList('T18427', DEFAULT) tag,
dbo.str_CreateTableFromList('LN1175,LN1233', DEFAULT) wo

THANKS!!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 13:32:42

sadly it inserts only 1 row....
INSERT INTO MyTable
(Tag,
WO,
UserName)
SELECT tag.strJoinerField,
wo.strJoinerField ,
@UserName
FROM dbo.str_CreateTableFromList('T18427', DEFAULT) tag,
dbo.str_CreateTableFromList('LN1175,LN1233', DEFAULT) wo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:34:25

quote:
Originally posted by yosiasz

sadly it inserts only 1 row....
INSERT INTO MyTable
(Tag,
WO,
UserName)
SELECT tag.strJoinerField,
wo.strJoinerField ,
@UserName
FROM dbo.str_CreateTableFromList('T18427', DEFAULT) tag,
dbo.str_CreateTableFromList('LN1175,LN1233', DEFAULT) wo

Can i ask what's data you're trying to filter using this parameter values? Can you post that query?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 14:08:48

Visakh,

I am not trying to filter out any data. I am using it to populate my Table. Users using the front end select one row that contains work order(s) grouped together if they are the ordering same item and then select item(s) that can fill the work order(s)
You want to post which query? str_CreateTableFromList?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 14:27:16

quote:
Originally posted by yosiasz

Visakh,

I am not trying to filter out any data. I am using it to populate my Table. Users using the front end select one row that contains work order(s) grouped together if they are the ordering same item and then select item(s) that can fill the work order(s)
You want to post which query? str_CreateTableFromList?

then try like this:-

SELECT tag.strJoinerField,
wo.strJoinerField ,
@UserName
FROM dbo.str_CreateTableFromList('T18427', DEFAULT) tag
CROSS JOIN dbo.str_CreateTableFromList('LN1175,LN1233', DEFAULT) wo

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 14:45:46

ok very nice!! But when I sitck that into a sproc it only create one row? when i run it manually in dev Studio it creates two rows?

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 14:48:07

quote:
Originally posted by yosiasz

ok very nice!! But when I sitck that into a sproc it only create one row? when i run it manually in dev Studio it creates two rows?

Thank you very much

how are sticking this to sproc?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 14:55:12

CREATE PROC [dbo].usp_InsWorkOrderTags
@WorkOrderNum AS CHAR(6),
@TagNum AS VARCHAR(MAX),
@User AS VARCHAR(20)
AS

-- =========================================================
-- Object Name: usp_InsWorkOrderTags
-- Author:
-- Create date: 5/19/2008
-- Description: --
-- History Date Comments
-- =========================================================
BEGIN

INSERT INTO [dbo].[WorkOrders]
(TagNumber
,WorkOrderID
,[UserName])
SELECT tag.strJoinerField,
wo.WorkOrderID ,
@User
FROM dbo.str_CreateTableFromList(@TagNum, DEFAULT) tag
CROSS JOIN dbo.str_CreateTableFromList(@WorkOrderNum, DEFAULT) wo
INNER JOIN dbo.VWorkOrders wop
ON wo.nstrJoinerField = wop.WONumber

END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-28 : 15:29:27

While I agree that you CAN join directly to a table valued function, if you have tables of any size the performance is going to suffer. If possible, it is a good idea to put the results of a table valued function into a table variable or temp table with a suitable primary key. Just thought I'd mention it.. :)

quote:
Originally posted by visakh16

Why are you putting the parameter values into table. You can use the function directly in joins for each of parameter with your data and filter your results. If you want detailed solution, post what you're trying to get here with tables involved.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 15:32:07

Lamprey

I considerd that during initial design. It is fairly light weight insertion..therefore the use of table valued function

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-28 : 15:40:46

can somebody please come and punch me or wack me on the head @WorkOrderNum AS CHAR(6) was the problem!! it should be longer than char(6). Truly sorry to have wasted your afternoon. :O(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 01:07:35

quote:
Originally posted by Lamprey

While I agree that you CAN join directly to a table valued function, if you have tables of any size the performance is going to suffer. If possible, it is a good idea to put the results of a table valued function into a table variable or temp table with a suitable primary key. Just thought I'd mention it.. :)

quote:
Originally posted by visakh16

Why are you putting the parameter values into table. You can use the function directly in joins for each of parameter with your data and filter your results. If you want detailed solution, post what you're trying to get here with tables involved.

Thanks Lamprey for the info

. I really appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 01:08:28

quote:
Originally posted by yosiasz

can somebody please come and punch me or wack me on the head @WorkOrderNum AS CHAR(6) was the problem!! it should be longer than char(6). Truly sorry to have wasted your afternoon. :O(

No worries.Glad that you spot it out yourselves.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources