June 2, 2003 at 11:27 am
Can someone tell me what the SQL script is to copy data from a table column called Spanish to another SQL server database with the same table name into Spanish.
So db1.mytext.Spanish into db2.mytext.Spanish
Thanks,
Peter
June 2, 2003 at 11:42 am
Ok, I am new to SQL Server and DBs and I tried the following
INSERT INTO db2..mytext (Spanish)
SELECT Spanish FROM db1..mytext
However, I get the following error message:
Cannot insert the value NULL into column 'ID', table 'db2.dbo.mytext'; column does not allow nulls. INSERT fails.
The statement has been terminated.
ID is the primary key and is filled with numbers. Any ideas?
June 2, 2003 at 1:46 pm
Have you verified on the destination table that you have the identity flag is set to "Yes"? If you have the PK column set up as a numeric with the identity on, then it should autopopulate for you. The other thing to consider is this: if your source table has the PK autopopulating, and you want to use thlose same PK values on the destination table, then you'll need to have the identity flag on the destination table set off. That way, you can write the PK value from the source table to the destination table.
Did I actually answer the question you asked?
June 2, 2003 at 1:50 pm
The ID column is not an autopopulate, it is set with ascending static nmbers like, 1,2,3...
I think the error is that insert trys to append the data, instead of put it into the empty column. do I maybe need an UPDATE statement or something else, and how would I use that?
June 2, 2003 at 1:59 pm
What generates the hard-coded numbers? Also, give me a little more info please on why you think the numbers have to be updated... I thought you were inserting...
June 2, 2003 at 2:45 pm
Also, just reading the message - your Spanish column has NULL values, but your column in db2 does not allow NULLs.
Is that true? If it is, if you set a default of empty instead of allowing NULLs then it should work.
Patrick
Quand on parle du loup, on en voit la queue
June 2, 2003 at 2:49 pm
Ah, sorry, the error is on the ID column, not the Spanish column.
What eljeffo said should be true.
Patrick
Quand on parle du loup, on en voit la queue
June 2, 2003 at 3:17 pm
the hard-coded numbers were inserted by me as I added items in the columns. The Spanish column does allow nulls, but the primary key 'ID' certainly does not. The problem is that the insert statement added the values and there werent't any hardcoded 'IDs' for those values. However I am trying to get those values into the existing fields for the Spanish column where numbers already exist and the Spanish field is empty. Is there some kind of loop from 1 to ... with an update statement or sth I can do?
June 2, 2003 at 3:23 pm
This may be simplistic and I may be missing the original concept of what we are talking about, but I think you can update the nulls with <BR><BR> SET <this column> = MAX(<this column>)+1, ... etc<br><br> that would store the next available value to the column for you (just make VERY sure you get the WHERE clause right or you get extremenely wierd numbering)<br><br>still on the right track?
June 3, 2003 at 5:47 am
update db2.dbo.mytext
set spanish = db1.dbo.mytext.spanish
from db1.dbo.mytext inner join db2.dbo.mytext
on db1.dbo.mytext.id = db2.dbo.mytext.id
June 3, 2003 at 7:52 pm
Is the ID field an IDENTITY field?? If it is you can SET IDENTITY_INSERT ON
Then you can insert your identity from table a into table b.
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply