March 4, 2010 at 9:16 am
Hi, I have a table that I created a new column in and I'd like to insert the values into this new column from the same table in another database. I tried this insert statement:
Insert into [databasename1].[dbo].[tablename] (columnname)
select columnname from [databasename2].[dbo].[tablename]
But this gives 'Cannot insert the value NULL into column columnName' since my columns are not null. Then I tried to do an update an update statement:
Update [databasename1].[dbo].[tablename]
SET [databasename1].[dbo].[tablename].[columnname] = [databasename2].[dbo].[tablename].[columnname]
But then I get 'The multi-part identifier "databasename2.dbo.tablename.columnname" could not be bound'. I know I could truncate the table and copy the table from the database2, but there's a few foreign keys attached to the table, so that would be a headache. Any suggestions would be appreciated. Thanks!
March 4, 2010 at 9:26 am
Your syntax is a bit wrong. Try this instead.
Update [databasename1].[dbo].[tablename]
SET [columnname] = B.[columnname]
FROM [databasename1].[dbo].[tablename].[columnname] AS A
INNER JOIN [databasename2].[dbo].[tablename].[columnname] AS B
ON A.PrimaryKey = B.PrimaryKey
-- Gianluca Sartori
March 4, 2010 at 12:29 pm
Thanks a lot for your speedy reply! this saves me a lot of time. The only thing I changed was to remove the columnname after the tablename in the from and inner join statement :
Update [databasename1].[dbo].[tablename]
SET [columnname] = B.[columnname]
FROM [databasename1].[dbo].[tablename] AS A
INNER JOIN [databasename2].[dbo].[tablename] AS B
ON A.PrimaryKey = B.PrimaryKey
Thanks again!
March 5, 2010 at 12:56 am
Dang, you're right! Damn cut 'n paste!
Glad I could help.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply