Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
thx
Kat
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
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.
Thanks!
sgreene
how do i add columns to an existing table?
View 1 Replies View RelatedDear Friends,
what is best and quicker way to add 500 columns to existing table having 145 columns already.
Is there any way to avoid manual work of adding columns one by one in design mode or using script.
I have a TXT file (comma delimited) that contains all those columns names as a first row,but I am not sure if i can use DTS package to create table design having such sourcre TXT file.
Any advice?
Thanks,
Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?
View 4 Replies View RelatedSorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table
When I am using this query:
alter table hem154
add indexNO uniqueidentifier default newid()
alter table hem154
add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO)
go
Note:
Hem154 ~ Table name
indexNo ~ Column Name
I get this runtime error:
Msg 1779, Level 16, State 0, Line 1
Table 'hem154' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.
I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
Dear Friends,
I need a SQL Query to add a identity coloumn for anExisting table. (ie) when i try to alter the table i want to add an identity coloumn.
Thanks in advance.
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
Thanks in advance!
After i run the sql which adds some columns on one particular table.I am getting this Warning
Warning: The table 'usac499_499A' has been created but its maximum row size (9033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I got a series of the above warning message , but the coulmn wa created.
I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
+Identity Specification (Is Identity) Identity Increment Identity Seed
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?
how to add identity column into existing table in sql.
View 5 Replies View RelatedI have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.
If I alter a table and add a column, would this column automatically get picked up in replication?
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
View 7 Replies View RelatedHi all,I have a problem and need some ideas.What I have done: I created a page to upload an excel file into a SQL Server table along with some customer info (from the login, day, etc.). This excel file contains several rows (some of them may be blank) and columns (also some may be blank). The file is stored in an image object.The file will be checked (they want to do it manually, because contents is a problem). If they say it is OK, I want to run a program to add a record into an existing table with the request no. (from the first table, where the object is stored) and all the information available from the filled rows (first row is header). I have a column, which can be checked, if the row contains data or not.Any ideas?I know how to read from and write the contents of the object to a field in the SQL table. Can I use this?Thanks for any idea / code / link.
View 2 Replies View RelatedI am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.
We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.
Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers!
Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.
TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'
I've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer
View 2 Replies View RelatedI have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies View RelatedI 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.
Hi All,
I am having a server where replication is set up between 2 differnt databases. It is currently running. I want to add a couple of tables to the replication. I tried using sp_addArticle, but after executing it, in the properties of the publication it shows the new tables, but at the database level the tables are missing.
I tried with sp_addsubscription but I am getting strange error:
Server: Msg 14100, Level 16, State 1, Procedure sp_addsubscription, Line 240
Specify all articles when subscribing to a publication using concurrent snapshot processing.
What can I do to publish the tables into the target database?
Thanks in advance.
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
Hello,
We currently have a 2 node Active/Passive 2 named instance SQL 2000 cluster. We will be chaning the configuration to Active/Active, basically moving 1 instance to the passive node (so we can take advantage of the resources on the passive node).
We would also like to add 3 nodes to the cluster making it a 5 node SQL cluster. What we are thinking of doing is basically making it an Active/Active/Active/Active with the 5th server being passive. The question I have is will I be able to add and install the 3 new nodes without having to redo the SQL cluster? Should I install the two new active nodes with the default SQL instace or do I have to install SQL with named instances? (actually I think the named instances is the way to go but then I wouldn't be posting here if I was sure about the answer).
Thx!
-Albert-
I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.
Anyone have any ideas? Thanks in advance.
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
Thanks in advance!
HI,
Is there an easy way to install reporting services on a machine that is already running sp1? When I initially isntalled Sql Server I did not install ssrs. Now, SP1 is installed and when I run the setup tool to install ssrs, it warns me regarding a "version change", presumablly because my media is not sp1, but RTM. Any ideas?
We have a process here that produces several dozen Excel Spreadsheets every day from SQL Server using the bcp utility. the problem is that we cannot include the column headers when producing the Output. We are therefore trying to find a method where we can append the column Headers to the Spreadsheets; is there a way of doing this?
View 2 Replies View RelatedHi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?
I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)
but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .
Thanks,
Natalia