SQL Newie needs help
-
quote:
I feel this is a really good beginners guide: http://www.functionx.com/sqlserverLooks also like a good starting point.
quote:
At one point in the guide it says "Another technique used to perform data entry consists of importing already existing data from another database or from any other recognizable data file. Microsoft SQL Server provides various techniques and means of importing data into Microsoft SQL Server."I saw that a Microsoft Excel document can be used. Does this document have to be in any particular format? Ideally I would love to be able to grab the data from my excel spreadsheet, insert it into my database, and then be able to show this data to users from the web page. Anyone have any experience in this?
Yes Sir, you can import Excel data.
I think the easiest way for this is in EM to right-click on the database to import to, select All Tasks -> Import Data. This invokes the DTS Import/export wizard. Follow this instructions. On the wizard page 'Select source tables and views', I think you can specify which worksheet to import into which table (if it doesn't exist, it will be created). There is also a button 'Transform' where you can specify more advanced options once you got familiar with it. At the end you can save this all as a DTS package for future use, in case you want to do frequently this same task.
Maybe one tip. Try to avoid blank lines in your Excel data! Might save some headache
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Once again thank you Frank.
Do you think it would be possible to get the users to trasport the data from an excel spreadsheet to the database every time a new product price list comes in? Or would it just be easier to create an application that they would have to manually enter new prices into? Thoughts? (I know this isn't really a technical question, its just a personal opinion, so if you don't feel like answering it then don't :))
- Another excel question, what if the spreadsheet has multiple pages (with tabs down the bottom that go between the different pages), and it also has various columns and 'tables', is this going to be a nightmare? Is it even possible with this kind of spreadsheet?
I read this great tutorial on templates at: http://www.databasejournal.com/features/mssql/article.php/10894_1560661_1
but I have a few questions from it. I have searched on google with +scripts +sql +tutorial but can't find any answers to my simple but probably silly newbie questions.
- I can now create templates that can then be saved as .sql scripts? Is this correct?
- Once I have these scripts how can I run them? I know I could run them with QA, but how about with other methods like ASP, or C++? A lot of websites have ASP scripts for use but I couldn't find out how you actually use them. As in, how do I put my scripts that I made through QA into something like ASP, C++, etc.
Thanks.
Edited by - JigMan on 07/15/2003 11:51:57 PM
-
Hi Jigman,
quote:
Do you think it would be possible to get the users to trasport the data from an excel spreadsheet to the database every time a new product price list comes in?possible yes, only a matter of permissions
quote:
Or would it just be easier to create an application that they would have to manually enter new prices into? Thoughts? (I know this isn't really a technical question, its just a personal opinion, so if you don't feel like answering it then don't :))I have no experience with price list, but I would say it depends on how frequently prices have to be updated and how many people are involved in this process. If they are more frequently updated, I would prefer creating a small app doing this in SQL Server. If they are less frequently updated, the Excel transfer should be fine.
But when there are many people involved, I would prefer the separate app solution in either case.
I'm pretty sure, anyone can comment with more experience on this
quote:
- Another excel question, what if the spreadsheet has multiple pages (with tabs down the bottom that go between the different pages), and it also has various columns and 'tables', is this going to be a nightmare? Is it even possible with this kind of spreadsheet?shouldn't be a problem.
You are able to select the worksheets and named ranged you like to process. Might be a problem when the data in Excel is spread over several worksheets and must be imported into one table. Never done this, I always manipulated Excel data before importing to cause no problem.
quote:
I read this great tutorial on templates at: http://www.databasejournal.com/features/mssql/article.php/10894_1560661_1Greg is also on this forum. No one can better comment on this!
quote:
but I have a few questions from it. I have searched on google with +scripts +sql +tutorial but can't find any answers to my simple but probably silly newbie questions.- I can now create templates that can then be saved as .sql scripts? Is this correct?
- Once I have these scripts how can I run them? I know I could run them with QA, but how about with other methods like ASP, or C++? A lot of websites have ASP scripts for use but I couldn't find out how you actually use them. As in, how do I put my scripts that I made through QA into something like ASP, C++, etc.
I guess in most cases, you place your T-SQL commands, eg. your script in a stored procedure and call this from wherever.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the help, I think importing data from excel is going to be a no-go. I think the spreadsheets have too many different headings and what not, and when I try to import them I get alot of NULL values and the information I want is listed halfway down between all the NULLS. It actually works for a couple of rows but because of all of the different headings and descriptions it fills the data up with NULLS where it is expecting a price value. I think it would take the user longer to edit the spreadsheet to convert than using an application I could create to enter the data manually.
I actually want the user to be able to enter data and change the prices, this will then be saved to the database, of which the webpage will get and display the prices in a user friendly format. Confusing? So in the end I don't want to show the Excel sheet, just the data that is initially contained in it that I want put in the database, that will be displayed to the web page.
I think I will create an application in C++ Builder that will allow the entering of the data, this will be saved into the database, when a client views the web page their desired information is grabbed from the database.
-
Hello Jigman,
quote:
I think I will create an application in C++ Builder that will allow the entering of the data, this will be saved into the database, when a client views the web page their desired information is grabbed from the database.really going down C++ road?
While, from my point of view, certainly a great decision, starting to learn C++ while programming a db app, can be really frustrating. Good luck for this!
... and have fun with this
Why C++ is like teenage sex.It is on everyone's mind all the time.
Everyone talks about it all the time.
Everyone thinks everyone else is doing it.
Almost no one is really doing it.
The few who are doing it are:
Doing it poorly.
Sure it will be better next time.
Not practicing it safely.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok, back to SQL Server. Say I have a table populated with around 50 items. The primary key is their ID (eg 1-50). If I get a new item that I want to chuck in the database at say position 25, is it possible to do this without deleting position 25? For example can I insert this new item with ID 25, so the current item with ID 25 now becomes 26, the one after that goes from 26 to 27 and so forth, all the way to the end where the table will now hold 51 items instead of 50. Does this make sense? Is this possible?
-
Hi Jigman,
quote:
Ok, back to SQL Server. Say I have a table populated with around 50 items. The primary key is their ID (eg 1-50). If I get a new item that I want to chuck in the database at say position 25, is it possible to do this without deleting position 25? For example can I insert this new item with ID 25, so the current item with ID 25 now becomes 26, the one after that goes from 26 to 27 and so forth, all the way to the end where the table will now hold 51 items instead of 50. Does this make sense? Is this possible?why do you want to do this?
Of course, you can do this, but unless there is a good reason for this, I think the maintenance cost will outweight the benefit by far.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
So it is possible its just hard to do and time consuming? An SQL insert statement couldnt just chuck it where i want it?
I guess i code write code to increase all the IDs by 1 and insert it at the ID I want.
-
This might not be the best place to post this because it is getting too asp instead of SQL but its worth a try 🙂
When accessing data from a data base I try to test whether a value is less than another to move on to a further step. I have a value license, startEx and endEx.
If I try:
if license > startEx then....
the code works fine, but when I try endEx it doesnt work. eg.
if license < endEx. I have tried just chucking in an integer of the same value of what endEx should:
if license < 6.... and that works. I have printed endEx to the screen and it comes up as a number. I have checked the database and it is stored as an Int. I have no idea whats wrong. Any ideas?
-
Sorry all, please ignore.
After hours I finally figured out that it was the license value that had the problem. I was passing it in a querystring and I dont know how but I must have been passing it as string because it was always greater than my startEx and endEx values. I will post another thread on my querystring problem.
-
Hi Jigsam,
quote:
So it is possible its just hard to do and time consuming? An SQL insert statement couldnt just chuck it where i want it?so you want to maintain some kind of identity. It is possible to do the job on your own or let SQL Server manage this.
Some days ago there was a thread about this, but I can't find it right now.
Of course, can a SQL statement modify the value to what you want, but as you may want this for consecutive numbering or indexing, all numbers should be unique, and that is the problem you have to deal with.
[/quote]
I suggest to take a look at BOL for 'identity columns' as a start.
Maybe it helps when you say what you want this for?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks, I understand that there a no positions in my tables in my database, but when viewing the database in Enterprise manager (when I go 'Open Table, Return all rows'), it would be nice if it showed me my information ordered. For example at the moment it goes, 1,2,3,4,6,7..........20,5. Its not a must, but I would prefer it if it was shown in order with 5 after 4. Is this possible?
-
While working in Enterprise Manager I try to update some fields, and then when i go to close the table i get the message:
'Transaction cannot start while in firehose mode'
What the????
-
To go with the above error, I now have an entirely new problem! yay!
I was inserting data and was stopping every now and then to c if it worked. My last check was at ID 51. After ID 60 I stopped to check again. After I reopend my database I was alarmed because I thought the data wasn't there anymore. What had happened though was it chucked IDs 52-60 up between IDs 14 and 15. E.g 13,14,52,53,...60,15. I have NO IDEA why it did this and at the time I didnt know it had done this. I couldnt see the fields so I assumed they werent stored for some reason. So I added record 52 again, closed the table, opened it again, and couldnt c it, so i thought again it didnt work but once again it moved it higher where I couldnt c it. I repeated again as all newbies do 🙂 Once I figured out what had happened I have 3 records set as 52. Now ID is meant to b the primary key but I dont have it set as it as such. So when I try to delete the 2 of ID 52 fields it tells me 'Key column information is insignificant of incorrect. Too many rows were affected by update.'
Can anyone please help me find a way out of this mess I have gotten myself in? And does anyone know why it moved my records in the first place?
I just tried to set the ID as the primary key and this is the error I got:
- Unable to create index 'PK_KAVPRICEBOOK2'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '52'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Please tell me I don't have to start this table all over again.
Thanks.
Edited by - JigMan on 07/22/2003 8:39:24 PM
-
Hi Jigman,
quote:
Thanks, I understand that there a no positions in my tables in my database, but when viewing the database in Enterprise manager (when I go 'Open Table, Return all rows'), it would be nice if it showed me my information ordered. For example at the moment it goes, 1,2,3,4,6,7..........20,5. Its not a must, but I would prefer it if it was shown in order with 5 after 4. Is this possible?that's one feature of relational dbms. Table data is unordered. However, there are two ways to work around this.
- SELECT your_columns ORDER BY id
- create a clustered index. This will influence the way the data is stored
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply