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
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!
I have this already existing query and want to add another field to it but its failing. Below is the current query
REPLACE(CONVERT(CHAR(10), A.BATDAT, 1), '/', '') + RIGHT('00000' + CONVERT(VARCHAR, A.BATSEQ), 5
RIGHT('000' + CONVERT(VARCHAR, A.SEQNBR), 3) AS CLIENT_TRACK_NO
I want to add a field from a table name APTTL and the filed name is DTYYY char (2)
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
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,
Sorry 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?
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
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.
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 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 '____.____%'
Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
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?
Please help...
Thanks!!
Joydeep
Can a new field be added to a table that already exists.Thanks in advance.
View 2 Replies View RelatedHi, 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!
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
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.
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!
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 RelatedIn my local environment , i was setup transnational replication then i have added new article by using below script
EXEC sp_changepublication @publication = 'demo', @property =
N'allow_anonymous', @value='TRUE'
Go
EXEC sp_changepublication @publication = 'demo', @property =
N'immediate_sync', @value='TRUE'
EXEC sp_addarticle @publication = 'demo', @article ='employee',
[Code]....
But article not added and showing this error message The initial snapshot for article 'employee' is not yet available.
What is the issue, what can i do to add new article ? What did I mistake?
Hi, All:
I am relatively new to SQL Server 2005 SSIS. I encountered the below error while trying to add an existing package to a newly created Project:
Failed to save package file <path_filename> with error 0x80029C4A "Error loading type library/DLL.".
If someone has encountered this error, please reply with a solution or suggestions.
Thanks.