copying one table into another table (diff DB's!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

copying one table into another table (diff DB's!)

Author

Topic

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 05:03:12

Hi Everyone

I am hoping someone could please point me in the right direction.

I need to transfer data in a table (e.g Table1) from one database (e.g. DBOne) to another table (e.g Table2) in another database (e.g. DBTwo).

I have tried using the SSIS however its throwing a lot of exceptions, most of which I have already sorted, however the SSIS throws collation exceptions. I would really like to know if I could just transfer the data withou having to go back and edit the collations in a MASSIVE database. I understand that there could be conflicts when it comes down to CONSTRAINTS.

Anyway could anyone out there just give me a shove in the right direction?

Thanks a lot and happy coding!

:)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 05:12:10

Try this!

insert into DBtwo.table2 (column1,column2,...)
select column1,column2,... from DBOne.table1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 05:20:04

Hi Senthil

THanx for the prompt reply :)

Howevere I have tried something similiar and yours - and it keeps throwing 'Invalid object name' and I have check the naming of the diffrent col and tables.

Just cant understand why it wont work! :(

Thankful for any advice

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 05:24:11

PS - still doesnt work tho...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 05:26:53

quote:
Originally posted by geoNeo_

PS - still doesnt work tho...

Whats the error message???

Post the query and error message!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 05:28:03

Try this too, But its create new table in db2 database!

select into db2.newtable column1,column2,... from DBOne.table1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 05:34:53

Hi Senthil

The error message on the previous post was 'Invalid object name'. And I did check if the naming of the diffrent col and tables where correct - and they are.

Also I cannot create a new table in DBTwo.

The data in Table1 in DBOne must copy its data to Table2 in DBTwo. I have removed and check contraints that may throw any exceptions, thats why its so puzzeling that it doesnt want to simply copy from Table1 to Table2 - :(

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-31 : 05:38:30

The owner is missing in the three part name (I've used dbo below):
insert into DBtwo.dbo.table2 (column1,column2,...)
select column1,column2,... from DBOne.dbo.table1

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 05:44:12

Hi YellowBUg

Yep i think you just might be right

Will let you guys now shortly!

Fanx :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 05:46:22

Sorry, forget to add owner, try as Yellowbug specified!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 06:00:26

NEW PROB:

Firstly thanks Yellow that seems to sort out one thing, now for a new prob...

Table2 has a few more fields than Table1, mostly just added as this will be a new live DB.

Now the data transfer statement as you specified Yellow seems to work however it throws exceptions:

" Cannot insert the value NULL into column '...', table '...'; column does not allow nulls. INSERT fails. "

I assume thats becuase these extra fields dont allow NULLS - now how does one tranfer from Table1 to Table2 if Table2 has more fields and there is no data at present to fill those fields (?)

Again thanks for the help guys :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 06:08:01

Fill null or some temp data..

insert into db2.dbo.table2(col1,col2,colx,col3...)
select col1,col2,'testdata',col3.... from db1.dbo.table1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

geoNeo_
Starting Member

7 Posts

Posted - 2009-08-31 : 06:11:36

hi senthil

Does the last post query work if Table1 has LESS fields than Table2?

Do I insert the temp data into Table1 from where i am importing that data?

Fanx :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 06:15:41

Just specify the literal values as i did in previous post.

Consider the colx is not in table1,but it is in table2

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

mail2harisha
Starting Member

1 Post

Posted - 2009-09-14 : 06:57:07

Hi all,

I have the same requirement.

But I am getting an error with this sql statement:
insert into DBtwo.schema_name1.table2 (column1,column2,...) select column1,column2,... from DBOne.schema_name2.table1

Error:
SQL0204N "DBOne.schema_name2.table1" is an undefined name. SQLSTATE=42704

Background:
I am using DB2 control center and when I connect to DBtwo with its username and password, I am getting "DBOne.schema_name2.table1" is an undefined name error.
but when I connect to DBOne with its username and password, I am getting "DBtwo.schema_name1.table2" is an undefined name error.

Please suggest how to connect to both database to resolve this error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources