In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
Help - I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Please can someone advise me on how I can create a statement that will query each individual row within a table.
As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.
An Overview: We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly. As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.
Please find below the script - its very much in its infancy!!
CREATE PROCEDURE sp_reallocate AS select * from tbl_mobile_reallocate order by allocation_change_date
update tbl_mobile_import set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date
update tbl_mobile_reallocate set status = 'Closed' GO
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created. Table 1 EmpID Text 123456 A1, A2, A3
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
Chris writes "How do I create a copy of a table (not temporary).
In Oracle I do .
Create table newtable as select * from oldtable
This command copies data and column data types.
I tried entering
SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable
in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.
I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil
I'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.
I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...
I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is
Dim ques As String For Each ques In questions query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))" cmd = New SqlCeCommand(query, con) cmd.ExecuteNonQuery()
Next
I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated. Thanks
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
hello I am working with an existing database and there is no Foreign key between 2 tables how can i create a FK after , when the tables are allready full ?
product :
product_id report_id name
report :
report_id dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?
I am very new to SQL Server 2005 so please excuse my mandune queastion.
First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.
My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.
Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!
I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
In sql server 2003 I would create tables with generated sql scripts. This way I could easily create duplicate dev environments.
Now in SQL 2005 Dev edition I try to run my scripts to create tables. I create a db [MSS]. They run successfully but there is no table created.
(example script)USE [MSS] GO /****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[KataNames]( [Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SequenceId] [int] IDENTITY(1,1) NOT NULL, [Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?
I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me????
I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Folks, Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?! Something like alter table add column+@i The alter table statement is within a Cursor and ideally @i should increment each time and alter the table to put in a new column. If I went 3 times thru the loop, I should have column1 column2 column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
