How to Copy the Column

[anish99virgo]

I am having two tables in MS SQL SERVER 2005 in master database.
1st table is Country & 2nd is City. 

[B][U]I want to know the command for Copying a Column(with values) for eg say Name from City Table to Country Table. Column is not empty, its having values(data entries).



Hope to see someones reply soon..

Thanks a lot!!
[/U][/B]

[debasisdas]

Have you tried using

insert into .......select from ......

[urtrivedi]

following query is only for MSSQL. Also to update you must have some relation between the two table.

UPDATE tableA
    SET column_in_A = b.ColumninB
    FROM tableB b
    WHERE tableA.keycolumn1 = b.keycolumn1 and tableA.keycolumn2 = b.keycolumn2

[anish99virgo]

@ mr debasis...
well the query you told does not works in this scenario. I have tried it. It only [B]Inserts into a New table not already existing table.[/B]

@ urtrivedi thanks a lot.
 i will try if its solving my problem or not..

[debasisdas]

It only Inserts into a New table not already existing table.

Can you post what exactly you have tried , which did not work for you.

[kplcjl]

All the replies to you have been based on reasonable assumptions based on what you said. Because you aren't clear on what you want, you force people to guess. I'm guessing based on your not liking the responses you have gotten.

I'm going to re-write your question and answer it. You say if the question is close to what you really want.

How do I get a column's values from one table into another table when the other table doesn't have the column defined?

(The difference? You are saying the schema of the other table isn't right. Why I think that's what you mean? Neither of the responses you got were acceptable.)

First off, you can't do this in one step. Use ALTER TABLE to add a column to the other table first. If the table has data already in it either this new column must be nullable or you supply a default value that can be overridden.

(You do specify column names in all of your existing queries, don't you? Otherwise, you probably just broke your existing queries.)

Second, you need to plan how you want the data from the one table put into the second table. You have two ways to do this: insert or update. With insert you need to figure out how to supply all the required columns in the table along with the new column's values. With update you need to figure out how you are going to join the two tables together to get the data from one table into another.

In the case of city and country tables, I would expect a field (column) like CountryID defined in both tables. So join the two tables by matching their CountryID values. Know that I would think the city table could have many records per country and when you put it into country only one of them will be transfered.

It would be better if you grouped the city table by CountryID and picking which value you want transferred.

If you are going to city from country you are going to get a bunch of duplicates from one table to the other.

Now that you have the field filled with values you can alter the table again to make the field NOT NULL, but everything that inserts into the table must now account for the new field or break.

By the way, you are either ignorant or stupid.

You're ignorant if no one has told you it is an extremely bad idea to modify system databases. The alternate is that you knew that and you do it anyway.

[anish99virgo]

@ mr debasis.. Sir, I read & tried this command again, my query is partially solved. thanks!!


@ kplcjl.. thanks a lot for your detailed analysis & help.
firstly.. i mentioned that i need to transfer Coulumn filled with data entries.. that means whole schema + values in that column..
2nd.. How can you presume that I am working on system databases??? better check your guessing strategies.. there are hell lot of csv file available online which can be imported in Sql Server.. i hope you are not ignorant of this thing, coz it would be stupid if you are!!

[debasisdas]

my query is partially solved.

lets see your code and make it fully functional.

[kplcjl]

@ mr debasis.. Sir, I read & tried this command again, my query is partially solved. thanks!!


@ kplcjl.. thanks a lot for your detailed analysis & help.
firstly.. i mentioned that i need to transfer Coulumn filled with data entries.. that means whole schema + values in that column..
2nd.. How can you presume that I am working on system databases??? better check your guessing strategies.. there are hell lot of csv file available online which can be imported in Sql Server.. i hope you are not ignorant of this thing, coz it would be stupid if you are!!

In the original post you said: "I am having two tables in MS SQL SERVER 2005 in master database." I presumed from this that you were using the master database. Since "master" is a system database in SQL Server, I presumed you were modifying the schema of at least one system database. How silly of me to presume that was true!

With rare exceptions columns do not contain schema.(xml comes to mind and binary or text fields that SQL has no knowledge of schema. I don't know the xml type well enough to know if SQL recognizes schema in it.)

I am again left guessing what you mean. (The following example does alter the schema of a system database, but one of the two special system DBs it is OK to alter.)

USE tempdb
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF (OBJECT_ID('dbo.city') IS NULL) 
BEGIN
	exec ('create table dbo.city(name varchar(100),countryid int)')
	exec ('create table dbo.country(countryid int identity,countryname varchar(100),name varchar(max))')
	insert into city values('San Francisco',1),('New York',1),('Venise',2)
	insert into country values ('USA',NULL),('Italy',NULL)
END
select * from city
select * from country
IF (OBJECT_ID('dbo.combineNames') IS NOT NULL) DROP FUNCTION dbo.combineNames
GO
CREATE FUNCTION dbo.combineNames (@id int)
RETURNS varchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(max)=''
	SELECT @ResultVar=@ResultVar+name+ ', ' from city where countryid=@id order by name
	IF (@@ROWCOUNT>0) SET @ResultVar=SUBSTRING(@ResultVar,1,len(@ResultVar)-1)
	RETURN @ResultVar
END
GO
UPDATE country set name=dbo.combineNames(countryid)
select * from country

PS I was surprised by this, in SQL 2000 you would use "len(@ResultVar)-2" to take out the last ", "

[kplcjl]

By the way, if I am ignorant of csv files, it doesn't prove I am stupid. I am ignorant of a lot of things, just like everyone else on the earth. Obstinately remaining ignorant is a sign of stupidity, but that also doesn't prove anyone is stupid. Being told about a best practice and forgetting about it is regrettable, but isn't stupid. Being told about and expressely violating a best practice for no valid reason is stupid.

My function has a mistake. name is nullable so the where clause should also have had " AND name IS NOT NULL"