1

I have a SQLite DB that has a table:

table: import_info
   Columns: First_Name, Last_Name, Payroll_ID, ... other columns not used

tabel employees
   Columns: name, employee_num

I want to select the unique names and payroll_id's from the first table into the employees table as name and employee_num.

If I select the table in SQLite Manager and run:

SELECT DISTINCT Last_Name || ' ' || First_Name AS name, Payroll_ID 
AS employee_num 
FROM import_info

I get the results perfectly. But if I try:

INSERT INTO employees (name, employee_num)
SELECT DISTINCT Last_Name || ' ' || First_Name AS name, Payroll_ID 
AS employee_num 
FROM import_info

I get nothing. No error, nothing happens.

Beartech
  • 6,173
  • 1
  • 18
  • 41
  • Did you then try `select * from employees`? The `insert` doesn't really return anything. – Gordon Linoff Feb 02 '15 at 02:31
  • Yes, it returns all the columns in `employees`, with no data since that table is empty. The import_info table has 9,000+ rows, each employee having many entries. Thus the `Distinct`. – Beartech Feb 02 '15 at 02:39
  • I think I might see the problem. The table structure is: `CREATE TABLE "employees" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "employee_num" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL)` so I am not supplying created_at or updated_at. Any idea how to just supply every row with a dummy value for those two columns? – Beartech Feb 02 '15 at 02:42

1 Answers1

0

You can supply values as:

INSERT INTO employees(name, employee_num, created_at, updated_at)
    SELECT DISTINCT Last_Name || ' ' || First_Name AS name, Payroll_ID,
           datetime('now'), datetime('now')
    FROM import_info

I'm surprised, however, that leaving out the values doesn't result in an error. Perhaps something is suppressing the errors.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, that was perfect. I was googling around for the syntax but it was stumping me on how to phrase it so that google would give the answer. If I messed up the syntax it would have no problem throwing an error. Odd that it doesn't throw one in the case I outlined. – Beartech Feb 02 '15 at 02:54