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.
| Author |
Topic |
|
trackjunkie
Starting Member
31 Posts |
 Posted - 2009-11-05 : 14:09:10
|
| I have an excel spreadsheet and want one of the columns copied into a column on an SQL table. The column already exists in the SQL table but is all zeros. There are the same number of rows in the spreadsheet as in the table, and the data type should be fine. Simply copying the column from excel and pasting into the top cell only gets me the first value.Is there some reasonable way to do this? Any unreasonable way? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-05 : 14:28:46
|
| use an excel expression create an insert statement into a temp table. something like this:="insert #t (PK, col) values("A1&","&A2&")"then copy that formula down so you have a column of generated insert statements.Then in a query window create the temp table #t, paste your insert statements from excel, then update your real table with the temp table values (joining by your PK column)Be One with the OptimizerTG |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-05 : 14:28:53
|
| Is each column in the Excel associated with a specific record in the SQL? If so,you're going to have to take the Excel column and what links it to the SQL table into SQL server, then update your SQL table with the info. You can give the target column and the linking column in Excel a named range, and import that into SQL as a table and then do your update.JimEveryday I learn something that somebody else already knew |
|
|
|
trackjunkie
Starting Member
31 Posts |
Posted - 2009-11-05 : 17:06:13
|
| I think I made life easier and just created a new SQL table with the column I want to insert. Now I am trying the following code (from google...) to copy the column from one table to the other:INSERT INTO item(unit_weight)Select unit_weight From TempWeights"TempWeights" is the table I just created, "item" contains the column I want filled in. The column in question is named unit_weight in both tables. I get this error:Msg 515, Level 16, State 2, Procedure itemInsert, Line 19Cannot insert the value NULL into column 'item', table 'Flex_App_Plt1.dbo.item'; column does not allow nulls. INSERT fails.It seems to be writing into the column item.item, not item.unit_weight.Any idea what is wrong? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-05 : 18:23:45
|
| You either have to alter the table so that the column allows nulls, or enter some other default value,likeINSERT INTO item(unit_weight)Select ISNULL(unit_weight,1.0) From TempWeightsJimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Importing a column from excel