How would I do this with SQL Server?

Question

I have two tables users and address

users table schema

 user_id
 name
 address_id
 sent

address table schema

 address_id
 create_date
 location
 user_id

I have this query that returns 16 rows

select * from users where sent = 1;

but all the address_id are all NULL because they have not been created yet

So what I need to do is create 16 rows in the address table one for each of the users with the user_id of the user and then set the address_id in the users table of that address

For now I can leave the location field blank. All I need to do is set the create_date to CURRENT_DATE

Is there a way to do this in one query?

I don't understand your design. Address_ID in the User table indicates that one user can have only one address. Why is user_id in the address table? Couldn't all of the fields be in the user table? — Mark Sherretta, Sep 14 '11 at 13:55
I didn't create this design....that is what i need to work with. Its the design of sales logic which is our CRM — Matt Elhotiby, Sep 14 '11 at 13:56
if every user has only one or two address' its better to use only one table... there is no need for two separate tables — kasper Taeymans, Sep 14 '11 at 13:56
I don't have an option to change it ...i just need to make the inserts — Matt Elhotiby, Sep 14 '11 at 13:57
Why do you want it to look like a user has an address when they really don't? It will also look like an address was created before it was actually created. — Tom H, Sep 14 '11 at 14:13
Its kind of hard to explain but here goes. The user and address tables are being populated by the application 98% of the time but we now have external leads that are going in the user table which don't have addresses so to avoid the application having issues with an addressed as null I am creating an address Id and the sales reps can update the address information later. — Matt Elhotiby, Sep 14 '11 at 14:21

Trio · Answer 1 · 2011-09-14T14:48:21.173

Not exactly sure what your mean but this should at least point you in the right direction

Begin Try
    Begin Transaction

        Update Users
        Set Users.address = Address.address, create_date = GetDate()
        From Addresses
            Inner Join Users On Addresses.userid = Users.userid

    Commit Transaction
End Try
Begin Catch
    Rollback Transaction
End Catch

It should be something like this. There are a couple of ways of doing the problem so have fun with it and hopefully this helped. For testing it write two Select * From Users statements one before and one after. Also change Commit Transaction to Rollback Transaction so you don't have to worry about making a mistake.

Just reread question yea you can't do that in one shot just replace the Update statement with

Insert Into Addresses (address_id, create_date, location, user_id)
Values ('@ddr355_1d', GetDate(), '1234theLocation', 123478)

and you will have to do that for each one but should be easy with only 16 entries in the User table. You might want to look into writing a Stored Procedure if you plan on adding more to the table. Something kind of like this

Create Procedure [dbo].[AddressesInsertData]
(
    @Address Int,
    @Location varchar(100),
    @UserId Int
)
As
Begin Try
    Begin Transaction

        Insert Into Addresses (address_id, create_date, location, user_id)
        Values (@Address, GetDate(), @Location, @UserId)

    Commit Transaction
End Try
Begin Catch
    Rollback Transaction
End Catch

Basic structure of a stored procedure. I would add an if not exists in there that would update instead of insert but this should be plenty to get you started. Hopefully these examples should clear up somethings for you and help you out.

If it was a bigger table I would limit the `Select * From Users` to `Select Top 1000 * From Users` — Trio, Sep 14 '11 at 14:09
@Alex Oops thought it said pulling addresses from addresses and updating to users i'll update to reflect what it actually says :) — Trio, Sep 14 '11 at 14:12
Is there a way to do this in one transaction with the select users statement around the insert so i can make one transaction — Matt Elhotiby, Sep 14 '11 at 14:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3443/discussion-between-alex-and-trio) — Matt Elhotiby, Sep 14 '11 at 14:24

score 2 · Answer 2 · answered Sep 14 '11 at 14:26

2

Try this:

declare @user table(user_id [int] IDENTITY(1,1), name varchar(25), address_id int, sent int)
declare @address table(address_id [int] IDENTITY(1,1) NOT NULL, create_date datetime default getdate(), location varchar(100), user_id int)
declare @t table(user_id int, address_id int)

insert @user (name) values('you')
insert @user (name) values('someone else')


begin Transaction 

insert @address (user_id) 
output inserted.user_id, inserted.address_id
into @t
select user_id from @user u 
where not exists (select 1 from @address where u.user_id = user_id)

update u 
set u.address_id = t.address_id
from @user u
join @t t
on u.user_id = t.user_id

commit transaction 

select * from @user
select * from @address

answered Sep 14 '11 at 14:26

t-clausen.dk

43,517
12
59
92

can you explain the top three lines...i understand the declare but what is the table(user_id int, address_id int) after – Matt Elhotiby Sep 14 '11 at 14:33
also what are the two inserts after for – Matt Elhotiby Sep 14 '11 at 14:33
The 3 top lines is declaring temporary tables that is like your permanent tables. The 2 inserts are like your 16 rows in the address table, the script can run as it is. It will generate temporary tables that dissapear after running the script. In order to make this work for you, replace my temporary tables with your tables and remove the inserts. You should have the autoincrementation in your tables and you should have the default on the create date like in my example. You can alter the default part on your own tables without affecting anything else. – t-clausen.dk Sep 14 '11 at 14:56

score 0 · Answer 3 · answered Sep 14 '11 at 15:03

There is a design rules of thumb that a table models EITHER an entity/class OR the relationship between entities/classes but not both.

Therefore, I suggest you remove the address_id column from the users table, remove user_id from the address table and create a third table comprising both user_id and address_id to model the relationship between users and their addresses. This will also rid you of the need to have nullable columns.

How would I do this with SQL Server?

3 Answers3