|
Hi,
I am a beginner in SQL server 2005. I have two databases. In the first database I have a table which I want to copy in the second database (with values and structure).
Please advise how can this be achieved through a sql query.
Thanks in advance,
Ankit Jain
|
|
|
|
|
Presumaby they are in the same server!
Select *<br />
into Database2.dbo.Table1<br />
From Table1
You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.
I beleive that DTS is deprecated in SQL Server 2005. SSIS (SQL Server Intergration Services) is the replacement for DTS.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
Jerry Hammond wrote: I beleive that DTS is deprecated
True, I meant to type BCP.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello All,
I've been trying to connect to a custom database for weeks without success. I was planning to use the custom database to store users login info, role, etc. I ran aspnet_regsql.exe, tried to tweak the web.config file of my webpage, then set up users login etc using Website Administration Tool. Needless to say I was not able to connect to my database. I think that its probably the Configuration block in my Web.config that is causing the problem. The following is what the article that I was using as a guide, said I must do in order to connect to a custom database.
a. provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.
b. add the following to your web.config file:
<configuration>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Data Source=localhost;
Initial Catalog=aspnetdb;
Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
The part in the configuration section above that I don't quite understand is where LocalSQLServer is removed and then added back in, what is that about? Also where it says Data Source=localhost, what if the data source is not localhost, what do I use in its place? Is Integrated Security=True the result of running the aspnet_regsql.exe and choosing Integrated Security while in side of aspnet_regsql? I hope the answers to these questions will help solve my problems. Thank you in advance for your help.
modified on Saturday, August 23, 2008 1:25 AM
modified on Saturday, August 23, 2008 1:30 AM
|
|
|
|
|
AAahhhh - you need to get an understaning of database servers, get hold of a beginner book on SQL Servers please.
I have no idea why it is removing the localserver and then adding it, possibly to insure the destruction of existing connections or registrations.
The rest of it is a connection string try this site[^] it may help you to understand the usage of a connection string. It basically dictates what you connect to and supplies the credentials.
Integrated security is active directories. Depends on how your server is configured, most servers are configured to accept both integrated and sql standard authentication.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I need some advice on how to design for expansion.
I have a typical Employees table. It contains first name, last name, etc. I also need to track whether each employee can perform certain tasks (e.g. bricklayer, cook, brain surgeon). Note that these tasks are not mutually exclusive: they can perform all, none, or some. Also, note that some tasks require additional attributes. For instance, a cook may have a specialty (e.g. Italian, Chinese...).
In addition, I want to be able to add tasks later without upsetting the database (e.g. modify a table) too much. Ideally, this would mean no changes to an existing table.
I could break each task into a table of its own. But, some of these tables would only contain one field, the key from the Employees table. This doesn't seem right.
Another option is to include a field in the Employees table to track the tasks the employee can perform. Perhaps some bit-encoded valued with one bit for each task. But, this has the shortcoming of not allowing additional attributes, like a cook's specialty, without adding a new field (violates my wish of not wanting to change the table later).
Is a table with one field OK? Is it catastrophic to add a field to a table later? Is it bad to keep heaping fields into my Employees table?
Any advice is welcome.
|
|
|
|
|
I'm not an expert, but I've seen systems similar to the one you're describing. For the sake of normalization I wouldn't put more fields into your Employee table. What you need is a mapping table. Since the tasks are not mutually exclusive you can create a table with a field for the employee's id and a field for the task id (assuming each task has it's own id in the tasks table). This way you can have multiple tasks per employee as each combination will just be an entry in this table. The only kicker is the attributes for the tasks. The only advice I can give on this one to have a generic "attributes" field in the Task table which contains the id of a special attribute from an Attributes table. This idea is still a little fuzzy though.
As for tables with one field, I believe those should be reserved for tables used as data sources for comboboxes. At the minimum, all tables should have two fields; id and item.
Does any of this make sense? It's late and I'm tired.
|
|
|
|
|
Thanks for the reply. Your suggestions are similiar to the previous response and I have the same questions.
Actually, you anticipated the problem with the Attributes table, but it applies to the Tasks table as well. That problem is: What is the type of the field that identifies the Task or Attribute? What does your Tasks table look like? What is the field that identifies the task (TaskName?)? I suppose one could use a string, but then I have to know those strings when I do my SELECTs. This doesn't seem right.
This is how/why I arrived at tables for each task: the table itself implied that it only contained employees who perform the given task. This also eliminated the problem of attributes for a given task. But this design yields tables containing only one field and that didn't seem right either.
Thanks again for the reply and any more suggestions are welcome.
|
|
|
|
|
Four tables
Employees
ID
Employee details....
Tasks
ID
Task details....
Task Attributes
ID
TaskID
Attribute details....
LinkEmpTask
ID
EmployeeID
TaskID
Link file is a many to many relationship table. A task can have many attibute, an employee can do many tasks and a task can be done by many employees. This is a basic normalisation excercise and examples can be in any textbook
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK, this has same possibilities, but...
What (type) is the Tasks.TaskDetails field? Put another way: What identifies the task as a bricklayer, cook, or brain surgeon? I suppose a string would be OK...
The same questions apply to the TaskAttributes.AttributeDetails field, though it is more complicated here. For instance, how do I represent a cook's specialty (Italian, Chinese) as well as a brain surgeon's preferred nurse? Also, how do I represent mutliple attributes for a single task? Again, I am wondering about the type of the field. A string can be used, but then you probably end up encoding information in the string (e.g. "Specialty: Italian").
Thanks for the feedback.
|
|
|
|
|
You need to do some basic study on relational data structures, these question would all be answered. Rule 1, your IDs should have NO MEANING except to identify a record. EG where you see an ID field it should be an identity integer field and the primary key index (index is optional). Your tables will have additional fields for descriptions etc.
Your cook structure requires a knowledge of your requirements and goals. This is where you get a professional if it is a commercial project or some training if it is personal. I can make suggestions but there will always be a "what about/if" that someone not involved will not be able to answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to use SQL language to destroy SQL Server 2000?
I just kidding with a friend.
|
|
|
|
|
?!
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
ChinaTJBoy wrote: I just kidding
Huh? Don't litter garbage like this all over the forum.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
But, why would you destroy SQL server 2000?
♫ 99 little bugs in the code,
99 bugs in the code
We fix a bug, compile it again
101 little bugs in the code ♫
|
|
|
|
|
My Virtual memory size is increasing when i am using Insert operation and select operation.
which is leading to hang the system.
It is prompting that the virtual size is low. system will increase the virtual size.
Kindly help to come out of this problem.
|
|
|
|
|
Are you using transactions during this processing ? If so, you need to ensure that you are issuing a commit periodically.
|
|
|
|
|
Hi I am Doing Insert and Select operations.
And the 30 records per second will be inserted.
|
|
|
|
|
that really didnt answer his question, he was asking about logging, if you issue a commit the log should get truncated so it wont just keep growing. however i've only had problems with that makeing a system low on disk space not virtual memory or ram. if you are running SQL Server 2k it wants 2gb of memory and will take all it can find..the 64bit version wants 4GB..and i imagine the newer versions take even more.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
no iam not doing any logging.
|
|
|
|
|
you might not be logging, but did you turn it off for the database on the server? it will automatically log inserts and updates, so that if you or something cancels the query that is bieng run it can roll back any changes. you can disable it but i've never done so and was told that it was a pain, and can cause issues. If you have any indecies on those tables you'll have to rebuild them unless your data is in order when its bieng inserted, either way the indecies will slow down inserts and if they've been corrupted will slow down any table scan, select, update etc..
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Logging, logging, what logging, BBbbrrrrr [fires up the chain saw] wheres the log I'll truncate the bloody log for you....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i would have loved to use a chain saw on our damn dev server the other day..my boss was updating 3 columns based on a 4th column in a table with over 400M rows, and the database was 23gb w/ a logfile 36gb and the array it was sitting on had 8MB left on it, we were wondering why the command wouldnt finish...but thats on SQL Server 2k, hopefully the new ones do it differently. cant wait for the new dev server 2TB of storage.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Hello All,
I have a table with some Columns. I am inserting the records in this table by bulk insert query. The problem is that in the last column the data is inserted by name with FIELDTERMINATOR $. I just need to wirte a query by which I can remove this $ and just have name in that column.
Any suggestions...
Thanks in advance...
|
|
|
|