I'm updating an existing table by adding data into an existing column.
I have already have an output of the data to be inserted, but due to the amount of records, i'm looking for the best way to insert this into my table without having to manually write to each line of sql.
Here's my sql (partial) i want to insert into
INSERT INTO `tbl_user_variables_dobRE` (`user_id`, `value`) VALUES
id | 7
username | guestinvite
password | BLANK
forname | forname
surname | surname
email | email@example.com
address_id | 286
type_id | 4
dob | 0000-00-00
plusGuest | 0
update | 2016-02-16 11:54:36
created | 2016-04-04 17:03:12
You tagged both MySql AND sql-server in your post. The following is assuming you're using SQL Server, but the idea would remain the same in MySQL (just different syntax)...
If I'm understanding correctly, it sounds like you want to do an
UPDATE, not an
INSERT, being that you're modifying existing rows.
You said that you have an output of the data to be inserted - Insert this into a TEMP table and JOIN it to the table you'd like to update where the id's match.
BEGIN TRANSACTION [Transaction1] -- Do large updates as transactions to avoid data loss CREATE TABLE #temp ( -- Create temp table [user_id] int, [dob] nvarchar(20) ) INSERT INTO #temp -- YOUR SELECT GOES HERE SELECT my_id as [user_id], my_dob as [dob] UPDATE my_table SET my_table.dob = t.dob FROM tbl_user_variables_dobRE my_table INNER JOIN #temp t ON t.user_id = my_table.id DROP TABLE #temp
If your data looks good, commit the transaction: (Don't dwell too long, transactions lock table data!)
COMMIT TRANSACTION [Transaction1]
ROLLBACK TRANSACTION [Transaction1]