none
how to insert multiple values into a single field in a table using sql query

    Question

  • i have a table named profile..in that i have a particular column called questions..in this i need to insert multiple questions for a single id..that is for a particular field i need to store multiple questions ny splitting them using a comma delimeter..kinldy share with me the code for this..
    Monday, October 03, 2011 5:56 AM

Answers

  • Are you looking for T-SQL code to insert or update an exising row with the specified question?  If you are using SQL 2008 or above, you can do this with MERGE.  Below is a sample stored procedure:

     

    CREATE TABLE dbo.Foo(
    	Id int NOT NULL
    		CONSTRAINT PK_Foo PRIMARY KEY
    	,Questions varchar(MAX)
    	);
    GO
    
    CREATE PROC dbo.MergeQuestion
    	@ID int
    	,@Question varchar(MAX)
    AS
    MERGE dbo.Foo AS target
    USING (VALUES (@ID, @Question)) AS source(ID, Questions)
    	ON target.ID = source.ID
    WHEN MATCHED THEN
    	UPDATE SET target.Questions = COALESCE(target.Questions + ',' + @Question, @Question)
    WHEN NOT MATCHED BY TARGET THEN 
    	INSERT (ID, Questions) VALUES(@ID, @Question);
    
    RETURN @@ERROR
    GO
    

     

    Note that this design violates normalized database design principals (atomic data, no repeating groups) and introduces other problems, like the inability to store a comma as part of a single question text.  Consider creating a separate table with a single row for each question.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, October 03, 2011 12:31 PM

All replies

  • Hello,

    It is difficult to help you without some more informations.

    - Could you tell us if that table is belonging to a database  ( if yes, which type of database : SQL Server, Access,Oracle,..)  or if it is a DataTable which can belong to a Dataset ?

    - From where are you getting the questions you want to merge in a single field ( or column ) ? From another table, a form ( ASP or Windows Form ) ?

    - Could you also provide the structure of this table ?

    - Could you explain the aim of this table ? Maybe , it would be easier to create a child table linked to a main table thru a primary key in the parent table which is an index in the child table

     We are waiting for your feedback to try to help you more efficiently, but according on your reply, a moderator may move this thread towards your thread towards a better forum ( it is one of the most important task of a moderator to detect threads created in a "bad" forum and to move it in a more suitable forum ).It avoids to have unanswered threads in a forum and the move is quick and concerns all the posts of the thread.It avoids to the moderators to merge multiple threads in a single one, a long and complicated operation which avoids to have different responses on different forums. For example, if it is a question related only to an insert statement in a SQL Server, this thread will be moved towards the T-SQL forum, if for Access , towards an Access forum.

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, October 03, 2011 7:58 AM
  • its a ms sql server..the question have to be inserted manually by writing an insert query.

    i need the insert query for this actually. multiple questions have to be inserted into a single column called questions with each question seperated by a comma delimeter..

    so i will easily split up the comma from that field and show it in UI the questions in order..

     

    thanks in advance..kindly help me out

    Monday, October 03, 2011 11:42 AM
  • Are you looking for T-SQL code to insert or update an exising row with the specified question?  If you are using SQL 2008 or above, you can do this with MERGE.  Below is a sample stored procedure:

     

    CREATE TABLE dbo.Foo(
    	Id int NOT NULL
    		CONSTRAINT PK_Foo PRIMARY KEY
    	,Questions varchar(MAX)
    	);
    GO
    
    CREATE PROC dbo.MergeQuestion
    	@ID int
    	,@Question varchar(MAX)
    AS
    MERGE dbo.Foo AS target
    USING (VALUES (@ID, @Question)) AS source(ID, Questions)
    	ON target.ID = source.ID
    WHEN MATCHED THEN
    	UPDATE SET target.Questions = COALESCE(target.Questions + ',' + @Question, @Question)
    WHEN NOT MATCHED BY TARGET THEN 
    	INSERT (ID, Questions) VALUES(@ID, @Question);
    
    RETURN @@ERROR
    GO
    

     

    Note that this design violates normalized database design principals (atomic data, no repeating groups) and introduces other problems, like the inability to store a comma as part of a single question text.  Consider creating a separate table with a single row for each question.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, October 03, 2011 12:31 PM