I'm trying to find examples of sqlcmd script files that will run a select statement, and return those values internal to the script and place them in a variable. I then want to iterate over those returned values, run some if statements on those returned values, and then run some sql insert statements. I'm using Sql Server Managment Studio, so I thought I could run some scripts in the sqlcmd mode of the Query Editor. Maybe there's a better way to do it, but that seemed like a good solution.
I've looked on the Microsoft website for sqlcmd and T-SQL examples that might help. I've also done general searches of the web, but all the examples that come up are too simplistic, and weren't helpful. Any help would be appreciated.
Here is how I understand your starting position:
create table #data
(
id int,
column1 varchar(100),
column2 varchar(100),
newcolumn int
)
create table #lookup
(
id int,
column1 varchar(100),
column2 varchar(100)
)
insert into #data
values
(1, 'black', 'duck', NULL),
(2, 'white', 'panda', NULL),
(3, 'yellow', 'dog', NULL),
(4, 'orange', 'cat', NULL),
(5, 'blue', 'lemur', NULL)
insert into #lookup
values
(1, 'white', 'panda'),
(2, 'orange', 'cat'),
(3, 'black', 'duck'),
(4, 'blue', 'lemur'),
(5, 'yellow', 'dog')
select * from #data
select * from #lookup
Output:
select * from #data
/------------------------------------\
| id | column1 | column2 | newcolumn |
|----|---------|---------|-----------|
| 1 | black | duck | NULL |
| 2 | white | panda | NULL |
| 3 | yellow | dog | NULL |
| 4 | orange | cat | NULL |
| 5 | blue | lemur | NULL |
\------------------------------------/
select * from #lookup
/------------------------\
| id | column1 | column2 |
|----|---------|---------|
| 1 | white | panda |
| 2 | orange | cat |
| 3 | black | duck |
| 4 | blue | lemur |
| 5 | yellow | dog |
\------------------------/
From this starting point, you can achieve what you are asking for as follows:
update d set d.newcolumn = l.id
from #data d
left join #lookup l on d.column1 = l.column1 and d.column2 = l.column2
alter table #data
drop column column1, column2
This will leave the tables in the desired state, with the varchar values moved out into the lookup table:
select * from #data
/----------------\
| id | newcolumn |
|----|-----------|
| 1 | 3 |
| 2 | 1 |
| 3 | 5 |
| 4 | 2 |
| 5 | 4 |
\----------------/
select * from #lookup
/------------------------\
| id | column1 | column2 |
|----|---------|---------|
| 1 | white | panda |
| 2 | orange | cat |
| 3 | black | duck |
| 4 | blue | lemur |
| 5 | yellow | dog |
\------------------------/