We help IT Professionals succeed at work.

SQL Query Help - Multiple Insert

217 Views
Last Modified: 2012-05-11
Team,

Front end ASP has 3 Textboxes & 1 Textarea.

Backend - SQL - has all fields as varchar(255)

In Textarea, users will input NTlogins (Comma Seperated). How do I run a query where i save the data like the following

Textbox1 Textbox2 Texbox3 Ntlogin
Textbox1 Textbox2 Texbox3 Ntlogin1
Textbox1 Textbox2 Texbox3 Ntlogin2
Textbox1 Textbox2 Texbox3 Ntlogin3 etc...,
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
"How do I run a query where i save the data like the following"
what query are you trying to run? what are you trying to do? need clarification...

Author

Commented:
HainKurt - I dont have any query. I dont know how to write a query for this. hence the ask.
From frontend,

Pass the all the four fields as parameters in your stored procedure,
@Textbox1 (textbox1 value)
@Textbox2 (textbox2 value)
@Textbox3 (textbox3 value)
@NTLoginList (comma seperated string)

At backend, in stored procedure
@Textbox1  varchar(255),
@Textbox2 varchar(255),
@Textbox3 varchar(255),
@NTLoginList varchar(max)

Here you need to split the NTLoginList with comma with split function.
Then you can insert the splitted NTLogin items one by one (from the array of NTLogin) with other three values Textbox1,Textbox2,Textbox3 in your database table

Please refer this nice article to split and loop the string value to insert data
http://sqltutorials.blogspot.com/2007/09/sql-function-split.html
As per my understanding you want to insert into databse multple entries at one time. 3 of the fields in your insert statment will have same value. but only last column will have different value. Your problem is to seperate those comma separate value and insert them to database.

if that is the case.

I will assue that three textbox values are in these three variables when you pass them to sql server

declare @Text1 varchar(255)
declare@Text2 varchar(255)
declare@Text3 varchar(255)
declare@NTLogin varchar(255)


set @Text1  =' Textbox1'
set @Text2  =' Textbox2'
set @Text3 = ' Textbox3'
set @NTLogin =  'Ntlogin,Ntlogin1,Ntlogin2,)

then you can use this insert statement after creating function as in my previous post.

insert into yourtable(Text1, Text2, Text3, Login)
select @Text1, @Text2,@Text3, item from dbo.fnSplit(@NTLogin, ',')

this will insert data like you desired


SAMIR BHOGAYTATeam Lead
CERTIFIED EXPERT

Commented:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.