CODEWITHSUNDEEP
sql-server
Prabhath Withanage
Prabhath Withanage

Reputation: 242

How to insert value into a SQL Server table by getting value and column name from another table

create table T1
(
    Name varchar(50),
    Address varchar(50),
    Tel varchar(50)
);

create table T2
(
    ParamName varchar(50),
    ParamValue Varchar(60),
    TableName varchar(50)
);

insert into T2 values('Name', 'test', 'Member');
insert into T2 values('Address', 'testAdd', 'Member');
insert into T2 values('Tel', 'test', 'Member');

insert into T1(Select distinct ParamName from T2) 
values(select ParamValue from T2 )

I'm looking for a way to do a insertion to T1 table by getting value and column name from T2 table.need to get column name from T2 table and Value to that particular column

Upvotes: 0

Views: 111

Answers (5)

Prabhath Withanage
Prabhath Withanage

Reputation: 242

DECLARE @TableName varchar(50) = 'T1'
DECLARE @ColumnName varchar(MAX)
SELECT @ColumnName= coalesce(@ColumnName + ', ', '') + a.COLUMN_NAME
from (SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    inner join T2 on TableName = TABLE_NAME
    WHERE TABLE_NAME = @TableName and ParamName = COLUMN_NAME) a;
print @ColumnName

declare @ParamName varchar(MAX)
SELECT @ParamName = coalesce(@ParamName + ', ', '') + ''''+a.ParamValue+''''
from (SELECT COLUMN_NAME,ParamValue
    FROM INFORMATION_SCHEMA.COLUMNS 
    inner join T2 on TableName = TABLE_NAME
    WHERE TABLE_NAME = @TableName and ParamName = COLUMN_NAME) a;
print @ParamName

declare @QUERY nvarchar(MAX);
SET @QUERY = 'INSERT INTO T1 ('+@ColumnName+')
          VALUES ('+@ParamName+')'

EXEC sp_executesql @QUERY

this is the answer what i expect ,thanks all for your help

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

You could try using a pivot query on T2 to get the names, addresses, and telephone numbers onto a single row for each table name. Then, just do an INSERT INTO ... SELECT as you were, except use the pivoted result.

INSERT INTO T1 (Name, Address, Tel)
SELECT
    MAX(CASE WHEN ParamName = 'Name'    THEN ParamValue END) AS Name,
    MAX(CASE WHEN ParamName = 'Address' THEN ParamValue END) AS Address,
    MAX(CASE WHEN ParamName = 'Tel'     THEN ParamValue END) AS Tel
FROM T2
GROUP BY TableName
-- WHERE TableName IN ('Member', ...)

You can uncomment the WHERE clause if you want to restrict the insert to certain table names.

As a general comment, if you are doing this to get your data into a more normalized form, then I think it is good. But if you plan on storing your data like this long term, you might want to reconsider your table design.

Upvotes: 2

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

I think you are looking like..

INSERT INTO T1(Name) 
SELECT DISTINCT ParamName FROM T2

Upvotes: 0

HEGDE
HEGDE

Reputation: 511

You can use this:

INSERT INTO T2(Column1,Column2)
SELECT Column1, Column2 FROM T1 
GROUP BY Column1;

Upvotes: 2

Adwaenyth
Adwaenyth

Reputation: 2110

Your table structure doesn't make much sense. There is no obvious logical relation between the tables. Is the second one just a buffer table?

In any case however, if you want to insert values from a table into another table you do it like that:

INSERT INTO [target_table] ([target_column_1], [target_column_2], ..., [target_column_n])
    SELECT [source_column_1], [source_column_2], ..., [source_column_n]
        FROM [source_table] 
        WHERE [conditon]

Upvotes: 1

Related Questions