transtar


Hi,

I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.

Thanks,

Kyle




Re: SQL Express Database table data insert into another database

Jens K. Suessmeyer


OK, moving data to another database is quite easy, you can use the three part name for that.

SELECT *
INTO SomeDatabase.SomeOwnerOrSchema.TableName
FROM TableName
--Where condition or other operators here

I am not sure what you mean by tranfering the files from one database to another, perhaps you should explain this is bit more detailed.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: SQL Express Database table data insert into another database

CB77

How do you transfer records from one table to another I think that is what this person is asking and I would like to know as well.




Re: SQL Express Database table data insert into another database

CB77

I found the solution with:

INSERT INTO dbName.ScemaName.TableName (column1, column2....)

SELECT column1, column2...

FROM dbName.ScemaName.TableName





Re: SQL Express Database table data insert into another database

uyen

Can you show me what schemaName is

When I write SQL comment,

insert into newsis.school_info(schoolname,street,ward,district,province)
select title,address,city,areacode,phone
from oldsis.schools

error : schema newsis does not exist

newsis is new database
oldsis is old database

Thanks so much






Re: SQL Express Database table data insert into another database

Francis73

Lets say if I'm using 2 sql2005 express file, how do i specify the databasename etc....




Re: SQL Express Database table data insert into another database

SpaceShot

I just accomplished this on SQL Server 2000. I figure the SQL should be the same.

Given two databases named: SourceDB and TargetDB

SourceDB has a table SourceTable

TargetDB has a table TargetTable

The tables have some compatible columns you want to copy.

try this:

Code Snippet

USE SourceDB -- not needed if already in the source database's context

INSERT INTO TargetDB.dbo.TargetTable (ColumnID, SomeColumn1, SomeColumn2)

SELECT ColumnID, SomeColumn1, SomeColumn2

FROM SourceTable

I figure it might be possible to use SELECT * but if the number of columns don't match, I got an error. It was easy enough to explicitly specify what I wanted.

It's quite possible the schema 'dbo' doesn't work in all circumstances, but I figure if people are asking (like I was), we must be fairly novice and haven't set up complex databases, yet. (Hope this helps).

-Chris Gomez