How would I do this with SQL Server?

I have two tables users and address

users table schema

 user_id
 name
 address_id
 sent

address table schema

 ad开发者_如何学运维dress_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?

---

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

---

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.

---

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.