Inserting values from a list - 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 2000 Forums

SQL Server Development (2000)

Inserting values from a list

Author

Topic

netedk
Starting Member

11 Posts

Posted - 2003-04-03 : 14:32:17

I have a table with two columns:
client, user

I want to insert a list of users into the table keeping the client same. The user's are in a list/ array in my application.

For example:
DK, 10
DK, 11
DK, 12
DK, 13
DK, 14
DK, 15

How can I do this using a single SQL statement?

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-03 : 15:04:19

Well what should the data look like after the SQL statement runs? Give us an example of a few rows of what the data should look like.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-03 : 15:09:53

I see a few choices:

1) a build a string with this format:

for i = 1 to listsize
s = s & " UNION ALL SELECT '" & value(i,1) & "'," & value(i,2)
next

and then through ADO run the following:

command.execute "insert into table (client, user) " + mid(s,13)

2) or, loop through and issue multiple inserts:

for i = 1 to listsize
command.execute "insert into table (client, user) values ('" & value(i,1) & "'," & value(i,2) & ")"
next i

Both have pros and cons. depends on how big your array is; option 1 might generate a string that is too long for SQL to process.

Hope that gives some ideas.

- Jeff

netedk
Starting Member

11 Posts

Posted - 2003-04-03 : 15:59:27

Tara,

I want the rows to look like:
DK, 10
DK, 11
DK, 12
DK, 13
DK, 14
DK, 15

after I have made the inserts.

Jeff,

Thanks for your reply. I am currently using the second option that you have mentioned - and that is very resource intensive for long lists. So is the first option.

I was looking at something similar to
INSERT INTO userTable (client, user)
SELECT client='DK', user FROM myLIST

obviously above sql does not work. But I was wondering if there is something similar to this that could do it - something that will not require me to loop thro' each of the list items.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-03 : 16:43:24

Does this work?

INSERT INTO userTable (client, user)
SELECT 'DK', user
FROM myLIST

Is the userTable empty right now?

Tara

danielhai
Yak Posting Veteran

50 Posts

Posted - 2003-04-03 : 17:15:15

using this function:

INSERT INTO userTable (client, user)
SELECT client='DK', [ID] FROM dbo.fn_GetDelimitedAsTable('10,11,12,13,14,15',',')


CREATE FUNCTION dbo.fn_GetDelimitedAsTable 
	(@String varchar(8000), 
	@Delimiter varchar(1) = ','
)

RETURNS 

	@Return Table (
		RowID int identity NOT NULL,
		ID varchar(8000)
	)

AS  

BEGIN 

	declare @Current smallint,
		@TempString varchar(500)
	
	set @Current = 1
	
	while (CHARINDEX(@Delimiter,@String,@Current) > 0)
	  begin
		set @tempstring = CHARINDEX(@Delimiter,@String,@Current) - @Current
	
		if @tempstring <> 0
		  begin
			insert into @Return (ID) values (substring(@String,@Current, cast(@tempstring as int)) )
		  end
	
		set @Current = CHARINDEX(@Delimiter,@String,@Current) + 1
	  end
	
	if substring(@String,@Current, len(@String)) <> ''
		insert into @Return (ID) values (substring(@String,@Current, len(@String)) )
	return 
END

Edited by - danielhai on 04/03/2003 17:15:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources