i would like to have my data rearranged to look like the following:
timestamp :var1 value :var2 value :var3 value
________________________________________________
11:00 x y z
12:00 a b c
....
i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes]( [VResID] [uniqueidentifier] NOT NULL, [Mes] [varchar](max) NOT NULL, [PID] [uniqueidentifier] NOT NULL, [Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
I normalized the below tables but I am finding it difficult to copy data to the new tables. Â How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it. CREATE TABLE SKU_DATA ( SKU     Integer NOT NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer).  SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer) Â BUYER(Buyer,Department)
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?Â
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
Hi I'm trying to Create a new Table from existing table in Q/Analyzer. I figured it would be something like this: CREATE TABLE newTable AS(SELECT * FROM OldTable); but i keep getting Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'AS'. also.. is there another method of doing this, something like INSERT INTO newTable(SELECT * FROM OldTable); and it creates the table ( newTable ) for u if it doenst already exist?? Cheers!!! im using sql2000
Hi All I want to create new table which is going to be a copy of one existing table. I need to copy the existing data into the new table too. How do I do that?
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B"Â .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
We are in the early stages of developing a small project and I have a question I would like an opinion on. When deciding how to design a table to store information on users who will be different types. The project involves setting up webpage that allow " appointment setters" to book demos for our Sales reps. We want to keep track of how many each appointment setter books. I have a user table with user_id, fname,lname, type. Where type represents the type of users they will be either "appointment setter" or "Sales Rep". In the Appointment table I would like to store the Appoinment setter ID, sales rep Id, date/time appointment was booked and some other data on the appointment. My question is how do I set up the relationship from the appointment table back to the user table. Since each record in the appointment table will have a App Setter ID and a Sales Rep ID. Should I just pick either the app setter id or the sales rep id as the foreign key back to the userID in the user table. Or is there another method how to handle this. Any thoughts or articles would be greatly appreciated. thanks,
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  :  Nestle milk | Rainbow milk packages  : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos  | CTN=8 Nos
I want to move an existing table from another. Example B.mdf dbo.xx to C.mdf dbo.xx B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx C.mdf dbo.xx is empty - All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx I was telling I could do a cut and paste to copy the dbo.xx Thank you in advance. ~ Peaches ~
Hi, I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
I have created two tables in phpMyAdmin 3.5.8.1. comments which stores comments users post on a website and registration which stores a users registration details (username, password, e-mail etc, etc).
I want to add the username field from the registration table as a foreign key to the comments table. How do I do this in phpMyAdmin?
I just started with SQL and SQL Server 2005. I cantfind in my books how to add a column to an existing table. It should be a primary key, auto_increment column.
i have table fff .it has two fields one is fno int , another is fnamevarchar(20)ffffno fname-------- -----------100 suresh102 rameshhere there is no not null constraint and identity column theni am add primary key constraint fno column pls help me
Hi all,I have an existing table(Several actually) that I want to add a columnor two or three. I need to have, I think, at least two columns. Onefor Created date and believe this is a no brainer in that I just addthe column and set the default to getdate(). Two, for tracking amodified date of the row of data. I want to keep it simple. What do Ido to track the modify date of a row in SQL Server 2005? Anytime anydata changes on a row update the update column? Is a trigger the onlyway to accomplish this task?Another column could be used to track who changed it. Is there a neatway to determine username, machine name, MAC address or any othermethod? I pretty wide open on this one as I am not sure what I want touse here.TIA!!!
I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstallit. When I did, my database(DEV_DATA) remained intact, but when I go inthru Enterprise Manager, it is not located in the system tree, and istherefore inaccessible. Can anyone suggest how I could get this databaseincluded.
How do you alter the table to use the new partition (I know ALTER TABLE is in there but BOL doesn't give a valid example with the move option)? I can create the partition but I want to apply it to an existing table with no partition? Thanks
I need sql script that can generate 'Create Table' definition including primary and foreign constraints for the table already existing in SQL server 2000.
Please do not refer me to the utilities or commercial software, I need only the sql script.
I am currently implementing RDA Pulls and Pushes. Both worked fine for me, except when I try to pull a certain table twice. I read that in order to pull the table a second time I must drop it on the client.
My original approach was to use a select statement with a where criterium in the pull statement (e.g. SELECT * from tblPhonebook where Pulled = 0), then set Pulled to 1 and pull again later.
My understanding of RDA was that I use the where criterium to filter the data at the server side and simply append that data at the client side.
Can I simply append data in some way or do I really have to drop the table on the client side every time I pull?
I have a table for blog comments I want to add a column that counts the number of comments for each article. existing table looks like this: CommentID ArticleID (FK) commentAuthor authorEmail comment commentDate (getDate()) I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool: "SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)" But can I add that to the table somehow so it does it automagically???
Hi the subject matter pretty much sums up what i need, a piece of sql code that queries 3 or more tables and selects only those records that have a date field that only occurs in one of the tables. So for example if one table has 20 records with a date of 12/12/04 and the other 2 tables do not have any records with a date of 12/12/04 then i only want to select those records with a date of 12/12/04.
I want to add a computed field to an existing SQL table: where Field1 is >0 and field2 is not null set newfield = 'Y' else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Hi, Is it possible to change any fieldname of an existing table?I mean to say by TSQL statement.We know that we can alter the data type and width etc. But I haven't got any info about filedname change.So if it is possible Please help... And Is there any TSQL command to alter multiple columns in a single statement?
How do I insert data into an existing temporary table? Note: I’m primarily a .NET programmer who has to do T-SQL to grab data from time to time.
What I am trying to do is this: 1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence. 2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).
I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I don’t want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.
If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.
-- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
My problem is that #GroupConfidence already exists so in fact I have this code below:
CREATE TABLE #GroupConfidence ([FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SubmitID] [int] NOT NULL, [GL_Score] [decimal](19, 10) NOT NULL, [GP_Score] [decimal](19, 10) NOT NULL, [GPH_Score] [decimal](19, 10) NOT NULL, [DL_Score] [decimal](19, 10) NOT NULL, [MP_Score] [decimal](19, 10) NOT NULL, [Role_MI_Score] [decimal](19, 10) NOT NULL, [Role_ASXRE_Score] [decimal](19, 10) NOT NULL, [Role_APRA_Score] [decimal](19, 10) NOT NULL, [AllCategories] [decimal](19, 10) NOT NULL )
-- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
So I get this error: Server: Msg 2714, Level 16, State 1, Line 109 There is already an object named '#GroupConfidence' in the database.
