Import in table identity column

I am importing data in the table which has primary key as identity.
Data file doesn't has the data for the column.
I created view which has the column except the identity column trying
to import but getting error
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'MyTable; column
does not allow nulls. INSERT fails.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.
Please help.
Thanks
--
FarhanUnless I am missing something, you should be able to import into the table
normally. Don't try to put anything in the Identity column. It will assign
the values automatically. Forget about creating a view to import into.
HTH
Paul
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>|||This works fine.
use northwind
go
create table t (colA int not null identity(1, 1) unique, colB varchar(25))
insert into t (colB) values('a')
insert into t (colB) values('b')
insert into t (colB) values('c')
execute master..xp_cmdshell N'bcp "select colB from northwind.dbo.t"
queryout d:\temp\test.tbl -Smyserver -T -c'
select * from t
delete t
go
create view myview
as
select colB from t
go
bulk insert northwind.dbo.myview
from 'd:\temp\test.tbl'
with
(
fieldterminator = '\t',
rowterminator = '\n'
)
go
select * from t
go
drop view myview
go
drop table t
go
execute master..xp_cmdshell N'del d:\temp\test.tbl'
go
AMB
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>sql

import data with different collation properties

Hi All,
I have to restore a database from a different collation name to my sql
server 2000 sp3.
This database has index, primary keys and so on.
After I restore the database if I try to do a relation I have an error.
Also if I try alter table with a new collation doesn't work because the
objects(index, ...)
How can I fix this? How can I import this database with my default
collation?
Tks
JohnnyYou can use COLLATE clause:
select 1
where
'Alejandro' collate SQL_Latin1_General_CP1_CS_AS = 'ALEJANDRO'
select 1
where
'Alejandro' collate SQL_Latin1_General_CP1_CI_AS = 'ALEJANDRO'
go
or you can dump the data into files, recreate your db with a new collation,
and load the data again (Uhhhh!!!).
AMB
"JFB" wrote:

> Hi All,
> I have to restore a database from a different collation name to my sql
> server 2000 sp3.
> This database has index, primary keys and so on.
> After I restore the database if I try to do a relation I have an error.
> Also if I try alter table with a new collation doesn't work because the
> objects(index, ...)
> How can I fix this? How can I import this database with my default
> collation?
> Tks
> Johnny
>
>|||Tks for you reply Alejandro,
uauuu.... It's not an easy way to change the db collation?
I tought this is an easy problem :)
JFB
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1FF88CBD-9B30-45B1-A637-A1E7ED22D226@.microsoft.com...
> You can use COLLATE clause:
> select 1
> where
> 'Alejandro' collate SQL_Latin1_General_CP1_CS_AS = 'ALEJANDRO'
> select 1
> where
> 'Alejandro' collate SQL_Latin1_General_CP1_CI_AS = 'ALEJANDRO'
> go
> or you can dump the data into files, recreate your db with a new
> collation,
> and load the data again (Uhhhh!!!).
>
> AMB
>
> "JFB" wrote:
>

Import / export question

When importing / exporting tables between databases in SQL 2005, the fields
lose any properties that have been set, i.e. primary key.
Any idea if there is a solution to this.
ThanksHello Chubby,
This behavior is by design. Importing/exporting data only copy/mapping data
from source to destination. It does not copy primary key/trigger etc.
If you want to achieve this, you need to use "Transfer SQL server objects"
in SSIS project. You could check "CopyPrimarykeys" table options in task
properties.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Many thanks
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:ne%231kACOGHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello Chubby,
> This behavior is by design. Importing/exporting data only copy/mapping
> data
> from source to destination. It does not copy primary key/trigger etc.
> If you want to achieve this, you need to use "Transfer SQL server objects"
> in SSIS project. You could check "CopyPrimarykeys" table options in task
> properties.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Welcome!
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.