0

I am pretty inexperienced when it comes to SQL, so I apologize if this is a rookie question.

I need to add columns to a table in a database, based on a join from another database, using a common column between the two tables.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eric
  • 3
  • 2
  • http://www.blackwasp.co.uk/SQLSelectInsert.aspx search engines tada –  Aug 08 '16 at 20:34
  • @will good information but this is an update operation though because the second table already has values in another column but still a quick search should get you there. The columns you want to copy will have to be present in your table or you will have to add them first then just do a update with a join between the tables here is some documentation on that http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql – Matt Aug 08 '16 at 20:37
  • @Matt Thank you for your help! Just to clarify, I will have to create a table with all of the columns present, and then run the script to populate it with the information from the source table? – Eric Aug 08 '16 at 20:48
  • It sounds like you have an existing table you want to add the columns to correct? If so all you need to do is alter that table add the columns then update the added columns with then join. here is how you can alter table: http://www.w3schools.com/sql/sql_alter.asp – Matt Aug 08 '16 at 20:51

1 Answers1

2

When you say "add columns to a table in a database," do you mean that you have a table, e.g.

create table Targets (
    TargetID int identity(1,1) not null ,
        constraint pkc_Target primary key clustered ( TargetID ) ,
    TargetField1 varchar(64) not null ,
    ..... )

And you want to add additional columns to that table? If so, you'd do something like

alter table dbo.Targets add TargetNewField1 varchar(64) null

After that, you'd have empty columns in your table, and could then run an update to fill in the blanks, something like:

update dbo.Targets
set dbo.Targets.TargetNewField1 = dbo.Source.SourceField1
from dbo.Targets
inner join dbo.Source
on dbo.Targets.SomeUniqueField = dbo.Source.SomeUniqueField
David T. Macknet
  • 3,112
  • 3
  • 27
  • 36
  • This is very helpful! I have a table with a single column, that will be matched up with another table, which will, in turn, be used to match yet another table. If I am understanding this right, I will add all the appropriate columns to the target table, and then run a couple of the update commands to populate the fields with the required information. – Eric Aug 08 '16 at 21:00
  • Yep - that's how it's done. Yes, you could use a `select ... into` statement, but that would only give you a different table with the result set in it, rather than adding columns to your existing table. – David T. Macknet Aug 08 '16 at 21:01
  • After playing with your suggestion, everything is up and running! Thanks so much for your help. – Eric Aug 08 '16 at 22:06