You must Sign In to post a response.
  • Category: SQL Server

    Column Name and Column value needs to be inserted in another table

    Hi,
    I have table which contains N no of columns. I have another table which contains ID(Identity column), DataColumnName,DataColumnValue. I need to insert all the column name and corresponding value in the second table.

    For Example
    EmpTable

    EmpId EmployeeName EmployeeSalary
    1000 David 10000
    1001 John 15000

    Expected Result

    ID EmpID DataColumnName DataColumnValue
    1 1000 EmployeeName David
    2 1000 EmployeeSalary 10000
    3 1001 EmployeeName John
    4 1001 EmployeeSalary 15000

    Thanks in Adavance.
  • #756939
    I think there are no way to directly insert your desired result set using single query in sql server. So I suggest you should use loop for no of count row in your table and within loop get the row value of your source table and you can insert record into destination table as your desired result set.
    Regards & thanks
    Arvind kumar
    Visit--blog.akumars.esoftera.in

  • #756942
    use this below script to get your expected output

    DECLARE @EmpId VARCHAR(50),
    @EmployeeName VARCHAR(50),
    @EmployeeSalary VARCHAR(50)
    DECLARE @Temp TABLE
    (
    empid INT,
    datacolumnname VARCHAR(50),
    datacolumnvalue VARCHAR(50)
    )
    DECLARE cursorname CURSOR local scroll static FOR
    SELECT empid
    FROM emptest

    OPEN cursorname -- open the cursor

    FETCH next FROM cursorname INTO @EmpId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH next FROM cursorname INTO @EmpId

    SELECT @EmployeeName = employeename
    FROM emptest
    WHERE empid = @EmpId

    INSERT INTO @Temp
    VALUES (@EmpId,
    'EmployeeName',
    @EmployeeName)

    SELECT @EmployeeSalary = employeesalary
    FROM emptest
    WHERE empid = @EmpId

    INSERT INTO @Temp
    VALUES (@EmpId,
    'EmployeeSalary',
    @EmployeeSalary)
    END

    CLOSE cursorname

    DEALLOCATE cursorname

    SELECT *
    FROM @Temp

  • #757079
    Here is the solution
    DECLARE @Columns NVARCHAR(MAX)='', @Query NVARCHAR(MAX)=''
    DECLARE @CastColumns NVARCHAR(MAX)=''
    Declare @TableName Varchar(50)
    SET @TableName='TableName'

    SELECT @Columns = @Columns + QUOTENAME(COLUMN_NAME) + ',',
    @CastColumns = @CastColumns+CASE WHEN data_type <> 'Varchar' THEN
    'CAST('+QUOTENAME(COLUMN_NAME)+' AS Varchar(1000)) AS '+QUOTENAME(COLUMN_NAME) ELSE
    QUOTENAME(COLUMN_NAME) END+','
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    ORDER BY ORDINAL_POSITION

    SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
    SET @CastColumns = LEFT(@CastColumns,LEN(@CastColumns)-1)

    SET @Query = '
    SELECT ColumnName, Value
    FROM (SELECT '+@CastColumns+' FROM '+@TableName+') AS P
    UNPIVOT(value FOR ColumnName IN ('+@Columns+')) AS UC
    JOIN (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''TableName'') CO
    ON ColumnName = CO.COLUMN_NAME And substring(ColumnName,LEN(ColumnName)-1,2)=''_A'''

    Print @Query
    EXEC sp_executesql @Query


  • Sign In to post your comments