Cant Add New Columns To Data Tables
Sep 19, 2005
Hi All,I am having a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks
View 1 Replies
ADVERTISEMENT
Jun 23, 2004
I'm having a very irritating time trying to migrate data from a COBOL system to SQL Server.
One of the A/R Master files has approx. 200 columns.
I can export this file any number of ways that will (sometimes) load partially into my database, but always when the load succeeds, columns 75 through N simply contain NULL, even though there is data in the file. When the load fails in DTS, the error is always missing column delimiter. Using BULK INSERT the error is always something like data too long at column 75.
Putting all this together, I have deduced that something isn't working if I try
to load a staging table with more than 74 columns of data. This seems like a way-too-low threshold for a robust database, especially since SQL Server is supposed to be able to handle up to 1,024 columns per table.
Has anyone ever encountered this problem?
Thanks in advance for any help
View 2 Replies
View Related
Oct 8, 2007
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Sep 10, 2015
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
View 3 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Oct 13, 2006
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
View 5 Replies
View Related
Aug 14, 2015
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies
View Related
Feb 6, 2007
my supervisor is trying to help move our existing database in IBM U2 into SQL Sever he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?
View 3 Replies
View Related
Aug 6, 2015
When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?
SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
View 4 Replies
View Related
Aug 2, 2007
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
View 7 Replies
View Related
May 8, 2005
I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column.
This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30)
--Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0)
--Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
Can anyone help ?
Thanks
View 2 Replies
View Related
Oct 26, 2005
Is there a way to change the collation against all the tables and columns within a database?
View 2 Replies
View Related
Nov 10, 2005
I drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?
View 2 Replies
View Related
Jun 14, 2006
Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!
View 2 Replies
View Related
Feb 5, 2008
How to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
View 2 Replies
View Related
May 5, 2012
I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:
Ingredients_tbl
IngredientID
Description
Inventory_tbl
IngredientID
AmountRemaining
Renconciled
Active
Order_Details_tbl
IngredientID
OrderAmount
Status
Order_Details_Details_tbl
IngredientID
OrderAmount
Status
I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:
Ingredients_tbl
IngredientID -- Description
1 -- Corn
2 -- Beer
3 -- SBM
4 -- Wine
Inventory_tbl
IngredientID -- AmountRemaining -- Reconciled
1 -- 500 -- False
1 -- 500 -- False
1 -- 100 -- True
1 -- 500 -- False
2 -- 1000 -- False
2 -- 1000 -- False
4 -- 500 -- False
4 -- 500 -- False
Order_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- MIXED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
4 -- 100 -- DELIVERED
Order_Details_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- DELIVERED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
I would like the results to be like this:
IngredientID
Description
Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false'
Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
IngredientID -- Description -- Inventory -- Production1 -- Production2
1 -- Corn -- 1500 -- 200 -- 100
2 -- Beer -- 2000 -- 100 -- 100
3 -- SBM -- 0 -- 300 -- 200
4 -- Wine -- 1000 -- 100 -- 100
I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:
SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description,
Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2
FROM Ingredients_tbl
LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'
[Code] .....
But this obviously has its issues with incorrect returned sum values.
View 2 Replies
View Related
May 23, 2008
Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
View 4 Replies
View Related
Jul 26, 2013
I have requirement like this
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
View 5 Replies
View Related
Oct 29, 2013
I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..
View 1 Replies
View Related
Feb 5, 2014
how do I get value from column using list of tables?
For example, I have list:
schema_name, table_name, column_name
How do I get:
schema_name, table_name, column_name, column_value
View 7 Replies
View Related
Aug 7, 2014
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
View 3 Replies
View Related
Aug 8, 2006
Hi,
Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database
For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)
name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?
THANK YOU
View 6 Replies
View Related
Mar 1, 2006
Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....
View 2 Replies
View Related
Jul 20, 2005
Hi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.
View 6 Replies
View Related
Mar 4, 2008
Hi All.
I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.
I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!
I need........
Table A
Column1 Datatype
Column2 Datatype
Table B
Column1 Datatype
Column2 Datatype
etc....
Many thanks.
View 7 Replies
View Related
Oct 21, 2015
In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...
select top 10 * from ProductIdList
View 6 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Dec 20, 2006
I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table Any help would be great, thanks
View 5 Replies
View Related
Apr 9, 2006
I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan
View 4 Replies
View Related
Mar 11, 2002
Hi
I have a 2 Tables EMP, STU
In EMP Table there is a Column "Country"
In STU Table there is a Column "City"
Where the EMPID = STUID
on this conditon how can i update those 2 columns
This is for one Table one column update how can i do 2 at a time
I don't want to do in 2 seperate UPDATE statements
UPDATE EMP
SET EMP.Country = 'USA'
WHERE EMP.EMPID = STU.STUID
Can some one can help me to fix this issue..
From
Madhavi
View 2 Replies
View Related
