issue to insert with a set of ids

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 2000 Forums

Transact-SQL (2000)

issue to insert with a set of ids

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-16 : 15:28:35

I have an insert statement..only for that particular value a new record should be inserted eg:

insert into tbl_emp_details
(emp_id,place,date_of_join,duration)
values (1244,'ATL','12/12/2004',4)

here I have a list of emp_ids only the emp_ids are going to change and the rest is going to be the same..1234,456456,4767,57878,7978,342,23,34 (emp_id list)..I don't want to write individual insert becasuse the rest is all same..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 15:36:49

search for "dbo.split" here and you can use that function like so...

Insert Into tbl_emp_Details
Select A.data, 'ATL','12/12/2004',4
From dbo.split(@empList,',') A

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 15:37:34

realized I had it handy...


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 15:38:45

this should help:

declare @place varchar(10), @date_of_join datetime, @duration int
select @place = 'ATL', @date_of_join = '12/12/2004', @duration = 4

declare @temp table (emp_id int)
insert into @temp
select 1234 union all select 456456 union all select 4767 union all select 57878 union all select 7978 union all select 342 union all select 23 union all select 34

insert into tbl_emp_details
(emp_id,place,date_of_join,duration)
select emp_id, @place, @date_of_join, @duration
from @temp

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 15:40:03

or you could use dbo.split

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 15:41:25

spirit....have you been living here recently

I was only a few posts behind you like a 10 days or so ago... and I have probably post 50 or so myself

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 15:45:36

no i wasn't

, but i have it on active topics and go check about once per hour what's new. it helps clear the head...

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 15:51:30

ahh... Well i did get sidetracked looking at the square puzzle of ken's...

oh... and adding photos to my website. I finally got to some of my more recent photos :)

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 15:54:21

that i must see...

yeah how is that puzzle going? have u solved it yet?

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 16:01:43

well ken blew me out of the water with a 7 second 50 move solution...

I have to do more work to get mine to be faster, but I can't even get it to find a 50 solution at any speed.
I need to disect his code, because it looks foreign to me right now... which is bad.

Maybe I can improve upon his... maybe not!

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 16:05:23

use your
"watered-down skills" as a maths major

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-16 : 16:41:30

Another small issue here is the convertion of the data type in one of the column to money type

spirit1
Cybernetic Yak Master

11752 Posts