Adding An Autonumber Column After The Fact?
Oct 26, 2005
I have a csv file of quotes. I need to IMPORT the quotes into my SQL Server database. I created the table with an autonumber identity column, but the IMPORT fails because I can't INSERT NULL into my identity column. There is no option to autonumber or anything when I click the TRANSFORM button on the IMPORT wizard. So, I have decided to allow NULLS on the ID column and add the numbering later. I'd rather not have to manually number it, is there a way to do it with a query? I treid just setting it to not allow NULLS and to auto-incrememnt, but that didn't work....
View 3 Replies
ADVERTISEMENT
Apr 23, 2004
I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?
View 4 Replies
View Related
Aug 14, 2001
Hi all,
I'm writing a procedure and have a temporary table. One of the column in the temporary table must have an autonumber. How should I define this column when creating the temporary table!
Thank you
View 4 Replies
View Related
Jul 7, 2014
I need to update a column with AutoNumber which is depending on some other column. The output should be like following..
Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001
I want to Update The column Obj2 based on the values of Obj1..
View 3 Replies
View Related
Mar 6, 2008
Hi All,
I want you an urgent respones, I want to add a column to a Dim_table and Fact_table, the Dim_table is sourcing from different database table and now the new column is going to source from another DB, thus without changing the structure of the table relationship can i add a column? To make it clear Dim_table has got Clomun1 from table1, Clomun2 from table2, Clomun3 From table3, Clomun4 From table4. Now I want Clomun5 From table5. How do i add and let it source from table5.
Thank you for your qiuck response.
View 4 Replies
View Related
May 23, 2008
I have a fact table with one column storing string values. I need to use this column as a measure, but as different measure. Here's a snapshot of the fact ( with 4 columns)
#1 #2 #3 #4(value)
1 1 2007-12-20 Male
2 1 2007-12-20 French
1 2 2007-12-20 Female
2 2 2007-12-20 English
I have a dimension which is like
id name
1 Gender
2 Language
Is there a way to get measures from column 4 in fact table - so that I can give reports like how gender graph during a time period or language usage during a time period. The design will have more than 2 dimension members, so fact column can hold more data like location, etc...
Can you please suggest a way to handle such kind of fact table?
When I try to create a measure based on that column (and 'no aggregation') - I get error like - "Error 1 Errors in the metadata manager. The data type of the 'value' measure is not valid because the data type of the measure is a string type."
Thanks,
Chandra
View 4 Replies
View Related
Feb 24, 2011
I am using sql server 2005 enterprise edition.
How to list all the dimension and fact column names with mdx or tsql query...
View 9 Replies
View Related
May 18, 2011
Have to create SSIS package for the below requirement:
I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table.
The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table.
I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table.
I have given a sample data structure below:
Source Data
Excel1_Order.xls
OrderNumber OrderQuantity OrderDate
Order10001 100 01-01-2011
Excel2_Customer.xls
CustomerNumber CustomerName CustomerAddress
[Code] ....
Is there any way to handle this in SSIS?
View 16 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Aug 6, 2013
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies
View Related
Mar 30, 2004
I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
Thanks.
View 5 Replies
View Related
Jul 20, 2005
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
View 2 Replies
View Related
May 2, 2014
I have a query like following
SET NOCOUNT OFF
SET ROWCOUNT 0
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()
[Code] ....
and when i execute it, it gave a return that i expected, but then i want to add a date column on the first column.
View 5 Replies
View Related
Oct 8, 2007
How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
View 1 Replies
View Related
May 5, 2008
Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon
View 5 Replies
View Related
Jun 15, 2007
hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!
View 2 Replies
View Related
Jul 5, 2007
ALTER TABLE BFTITLE ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL ;
syntax is not working so can any one tell the right syntax
View 3 Replies
View Related
Mar 20, 2007
Sanjeev Dhiman writes "Sir,
How I can add a new column between existing two column...?
Sanjeev Dhiman"
View 2 Replies
View Related
Sep 12, 2005
Suppose I have a table with the following columns: Year, SalesInEurope,SalesInAmerica, TotalSales. I want to add a new column calledSalesInAsia, say, but I want it to appear before TotalSales. How canthis be achieved?Thanks,Bruno
View 13 Replies
View Related
Oct 26, 2006
Hi
I have a column with times in
845
930
1015
1145
I need to update the column to look like this:
08:45
09:30
10:15
11:45
thanks in advance
rich
View 1 Replies
View Related
Dec 27, 2006
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
View 1 Replies
View Related
Oct 13, 2005
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
View 8 Replies
View Related
Jun 6, 2006
I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.
View 1 Replies
View Related
Dec 18, 2001
I have a table size 2078mb, number of row +530,900. Is it normal for sql to lock users out of the db when I add a column to the end of the table? I'm running SQL 7.0. The table has 4 col regular indexes. No primary keys. It locked the user out for about 10 min. I thought with SQL 7.0 this problem went away?
View 1 Replies
View Related
Mar 26, 2002
Hello Everybody
We have a SQL Server 6.5 with SP 5a . I want to add an additional column in one of the tables of a database .
Since i don't have much experience on SQL 6.5 , i need your precious help in resolving this case .
Can somebody help me in this regard in a detail step wise manner ? Enterprise manager does not have facility to add new columns to an existing table in SQL 6.5 .
I want to add a Column called 'DM ' with datatype as 'bit' with size as 1 and as non nullable .
Any kind of help will be very precious to me .
Thank you all in advance.
Regards
Rita.
View 5 Replies
View Related
May 16, 2002
This is a very simple select but I would like to add a final column that adds the QOH, QOB, QOO and Quantity. How can I accomplish this? Thanks
SELECT DISTINCT
zcus_MM_Medsurg_Used.stock_no,
zcus_MM_Medsurg_QOH_Only.qty_on_hand AS QOH,
zcus_MM_Medsurg_Used.QOB,
zcus_MM_Medsurg_Used.QOO,
zcus_MM_Medsurg_Used.QUANTITY
FROM
zcus_MM_Medsurg_QOH_Only INNER JOIN
zcus_MM_Medsurg_Used ON
zcus_MM_Medsurg_QOH_Only.stock_no = zcus_MM_Medsurg_Used.stock_no
View 1 Replies
View Related
Feb 24, 2003
Can I add a column to a database table without dropping and recreating the table?
The problem is that everytime a user creates an action that requires a new table - at the moment I drop the table and recreate the table with the new column.
This requires lots of resources as I have to populate the table again.
Is there a design way I can go around this?
View 5 Replies
View Related
Jul 7, 2004
I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Any ideas?
View 9 Replies
View Related
Jan 20, 2004
Hi,
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
Laura
View 5 Replies
View Related
Oct 29, 2013
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
View 4 Replies
View Related
Aug 27, 2005
hello friends!
I hav a table..which has 4 columns and 5 rows..
whenever i add a new column it goes to end of the table..
Now i want to add a new column in between 2nd and 3rd column..
Is there a way to add a new column to a specified place in a table??
THANKYOU!
View 4 Replies
View Related
Sep 27, 2005
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (aexe.ReturnCode) as '# Patched'
from WrkSta, AeXEvt_AeX_SWD_Execution aexe
where WrkSta.WrkStaId=aexe.WrkStaId
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
and (aexe.returncode='0' or
aexe.returncode ='3010')
and aexe.AdvertisementName like 'MS05-035-043%'
group by substring (WrkSta.[Name], 1,2)
---
Returns :
Location # Patched
EP 102
ES 1986
ET 19
AB 174
SU 6
EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (coll.WrkStaId ) as '# Workstation'
from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
group by substring (WrkSta.[Name], 1,2)
---
returns:
Location # Workstations
EP 178
ES 2299
ET 24
AB 215
SU 13
EM 582
What I need is :
Location # Workstations # Patched
EP 178 102
EI 2299 1986
ET 24 19
AB 215 174
SU 13 6
EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else?
Once again I appreciate your help in advance....
View 1 Replies
View Related
Aug 17, 2007
i'm trying to add an 'id' column that will auto_increment in a table that i've set up in sql enterprise server. i tried using the code:
ALTER TABLE tablename ADD id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
however, i get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ID'.
could someone please help (ASAP!!!) with how to fix this.
thanks in advance for any help.
View 1 Replies
View Related
