CODEWITHSUNDEEP
sql
mehrab habibi
mehrab habibi

Reputation: 427

How to insert data in a table from another table?

I have a dynamic table which includes some dynamic and constant ([Id] [DecreeGuid]) columns:

It might have a lot of columns but in this case I'll demonstrate table in a small scale of columns that would be like this:

enter image description here

and there is another table that I'm going to get values from it: enter image description here

I wanted to know how can insert [Value] and [decreeGuid] from second table in to dynamic table under condition where column name of dynamic table equal rows of [Title] column of second table

Upvotes: 1

Views: 397

Answers (2)

mehrab habibi
mehrab habibi

Reputation: 427

I figure it out that how to solve my problem and I decided to share my answer. I use PIVOTE to get values in a table like this:

 DECLARE @SQL nvarchar(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    --Get distinct values of the PIVOT Column
    SELECT @ColumnName= ISNULL(@ColumnName + ',','')
           + QUOTENAME(Title)
    FROM (SELECT DISTINCT Title FROM Temp2) AS Title
    
    SET @SQL=' SELECT * into Temp3 FROM (SELECT Title,
    Value,
    DecreeGuid, [Type]'
    SET @SQL = @SQL +' from Temp2 ) AS S
    
        PIVOT(MAx(S.Value)
              FOR Title IN (' + @ColumnName +')) AS PVTTable'
    
    EXEC (@SQL)
    
    GO
    
    DECLARE @ColumnNames AS NVARCHAR(MAX)
    DECLARE @SQL1 nvarchar(MAX)
    
    SELECT @ColumnNames= ISNULL(@ColumnNames + ',','')
           + QUOTENAME(name)
    FROM (SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[prs_DecreeBookmarkExtra]') AND name <> N'Id') AS Title
    
    SET @SQL1='INSERT INTO prs_DecreeBookmarkExtra  SELECT '+@ColumnNames+' FROM Temp3 WHERE [TYPE] = 1'
    exec (@SQL1)
    
    GO

Upvotes: 0

Evgeni Enchev
Evgeni Enchev

Reputation: 552

You need INSERT SELECT statement, something like this:

INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;

Edited

Up to your requirements it should be:

INSERT INTO dynamic_table (decree_title, decree_guid) SELECT value, decree_guid FROM customer WHERE condition

Hope this helps

Upvotes: 2

Related Questions