CODEWITHSUNDEEP
sqlsql-server
Anukool Gandhi
Anukool Gandhi

Reputation: 37

How to create a SQL Insert Statement from Table Results

I am looking for a way to create an SQL statement from a Query Result

Based on the results of my query which contains column names, rowId and Value, I would like to generate a Query which will include the column name and the value to be inserted for that row.

Example:

Id | Row_Id | Column_Name   | Value            | Table
50 | 1      | Employee_Name | 'Joel'           | Employee
51 | 1      | Employee_Age  | '54'             | Employee
52 | 1      | Address       | '425 Baker Ave'  | Employee 
53 | 2      | Employee_Name | 'Jaret'          | Employee
54 | 2      | Employee_Age  | '29'             | Employee
55 | 2      | Address       | '423 Loma Rd'    | Employee
56 | 3      | Employee_Name | 'Jolie'          | Employee
57 | 3      | Employee_Age  | '37'             | Employee
58 | 3      | Address       | '896 Baren Blvd' | Employee
59 | 4      | Location      | 'Chicago'        | Office
60 | 4      | Address       | '264 Taler Ave'  | Office
61 | 5      | Location      | 'Detroit'        | Office
62 | 5      | Address       | '296 Forest Ln'  | Office

Expected Result

INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Joel', '54', '425 Baker Ave')
INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Jaret', '29', '423 Loma Rd')
INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Jolie', '37', '896 Baren Blvd')
INSERT INTO Office (Location, Address) VALUES ('Chicago', '264 Taler Ave')
INSERT INTO Office (Location, Address) VALUES ('Detroit', '296 Forest Ln')

Upvotes: 2

Views: 1258

Answers (4)

Deepshikha
Deepshikha

Reputation: 10284

Assuming that ID column has unique values and Row_Id is unique in sense for only one INSERT INTO statement it will be same, you can write a query as:

select  distinct
         'Insert into ' +[Table] + 
         '(' +
          stuff((
          select ',' + t.[Column_Name]
          from Base t
          where t.Row_Id = t1.Row_Id
          order by t.[id]
          for xml path('')
          ),1,1,'') 
          +') VALUES ('
          +
          stuff((
          select ', ''' + t.[Value] + ''''
          from Base t
          where t.Row_Id = t1.Row_Id
          order by t.[id]
          for xml path('')
          ),1,1,'') 
          + ')'
from Base T1

Test code here: http://sqlfiddle.com/#!18/77430/1

Upvotes: 0

masoud
masoud

Reputation: 478

i like this type of questions :) here my solution (using Dynamic query)

      select *,cast('' as nvarchar(max)) script into #T from YourTable

      declare @Id int,@OldId int =0,@script nvarchar(300),@script2 nvarchar(300)

      update #T
      set @Id = row_Id,
          @script=Case when @Id!=@OldId then 'insert into '+[Table]+' ( '+Column_Name+')' else left(@script,len(@script)-1)+','+Column_Name+')' end,
          @script2=Case when @Id!=@OldId then ' Values('''+[Value]+''')' else left(@script2,len(@script2)-1)+','''+[Value]+''')' end,
          script = @script+@script2,
          @OldId=@Id
      select script from #T t join 
      (select row_Id,max(len(script)) lenScript from #T group by row_Id) X on t.row_Id=x.row_Id and X.lenScript=len(t.script)

      drop table #T

Upvotes: 0

FizzBuzz
FizzBuzz

Reputation: 693

You can do this with some fun window functions. Here's a sample.

(#x serving as table with your structure, row_id, columnName, columnValue, tableName)

WITH foo AS (SELECT Id, Row_Id, columnName, columnValue, tableName, DENSE_RANK() OVER (ORDER BY Row_Id, tableName) AS sectionNumber, ROW_NUMBER() OVER (PARTITION BY Row_Id, tableName ORDER BY Id) AS rowNumberInSection FROM #x) SELECT 'INSERT INTO ' + tableName + ' (' + SUBSTRING(columnNames, 0, LEN(FEE.columnNames) - 1) + ' ) ' + ' VALUES (' + SUBSTRING(columnValues, 0, LEN(FEE.columnValues) - 1) + ' ) ' FROM ( SELECT tableName, ( SELECT columnName + ',' FROM foo f2 WHERE f2.sectionNumber = foo.sectionNumber ORDER BY f2.rowNumberInSection FOR XML PATH('') ) AS columnNames, ( SELECT columnValue + ',' FROM foo f3 WHERE f3.sectionNumber = foo.sectionNumber ORDER BY f3.rowNumberInSection FOR XML PATH('') ) AS columnValues FROM foo WHERE foo.rowNumberInSection = 1 ) FEE;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I wouldn't generate INSERT statements. Simply insert what you want using INSERT . . . SELECT with conditional aggregation:

INSERT INTO TABLENAME (Employee_Name, Employee_Age, Address) 
    SELECT MAX(CASE WHEN name = 'Employee_Name' THEN value end),
           MAX(CASE WHEN name = 'Employee_Age' THEN value end),
           MAX(CASE WHEN name = 'Address' THEN value end)
    FROM <query> q 
    GROUP BY row_id;

Upvotes: 0

Related Questions