0

I have this excel table(it's just for the example): enter image description here

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

M.R
  • 59
  • 1
  • 9
  • What DBMS are you using ? Please add the relevant tags to your question. It would also help if you posted the table structure. Perhaps explain how you are pulling the data from Excel and if you are using a scripting language like VB Python or whatever. – Kate Mar 18 '20 at 14:23
  • Use color_id instead of number for column name. It will save you a lot of pain due to number potentially being a SQL reserved word – Radagast Mar 18 '20 at 14:29
  • Ok. but, how can i import data from excel and insert to table in SQL (SSMS) and Update the number according to the color? – M.R Mar 18 '20 at 14:38
  • My background is mostly Oracle, and not SQL Server, but that syntax looks like a weird combination of an INSERT and and UPDATE statement. Maybe it's just me and my lack of knowledge about SQL Server, but that looks really wrong.... – Frank Ball Mar 18 '20 at 15:37

3 Answers3

1

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

Radagast
  • 5,102
  • 3
  • 12
  • 27
0

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.

RBJ
  • 128
  • 6
0

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

M.R
  • 59
  • 1
  • 9