tools/sql-to-insert.html at master · teyc/tools · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
<html>
  <style>
     html, body { font-family: Verdana; }
     textarea { width: 100%; height: 300px; margin: 10 0;}
  </style>
<script>
function enquote(s) {
   if (s === 'NULL') return 'NULL';

   // e.g. 007
   var shouldEnquote = (s[0] === '0' && s.length > 1);

   shouldEnquote = shouldEnquote || s == '';

   // e.g. 689-?
   shouldEnquote = shouldEnquote || (s.match(/^.+-.*$/));

  // e.g. 12.0.0.183
   shouldEnquote = shouldEnquote || (s.match(/\..*\./));

   // e.g. 12-01-2018
   shouldEnquote = shouldEnquote || (s.match(/\d+-\d+-\d{2,}/));

   for (var ch of s) {
      if ('abcdefghijklmnopqrstuvwstuvwxyz,:'.indexOf(ch.toLowerCase()) >= 0) {
         shouldEnquote = true;
         break;
      }
   }

   return shouldEnquote ? "'" + s.replace("'", "''") + "'" : s;
}

let noop = (_) => _;
let dropFirstColumn = columns => { columns.shift(); return columns; }

function turnIntoInsert(options) {

  var tableName = document.getElementById("tablename").value;
  var src = document.getElementById("src").value;
  var lines = src.split(/\n/);
  var header = lines.shift();
  var dropFirst = options && options.dropFirstColumn;
  var firstColumnOperation = dropFirst? dropFirstColumn: noop

  var columnNames = firstColumnOperation(header.split(/\t/)).map(_ => _.trim()).join(',');

    var results = [];
  for (line of lines) {
      var values = firstColumnOperation(line.split(/\t/)).map(enquote).join(',');
      results.push(`INSERT INTO ${tableName} (${columnNames}) VALUES (${values});`);
  }

  document.getElementById("dest").value = results.join('\n')
}

</script>

<b>Turns SSMS (Sql Server Management Studio) query results into INSERT statements</b>
  <br />


<p>Run the query, and in the grid view CTRL+A to select all, and CTRL+Shift+C to
  copy everything including column names, and paste into this text area below</p>

  <textarea name="src" id="src" cols="200" rows="20">
  </textarea>
  <br />
  <b>Table name</b> <input type="text" value="tablename" id="tablename" />
  <button  onclick="turnIntoInsert({})">Turn into INSERT</button>
  <button onclick="turnIntoInsert({dropFirstColumn: true})">Drop first column</button>
<br />
<textarea name="dest" id="dest" cols="200" rows="20">
</textarea>
</html>