How can i insert the values into sql database

Question

hi, how can i insert the values into sql database ex:manish,rama,raju etc....... these fields are inserted into table.may be the fields are 3 or 4 or etc.not particular numbers please help me.

Iftikar Hussain · Answer

Hi, First you need to change the Table definatition of TableMain as shown below CREATE TABLE TableMain ( Id int identity, Value varchar(50) ) Then if you want keep duplicate value then modify the SP as shown below ALTER PROCEDURE dbo.[usp_SaveCommaSeparatedValue] ( @Input varchar(1000) ) AS SET NOCOUNT ON INSERT INTO dbo.[TableMain](Value) SELECT value FROM [dbo].[udf_SplitCsvToTable](@Input,',') SET NOCOUNT OFF Regards, Iftikar Remember to click "Mark as Answer" on the post, if it helps you.

manjula d · Answer

using stored procedure how to update the data

Bineesh Viswanath · Answer

Hi i don't know how much fields your table has. A database will became unfruitful if the same group of values being repeated. to avoid these, u need a condition in the stored procedure. In variable declaration, can avoid Pk_column in insert because it will auto generated if set this to IDENTITY here is the stored procedure to insert data into database. CREATE PROCEDURE tbl_Name // variable declaration @name nvarchar (20), //condition to avoid data duplication if not exists ( select name from tbl_Name where name=@name) begin insert into tbl_Name(name) values (@name) end this statement is an easy way to add as we don't have to go table each time of insertion. when execute being clicked, a window opens for enter the name into the table. if this statement tested successfully, accept it.

Josephs · Answer

hi Iftikar Hussain why using using block what is advantages?

prathyusha · Answer

thank u very much. it's working perfectly......

prathyusha · Answer

alter PROCEDURE dbo.[usp_SaveCommaSeparatedValue] ( @Input varchar(1000) ) AS SET NOCOUNT ON DELETE FROM dbo.[TableMain] WHERE value=value INSERT INTO dbo.[TableMain](idx,Value) SELECT idx,value FROM [dbo].[udf_SplitCsvToTable](@Input,',') SET NOCOUNT OFF exec dbo.[usp_SaveCommaSeparatedValue] 'aaa,bbb,cccc' output like this 0 aaa 1 bbb 2 cccc exec dbo.[usp_SaveCommaSeparatedValue] 'aarra,bbb,cccc' first recod is deleted 0 aarra 1 bbb 2 cccc but i need like this 0 aaa 1 bbb 2 cccc 3 aaarra 4 bbb 5 cccc when i execute that it will be inserted. when iexecute second time second time records is there only i need full records please help me

Iftikar Hussain · Answer

That delete statement you need to execute before executing the insert statement inside the Stored Procedure Regards, Iftikar

prathyusha · Answer

hi Vulpes how can we insert depending on length. please help me

prathyusha · Answer

if i use that command table is empty. i want to fill the table continuously. please help me

Iftikar Hussain · Answer

Hi, before inserting the data into Table, always execute the delete statement with the primary key condition. DELETE FROM TableMai WHERE PK_Column=value Let me know if you need more information Regards, Iftikar Remember to click "Mark as Answer" on the post, if it helps you.

prathyusha · Answer

hi, thank u very much for your help. exec dbo.[usp_SaveCommaSeparatedValue] 'aaa,bbb,cccc' it is working and inserted successfully. but when i execute that procedure second time like exec dbo.[usp_SaveCommaSeparatedValue] 'd2,t3,ty4' this error will be displayed Msg 2627, Level 14, State 1, Procedure usp_SaveCommaSeparatedValue, Line 8 Violation of PRIMARY KEY constraint 'PK__TableMai__DC501A787132C993'. Cannot insert duplicate key in object 'dbo.TableMain'. The statement has been terminated. and by using coding incmd = new SqlCommand(); incmd.Connection = cn; //cn.Open(); incmd.CommandType = CommandType.StoredProcedure; incmd.CommandText = "usp_SaveCommaSeparatedValue"; //cmd.Parameters.AddWithValue("@Sno", "0"); cmd.Parameters.AddWithValue("@Input", txtkey.Text.Trim()); SqlDataAdapter da_in = new SqlDataAdapter(incmd); DataSet ds_in = new DataSet(); da_in.Fill(ds_in); Procedure or function 'usp_SaveCommaSeparatedValue' expects parameter '@Input', which was not supplied.this error will be displayed . please help me.it's very important to me

Vulpes · Answer

If you don't want to use a stored procedure, then you can use the String.Split method to split up the textbox contents into parts: string[] items = textBox1.Text.Split(','); and then have different INSERT INTO queries depending on the value of items.Length.

Iftikar Hussain · Answer

Hi, First create one User Defined Function in sql as shown below CREATE FUNCTION [dbo].[udf_SplitCsvToTable](@sText varchar(8000), @sDelim varchar(20) = ' ') RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000)) AS BEGIN DECLARE @idx smallint, @value varchar(8000), @bcontinue bit, @iStrike smallint, @iDelimlength tinyint IF @sDelim = 'Space' BEGIN SET @sDelim = ' ' END SET @idx = 0 SET @sText = LTrim(RTrim(@sText)) SET @iDelimlength = DATALENGTH(@sDelim) SET @bcontinue = 1 IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty')) BEGIN WHILE @bcontinue = 1 BEGIN --If you can find the delimiter in the text, retrieve the first element and --insert it with its index into the return table. IF CHARINDEX(@sDelim, @sText)>0 BEGIN SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1) BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END --Trim the element and its delimiter from the front of the string. --Increment the index and loop. SET @iStrike = DATALENGTH(@value) + @iDelimlength SET @idx = @idx + 1 SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike)) END ELSE BEGIN --If you can't find the delimiter in the text, @sText is the last value in --@retArray. SET @value = @sText BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END --Exit the WHILE loop. SET @bcontinue = 0 END END END ELSE BEGIN WHILE @bcontinue=1 BEGIN --If the delimiter is an empty string, check for remaining text --instead of a delimiter. Insert the first character into the --retArray table. Trim the character from the front of the string. --Increment the index and loop. IF DATALENGTH(@sText)>1 BEGIN SET @value = SUBSTRING(@sText,1,1) BEGIN INSERT @retArray (idx, value) VALUES (@idx, @value) END SET @idx = @idx+1 SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1) END ELSE BEGIN --One character remains. --Insert the character, and exit the WHILE loop. INSERT @retArray (idx, value) VALUES (@idx, @sText) SET @bcontinue = 0 END END END RETURN END Then create on new SP and pass comma separated value into it CREATE PROCEDURE usp_SaveCommaSeparatedValue ( @Input varchar(1000) ) AS SET NOCOUNT ON INSERT INTO TableMain(Id,Value) SELECT idx,value FROM udf_SplitCsvToTable(@Input,',') SET NOCOUNT OFF exec usp_SaveCommaSeparatedValue 'aaa,bbb,cccc' Let me know if you need more information If your SQL version is 2005 or 2000 then I will provide different solution Regards, Iftikar Remember to click "Mark as Answer" on the post, if it helps you.

prathyusha · Answer

by using stored procedure or code how can it's possible

Iftikar Hussain · Answer

Hi, There is two way you can do this. First tell me are you trying to insert the data directly from your code or by using Stored Procedure? Regards, Iftikar

prathyusha · Answer

i have only one textbox.in that ihave word like string s="aaa,bc,ccc"; now i want to insert that string in to another table like aaa bc ccc and one more thing that string not contains particular number of parts. it may be 1 or 3 or like. please help me.....

Iftikar Hussain · Answer

Hi, You can do like below protected void BtnSave_Click(object sender, EventArgs e) { var firstName = TxtBxFirstNm.Text; var lastName = TxtBxLastNm.Text; var address = TxtBxDesc.Text.Trim(); try { using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=password")) { if (conn.State == ConnectionState.Closed) conn.Open(); string insertString = @"insert into EmployeesBackUp(FirstName,LastName, Address) values(@FirstName, @LastName, @Address)"; SqlCommand cmd = new SqlCommand(insertString, conn); cmd.Parameters.AddWithValue("@FirstName", firstName); cmd.Parameters.AddWithValue("@LastName", lastName); cmd.Parameters.AddWithValue("@Address", address); cmd.ExecuteNonQuery(); conn.Close(); Response.Write("Data is Saved"); } } catch (Exception ex) { Response.Write(ex.Message); } } Regards, Iftikar Remember to click "Mark as Answer" on the post, if it helps you.

prathyusha · Answer

actually i need to insert one word like aaa,bbb,ccc,wewrwr.after that the word will be inserted into another table. and that word have like aa only.or aaa,ddd then also it will be inseted into that table. that table has only one field keywords

Vulpes · Answer

Check this link for how to INSERT into a SQL database: http://www.w3schools.com/sql/sql_insert.asp If you're doing this from .NET, then the precise code will depend on which database you're using as there are different 'providers' available. This link applies to SQL Server: http://msdn.microsoft.com/en-us/library/ms233812(v=vs.100).aspx

How can i insert the values into sql database

Answers (19)