importing to swl server from a flat file PK problem

I am trying to import into sql server from a .csv flat file i made in excel. The problem is the Primary Key. It is set to auto increment.
When i leave an empty column i get:
"Directcopyxform conversion error:destination does not allow null"

when i just omit the column entirely, i get a wrong datatype error because it basically tries to copy all the columns 1 shift to the left

How am i supposed to represent and auto PK in .csv file. ThanksUse a staging table. Where you can load the information to, perform any processing, then insert into your table. Finally cleaning out the staging table at the end.

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
GalWe have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"'" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
Gal
We have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"?" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal

Importing table question

I am part of a group that is developing a SQL database. When I import the ne
w tables from another server, They primary keys for the tables do not import
with the table. Does any one know why this would be happening?Do you mean the primary key column or the primary key definition
(constraint)? Also, what tool (exactly) are you using for the import?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jstokes" <anonymous@.discussions.microsoft.com> wrote in message
news:09B8020D-AF6F-4139-8365-B8AD3A965359@.microsoft.com...
> I am part of a group that is developing a SQL database. When I import the
new tables from another server, They primary keys for the tables do not
import with the table. Does any one know why this would be happening?

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Data With Duplicate Keys

I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!No.
You will have to update the keys in two or your Access databases to ensure that no records from the three databases conflict. And you will likely also have to deal with issues where you have identical records in each database that should be merged rather than duplicated (such as in lookup lists...).
Or, another possible solutions is to load the data from separate databases into separate versions of the same SQL Server table, assigning new GUID identifiers simultaneously. The three tables can then be merged using GUIDs rather than the integer/identity values you probable have right now. Note that you will still need to update foreign keys in related tables to reference the new GUIDs.
These things are all just part of a DBA's day...|||Blah, I knew this wouldn't be fun. Thanks for the info.|||want to show us some DDL and sample data of what the problem is?|||I never said it wouldn't be fun. I just said it is what we DBAs do all day.

Every day.

Day in, day out...

Day after day after day after day...

Hell, it's a blast.|||i have 2 contradictory seanisms on this...

I sometimes tell the bellachers around here

"if you ain't having fun, go do something else for a living"

and when they complain sometimes I tell them...

"if it was easy and fun they would not pay us money to do it"|||I figured out an easy way to do this. Basically I check the ID of the last field in my master database. Before I import data I remove the primary key restriction and run an update query whereby I add that last number to each of the existing ID numbers. This automatically iterates my keys to the new start point I need.|||Fine. Now make sure you update all the foreign key references to the primary keys you just modified...|||I forgot to mention to do that immediately afterward, thanks.|||sounds like a total fubar process

how does a surrogate key have any meaning?|||I admit, it's not the best solution--if I understood what you said. But we're only talking about three tables.

importing data issue

Hello,
We are moving our data from one sql2k to another. The initial db has all
of the tables in a primary filegroup. We have created multiple filegroups
for the new server from scripts. When we try to import the data from the old
sql to the new we get a bunch of FK and PK violations. Is this a way to shut
all of these off for the import and then turn them all back on or do we have
to do each table separately? Thanks in advance.
JohnThe PK violations implies that there is something wrong
with the import. You need to make sure the data added is
correct. Something like a different collation could cause
this.
FK - you just need to import the tables in the correct
order. ANother option is to remove all the FKs and add
them after the import.
Given that you seem to have problems wit hthe data I
would make sure the data is checked when the FKs are
added.