And another issue - SQL Server Forums

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

And another issue

Author

Topic

asiddle
Starting Member

35 Posts

Posted - 2006-08-03 : 07:17:20

So I now have my incproducts db with the correct categories. How do I copy all the information to the the products db? Or in this case 1 columns contents in 1 db to the another db column!

I have tried:

INSERT INTO products.pID select * from incproducts.impartno

and get

Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'pID'.
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'impartno'.

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-03 : 07:21:00

INSERT INTO DB_NAME.products.pID
select * from DB_NAME.incproducts.impartno

Bythe way, You're trying to fill one column (DB_NAME.products.pID) and you are selecting multiple columns (*)???

asiddle
Starting Member

35 Posts

Posted - 2006-08-03 : 07:45:01

Ok so what should I be putting then as opposed to the *? I want all information from impartno column to pID column/

asiddle
Starting Member

35 Posts

Posted - 2006-08-03 : 07:52:07

The results are the same

INSERT INTO fnsstore.products.pID
select * from fnsstore.products.impartno

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'pID'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'impartno'.

CREATE TABLE [dbo].[products] (
[pID] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,

So its definitely there

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-03 : 08:04:52

How many columns are there in
'select * from fnsstore.products.impartno' ???

Okay, just for my point of view:
fnsstore is a database??
products is a table??
pID is a column??
impartNo is a column??

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-03 : 08:06:37

DEFAULT LAYOUT:

INSERT INTO database.table1
SELECT database.table2.columns FROM other database.table2

database.table2.columns should be identical to the columns of the destination table (database.table1).

Kristen
Test

22859 Posts

Posted - 2006-08-03 : 08:19:08

Much better to specify a column list for the target too - very dangerous to rely on the order of the columns, or use the "*" all columns method:


INSERT INTO MyTargetTable
SELECT *
FROM MySourceTable

a) requires all columns for both tables to be in the same order
b) will fail if there is are IDENTITY, computed, etc. columns on the Target Table
c) Will fail if an additional column is added to Source Table (and not to Target Table) in the future
d) Will fail if the order of the columns, within each table, is changed in the future.
e) SELECT * [generally] performs slower than using a column list

Much better to put in the effort now to do it in a style that is much more immune to database structure changes:


INSERT INTO MyTargetTable
(
   TargetCol1, TargetCol2, ...
)
SELECT SourceCol1, SourceCol2, ...
FROM MySourceTable

Kristen

asiddle
Starting Member

35 Posts

Posted - 2006-08-03 : 08:25:31

Thanks Kristen, that worked a treat.

asiddle
Starting Member

35 Posts

Posted - 2006-08-03 : 13:03:21

Help, this has now stopped working:

use fnsstore
insert into products (pID, pName, pSection, pDescription, pLongDescription, pPrice, pWeight)
select impartno, pTitle, pSection, improddesc1, improddesc2, pCost, weight from incproducts

Error:
Server: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'pID', table 'fnsstore.dbo.products'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Have checked the impartno column and there is not one cell empty. What have I missed now

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-03 : 13:15:41

quote:
Originally posted by asiddle

'fnsstore.dbo.products'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Have checked the impartno column and there is not one cell empty. What have I missed now

asiddle,

impartno has a null somewhere. You can find it by querying:


select * from incproducts where impartno is null

I suspect you looked for it by using "impartno = null" instead of "impartno is null"

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources