Tranfer Data in one table to another table.

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

General SQL Server Forums

New to SQL Server Programming

Tranfer Data in one table to another table.

Author

Topic

Michael71
Posting Yak Master

126 Posts

Posted - 2006-12-29 : 14:57:09

This may be simple, I just want to tranfer about 15 columns of data into another table on the same database. The receiving database is empty, but has different column names. I'm guess I have to make sure the data type can hold the information being transfered. I'm using SQl Server 2005. Please help.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 15:37:10

INSERT targettable
SELECT columns
FROM sourcetable

There must be columns in the SELECT to match every column in the target table and they must have compatible (not necessarily the exact same) data types - for example the source column could be int and the target column decimal, but the source column could not be varchar if the target is int. You can cast the type in the source to the correct type in the target, so for example if the source was int, you could cast it to varchar.

The source and target tables can be in the same or different databases.

See CAST, CONVERT, INSERT ... SELECT in Books Online.

Michael71
Posting Yak Master

126 Posts

Posted - 2006-12-29 : 15:41:57

Do the columns have to have the same name? Also, how do I get to books online?

Michael71
Posting Yak Master

126 Posts

Posted - 2006-12-29 : 16:10:03

Does this look right? The Target table has a few more columns of its own. Thanks.

USING Simply Fasions
INSERT INTO aspnet_Membership
SELECT userID,email,password,email_password,lastLoginDate,vendorID,permissions,userType,recID,name,company,agentID,email_lookup,staff,customerID
FROM users);

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 18:19:07

There are a few problems

The statement is USE not USING.
You need to specify a value for every column in the target, even if you specify a literal of NULL or 0 or ''.
Remove the ) after the FROM table.

Books Online is the help for SQL Server, it's on the SQL Server start menu and comes up if you hit F1 in Query Analyzer or Enterprise Manager. Or it is also available online at
http://msdn.microsoft.com/library

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-31 : 23:33:10

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-31 : 23:54:20

You are copying data from another database so you need to qualify the table name.
Run the query in the destination database.
You should name the destination columns as well as the source.
There's no close bracket at the end.

INSERT aspnet_Membership (col1, col2, col3, ....)
SELECT userID,email,password,email_password,lastLoginDate,vendorID,permissions,userType,recID,name,company,agentID,email_lookup,staff,customerID
FROM sourcedatabase..users

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-01 : 11:41:45

So the destination columns and the source columns don't have to be called the same name? If not this should be easy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-01 : 13:31:13

Nope - just have to have compatible datatypes. You can convert in the select if you need to.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-02 : 08:39:42

How can I use DTS to do this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-02 : 08:59:40

Why DTS just to make simple copy between databases? The efforts are not worth it (unless of course, you are doing some processing while copying).
But if you want, you can follow Import and Export Data wizard steps and get your data transferred.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-02 : 09:07:12

Can you tell me why this won't work?

INSERT INTO aspnet_Membership
(UserId, Email,Password )
SELECT userID,email,password
FROM users

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-02 : 09:10:13

quote:
Originally posted by Michael71

Can you tell me why this won't work?

INSERT INTO aspnet_Membership
(UserId, Email,Password )
SELECT userID,email,password
FROM users

What is the error you got?

Madhivanan

Failing to plan is Planning to fail

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-02 : 09:12:44

Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-02 : 09:17:33

quote:
Originally posted by Michael71

Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.

Make sure if the table exists on the current database. If it is in different database then you need to specify as DBname.dbo.Users

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-02 : 09:17:48

Prefix your query with source and destination database names like this:

INSERT INTO DestDB..aspnet_Membership
(UserId, Email,Password )
SELECT userID,email,password 
FROM SourceDB..users

Note: Replace DestDB and SourceDB with respective database names.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-02 : 09:18:28

It's on the same Database.

Michael71
Posting Yak Master

126 Posts

Posted - 2007-01-02 : 09:21:33

INSERT INTO SimplyFashions.aspnet_Membership
(UserId, Email,Password)
SELECT userID,email,password
FROM SimplyFashions.users

Msg 208, Level 16, State 1, Line 1
Invalid object name 'SimplyFashions.users'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-02 : 09:21:50

quote:
Originally posted by Michael71

It's on the same Database.

Then run

sp_help 'users'

and see what you are getting

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-02 : 09:22:41

Then make sure Users table exists in the database as message suggests.
Also check the owner of the table using either sp_Help or INFORMATION_SCHEMA.TABLES.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-02 : 09:23:06

If you meant SimplyFashions is Database name then it should be

SimplyFashions..users

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources