Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Insert Multiple Records into a single field on another table

    Hi guys,

    I have a table JOBCODE which contains a list of codes.

    I want to insert these values into table VIEWS as a list separated by spaces.

    EG

    Table Jobcodes looks like this

    code
    1
    2
    3
    4
    5
    6

    and I want table Views to look like this


    field1
    1 2 3 4 5 6

    How do I go about this?

    Thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what do you mean by table views?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll start with the caveat that this is a VERY BAD idea, but you can do it using:
    Code:
    CREATE TABLE #JOBCODE (
    code 		CHAR(1)
       )
    
    INSERT INTO #JOBCODE (code)
       SELECT       1
       UNION SELECT 2
       UNION SELECT 3
       UNION SELECT 4
       UNION SELECT 5
       UNION SELECT 6
    
    CREATE TABLE #VIEWS (
       [view]		NVARCHAR(99)
       )
    
    INSERT INTO #VIEWS ([view])
       SELECT RTRIM((SELECT code + ' ' AS [text()]
             FROM #JOBCODE
             FOR  XML PATH('')
             ))
    SELECT *
       FROM #VIEWS
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2011
    Posts
    44
    Thanks guys,

    Brett - Views just happens to be the name of the table I am working with.

    Pat This worked perfectly. I am integrating with a 3rd part software and this is how it requires the information, odd and all as it seems. The field view is of type text - so i guess this will grow to what ever width is inputted?

    Thanks for your help,

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let me re-stress that storing the list in a column is a VERY BAD idea.

    TEXT objects (columns) have a 2 GB limit, if you call that a limit!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •