automatically import csv to MsSQL

Hi is there a way to emulate the MSSQL import wizard with a command prompt so I can shedule an automatic import form a csv file?
Thansk!Look up BCP (bcp in) and bulk insert in BOL. There are several command line parameters that can be used. Sometimes you can get by with filename and table table.

Bill|||If you're using the import wizard, just save it as a DTS package (option available at the end). You can then schedule the DTS package to run whenever you want. Look at www.sqldts.com for information on DTS.|||Thanks! I didn't notice you could save the DTS.

I'll try that, since the file and table will be fixed, I'm sure it will work!

Thansk again.|||Better look closely at the options you select.

Automated Data Export

Here's my process:
1. I have a form where someone enters certain criteria for some data…
2. The form than populates a record in a table with all the criteria…
3. I create a query based on the selected criteria for exporting…

How can I automate the process (maybe using DTS) where SQL server will automatically export the data for me using a stor proc? Create a file, populate the file etc…. or will I need to create an external App to do this?Use a scheduled job.|||A schedule job won't do the entire process. But here's the solution if anyone's interested:

Use a combination of SQL Server agent and the BCP Utilities in SQL Server. The bcp utility copies data between an instance of Microsoft® SQL Server? 2000 and a data file in a user-specified format.

For example
Copying Data From a Query to a Data File:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa –Ppassword

For more info check out the Transact-SQL Help section in SQL Server(under bcp utility)

Auto-increment fields when moving a base from Access to SQL server

Hello all,

I'm a total newbie with SQL Server 2000 and I have a little problem when
moving a database form Access 2000 to SQL Server 2000.
In the Access database, each table has an auto-increment field.
After importing the tables in SQL Server, all the auto-increment fields
are turned into "int" type fields.
Does anybody have an explanation for that mystery?

Thanks in advance,
YanYou might want to change the field type from int to identity. The identity
field type in SQL Server is analogous to the Autonumber field type in
Access.

good luck

CJ

"Yan Roosens" <yan.roosens@.skynet.be> wrote in message
news:3F7C4258.6C6B428E@.skynet.be...
> Hello all,
> I'm a total newbie with SQL Server 2000 and I have a little problem when
> moving a database form Access 2000 to SQL Server 2000.
> In the Access database, each table has an auto-increment field.
> After importing the tables in SQL Server, all the auto-increment fields
> are turned into "int" type fields.
> Does anybody have an explanation for that mystery?
> Thanks in advance,
> Yan|||Hi CJ,

> You might want to change the field type from int to identity. The identity
> field type in SQL Server is analogous to the Autonumber field type in
> Access.

Thank you, the error message related to that problem is no longer displayed, I
will now care about the next one :-(

Yan|||On Thu, 02 Oct 2003 17:20:57 +0200, Yan Roosens
<yan.roosens@.skynet.be> wrote:

>Hello all,
>I'm a total newbie with SQL Server 2000 and I have a little problem when
>moving a database form Access 2000 to SQL Server 2000.
>In the Access database, each table has an auto-increment field.
>After importing the tables in SQL Server, all the auto-increment fields
>are turned into "int" type fields.
>Does anybody have an explanation for that mystery?
>Thanks in advance,
>Yan
Because that isn't a data type. Look up identity for an explanation of
how sql server can provide that sort of functionality.

Auto numbers

How can I make my form to create an autonumber to my table when I insert a new record. Is there any field in the table similar to autonumber field in MSACCESS. Please help!You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks I am going to try it.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks you really help me. Now I find another problem. When I am in my main page I got links for administrator, clients, ect... this links are for entering data, delete and update. If I am in the clients area for example, if I view a record information and then go to anoter record the page does not displays, I got to go to my home page and then go to the page that I was looking and it displays the information. Hope you can help me whis this one.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||well, you have to tell us how you wrote the code to your application,
else we will be firing blank gueses to your question.

Although I do that often :)

What programing language are u using? ASP vbscript? and how do you query your database ? via ADO ?

Things like this will help us help u.

Auto Number mysql

I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.
I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
SimonThis question is most likely better positioned at one of the mysql
newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<S.Dickson@.shos.co.uk> wrote in message
news:1162848855.370242.88140@.h54g2000cwb.googlegroups.com...
>I had an access database that i use as an ordering system. I have a
> form for entering customer details. When i add a new customer on the
> form the customer number is an auto number that appears when i type in
> the details.
>
> I have just moved over to mysql server with access as the front end. I
> have setup the sql tables with the customer number as autonumber.
> When i go into the form and add a new customer it does not generate the
> customer Number automaticaly on the form like it did before. once i
> have entered all the data and saved it i went into the customer table
> to see if the data was saved the auto number was in there .
> Is there any way for on the form for the autonumber to be displayed
> when i start entering the data. Like how it was when i used access as
> the back end before i moved to mysql as the back end
>
> Any help would be great
> Simon
>

Auto Incrementing field

I have a asp.net form which is for orders with a field for numbers in it.
What I want is when a user raises a new order the Order number field will
be populated automatically with the next order number. I want this order
number to be incremented on the database table. The order number is of the
form 05/001 and when the next order is raised it will be 05/002. 05 is the
year.
Anyone any ideas how to do this.
Thanks.
Message posted via http://www.webservertalk.comHi
I'd recommend you to create a table with the following structure
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate DATETIME
)
Now you can easily to extract the order number along its orderdate.
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||here OrderID can me an IDENTITY column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Uri Dimant" wrote:

> Hi
> I'd recommend you to create a table with the following structure
> CREATE TABLE Orders
> (
> OrderID INT NOT NULL PRIMARY KEY,
> OrderDate DATETIME
> )
> Now you can easily to extract the order number along its orderdate.
>
> "macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
> news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>
>|||Hi,
Look into the identity property in books online.
Thanks
Hari
SQL Server MVP
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||Why?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:F05619B5-D96F-405E-B385-466995B09A71@.microsoft.com...
> here OrderID can me an IDENTITY column.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Uri Dimant" wrote:
>
message
it.
will
order
the
the|||Although it has been suggested, an Identity column would not satisfy your
requirement of "YY/SSS".
I would recommend a stored procedure:
PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
The procedure would generate a new Order_Num, insert a row into your Orders
table (and whatever else tables), and return the order number as an output
param.
If the Order_Num needs to be known when an order is first created, run the
procedure first, fill in your field, and set the Order_Status to incomplete.
When the submit the order, change the status.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"macca via webservertalk.com" wrote:

> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||I think this may cause concurency issue.
You can use an IDENTITY column plus another YEAR column (varchar).
whenever you show it to user, you just need to concat these 2 columns.
However, since YY is always current year, you can choose not to store this
value unless this column can be changed in the future.
Hope this may help.
Thanks.
Leo Leong
"Alex Papadimoulis" wrote:
> Although it has been suggested, an Identity column would not satisfy your
> requirement of "YY/SSS".
> I would recommend a stored procedure:
> PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
> The procedure would generate a new Order_Num, insert a row into your Order
s
> table (and whatever else tables), and return the order number as an output
> param.
> If the Order_Num needs to be known when an order is first created, run the
> procedure first, fill in your field, and set the Order_Status to incomplet
e.
> When the submit the order, change the status.
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "macca via webservertalk.com" wrote:
>