Adding new data to the existing tables of a database in SQL Server using table variables

Question

I'm trying to add new rows to the existing tables in the database by using table variables. The problem I'm facing is due to the auto-increment primary key in the database. The tables are like below:

Table 1: State - ID_State(PK)

| state     |ID_State |
|-----------|---------|
| Karnataka | 1       |
| Tamil Nadu| 2       |    
| UP        | 3       |
|    .      | 4       |
|    .      | .       |
|    .      | .       |

Table 2: City - ID_city(PK)

| City      | ID_City | ID_State|
|-----------|---------|---------|
| Chennai   | 1       | 2       |
| Bengaluru | 2       | 1       |
| Lucknow   | 3       | 3       |
| Mysore    | 4       | 1       |
|   .       | .       | .       |
|   .       | .       | .       |

Table 3: Location - ID_Location(PK), ID_City(FK)

| Zip  | ID_Location | ID_City |
|------|-------------|---------|
| 0001 | 1           | 1       |
| 0011 | 2           | 2       |
| 0002 | 3           | 1       |
| 0022 | 4           | 2       |
| 0003 | 5           | 1       |
| 0012 | 6           | 2       |
| .    | .           | .       |
| .    | .           | .       |

On the other hand I've the data in an excel sheet that I want to add into the existing database. This is mainly done to add Zip Codes that were missing previously.

Data in Excel sheet:

| Zip  | City        |      State       |
|------|-------------|------------------|
| 0001 | Chennai     | Tamil Nadu       |
| 0002 | Chennai     | Tamil Nadu       |
| 0003 | Chennai     | Tamil Nadu       |
| 0004 | Chennai     | Tamil Nadu       |
| 0005 | Chennai     | Tamil Nadu       |
| 0011 | Bengaluru   | Karnataka        |
| 0022 | Bengaluru   | Karnataka        |
| 0033 | Bengaluru   | Karnataka        |
| 0044 | Bengaluru   | Karnataka        |
| 1111 | Lucknow     | UP               |
| 2222 | Lucknow     | UP               |
| 3333 | Lucknow     | UP               |
|  .   | .           | .                |
|  .   | .           | .                |

Main motto is to add any Zip Code, Cities that are missing in the original database. I've written the following script in which I've made use of table variables.

DECLARE @temp TABLE (Zip_Code NVARCHAR(100) PRIMARY KEY,
                     City NVARCHAR(100),
                     State NVARCHAR(100)
                    ) 

INSERT INTO @temp (Zip_Code, City, State) 
VALUES ('0001', 'Chennai'), ('0002', 'Chennai')
.
.
.
.

select * 
from @temp
where City not in (select City from City)

select * from @temp
where Zip not in (select Zip from Location)

These queries return the rows that are not in current database. I need to add them, but do not know how to go about it using table variables. Any help would be much appreciated. Thanks!

P.S: I've implemented this using temporary tables and by adding new columns to the existing tables. In order to make less changes to the existing database, trying to implement using table variables.

A table variable can be referenced just like any other table. You say you have done this already using temp tables? Why do you think you need to rebuild it using table variables? — Sean Lange, Mar 27 '18 at 19:32
@SeanLange this is because I do not need any new tables or new log files when I try adding new data. — schikkamksu, Mar 27 '18 at 19:46
That doesn't make sense to me. What does that have to do with using table variable instead of a temp table? Do you realize that table variables can't be indexed, they have no statistics. If there is more than around 1,000 rows the performance starts degrading very quickly. Since you have this working already I don't see the benefit of changing to table variables. — Sean Lange, Mar 28 '18 at 13:17

score 0 · Answer 1 · answered Mar 27 '18 at 20:29

Harder than you might guess, but not too hard.

You will need to figure out zip on your own.

set nocount on;
declare @S table (id int identity primary key, val varchar(20));
insert into @S (val) values 
       ('Karnataka')
     , ('Tamil Nadu');
select * from @S s order by s.val;  
-- table variable does not have declared foreign key relationships
declare @C table (id int identity primary key, val varchar(20), fkState int);
insert into @C (val, fkState) values
       ('Chennai', 1)
     , ('Bengaluru', 1)
     , ('Mysoe', 1)
     , ('Lucknow', 2);
select c.val as city, s.val  as state
from @C c 
join @S s
  on s.id = c.fkState
order by s.val, c.val;
declare @L table (city varchar(20) not null, state varchar(20) not null, primary key (city, state));
insert into @L values
       ('Chennai', 'Karnataka')
     , ('Barsel', 'Karnataka')
     , ('South', 'Penn');
insert into @S 
select distinct l.state
from @L l 
where not exists ( select 1 
                   from @S s 
                   where s.val = l.state 
                 ); 
select * from @S s order by s.val; 
insert into @C (val, fkState) 
select city, s.id 
from @L l 
join @S s 
  on s.val = l.state 
where not exists ( select 1 
                   from @C c 
                   where c.val = l.city 
                   and c.fkState = s.id
                 );
select c.val as city, s.val  as state 
from @C c 
join @S s
  on s.id = c.fkState
order by s.val, c.val;

id          val
----------- --------------------
1           Karnataka
2           Tamil Nadu

city                 state
-------------------- --------------------
Bengaluru            Karnataka
Chennai              Karnataka
Mysoe                Karnataka
Lucknow              Tamil Nadu

id          val
----------- --------------------
1           Karnataka
3           Penn
2           Tamil Nadu

city                 state
-------------------- --------------------
Barsel               Karnataka
Bengaluru            Karnataka
Chennai              Karnataka
Mysoe                Karnataka
South                Penn
Lucknow              Tamil Nadu

Adding new data to the existing tables of a database in SQL Server using table variables

1 Answers1