I have this excel table(it's just for the example):
and i have in the SQL table just with the colors. i want to insert new column with the numbers like:
insert into table colors
set number = 'c'
where color = 'RED'
but i have 1500 records to add and i can't do it like this.. how can i do it?
thanks
First, you need to a add column in your SQL table (let's call it sql_table). I am assuming the colors in your excel table are a subset of the colors in your sql_table.
alter table sql_table
add color_id varchar(100) --change datatype/length as desired
Then you could bulk upload that file into SSMS as a new table (during upload/import make sure to set datatypes to be the same as your sql_table (let's call this new table excel_table)
Finally, update your sql_table by joining on to your excel_table. I am assuming the sql_table has 1 row per color.
update sql_table a
join excel_table b on a.color = b.color
set a.color_id = b.color_id;
If you wish, you can drop that excel_table since your sql_table is updated
You shouldn't need to know the database to write an SQL insert OR update. SQL is universal. Only flavors like MySql and T-SQL include language components foreign to SQL.
https://www.mysqltutorial.org/mysql-insert-statement.aspx
Updates
Only saw your actual question after reading the comments. First, create an Excel column which concatenates the update script.
=concatenate("update colors set number = '", B1, "' where color = '", A1, "'")
Creates
First, create an Excel column which concatenates the values into the format:
('color', 'number'),
=concatenate("('", A1, "', '", B1, "'),")
Be aware with SQL Server, you can only insert 1000 rows at a time.
insert into colors values
(paste rows here)
(remove comma from last line)
You only need to specify the table structure in the query if your input data is not in the same form as the table, which you can control.
what i did was so easy, i just copy the data from the excel to SSMS: 1. right click--> edit 200 top rows. and i past the data.
the i did what u write:
update sql_table a
join excel_table b
on a.color = b.color
thanks to you all