Deleting Multiple Columns From Multiple Objects
Apr 2, 2008
Is there a way to delete from multiple tables/views a column with a specific
name? For example, a database has 50 tables and 25 views all have a column
named ColumnA. Is it possible to write a simple script that will delete
every column named ColumnA from the database?
Seems to be it would be possible and I can somewhat vision it using
sysobjects but without wanting to spend too much time generating the script
(when I could in shorter time manually delete) thought I'd pose the question.
Thanks.
View 2 Replies
ADVERTISEMENT
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Mar 23, 2007
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
View 2 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Sep 7, 2007
Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:
This is the how the table is:
EmpID Designation
678
CFA
679
CFA
680
CFA
685
CFP
685
CIMA
685
IMCA
I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA
could anyone provide some help on this?
Thanks
View 1 Replies
View Related
Feb 12, 2003
I need to delete multiple sql logins on one of my database servers that are no longer needed. Most of these logins are not attached to any database on the server because the database has been deleted from this server. Is there any way to delete logins that are no longer needed without having to review every login to verify it is not attached to a database before deleting?
View 2 Replies
View Related
Aug 27, 2004
I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
TIA
View 3 Replies
View Related
Sep 5, 2007
Hi,
How do you delete multiple packages at once ?
We have a folder in SSIS called ETL and there are about 25 SSIS packages in it.
Now we need to update it.
I tried to delete the folder but you get the message that the folder is not empty.
So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work.
Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes.
But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
Any ideas ?
Constantijn Enders
View 1 Replies
View Related
Apr 19, 2005
want to delete rows from two tables after a join.
in Access, here's something we can do:
delete table_A.*, table_B.*
from table_A left join table_B on ...
but in ms sql, it appears (to me) that you can only delete from one table at a time. how would i accomplish what i want to do?
also, is there a distinctrow equivalent in ms sql server? thanks
View 3 Replies
View Related
Jan 10, 2007
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user.
DELETE FROM T1,T2,T3 WHERE column1='1'
how do i do it.
View 4 Replies
View Related
Feb 28, 2007
Hey all,
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=-
Matt Torbin
President
Center City Philadelphia Macintosh Users Group
http://www.ccpmug.org/
View 3 Replies
View Related
Jul 12, 2006
Hi,
Because I'me a newbie on this... and I don't want to make a monstrous-query, please some advice on this:
In pseudo-code:
for objectname in (specified list of objects)
do
some sql code (i.e. create table xyz)
done
With 'objects' I mean a database or table name.
I've searched and found the foreachdb option, but I don't want to execute the sql n ALL db's but only the ones specified.
Any help is appreciated!
View 1 Replies
View Related
Aug 17, 2006
Hi,
I need to change the schema of the stored procedures of several databases.
Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?
thank you
View 4 Replies
View Related
May 10, 2006
I want to delete a user on local but in order to do that I apparently need to delete the schema. The schema has many objects in it. Can I and what is the syntax to move multiple objects from let's say XXX to dbo so I can delete the schema XXX
Thanks!
View 3 Replies
View Related
Mar 1, 2005
Hi,
I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.
Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?
I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
View 1 Replies
View Related
Oct 10, 2007
Hi,
I have different tables with the same schema as follows
ID Name
-----------------
<Table 1>
ID Name
-------------------
1 Name1
2 Name2
3 Name3
<Table 2>
ID Name
-------------------
1 Name1
4 Name4
5 Name5
I just want to delete the row where ID = 1 from these tables in one query ? Is it possible??
Thanks
~Mohan
View 6 Replies
View Related
Jun 8, 2007
Hi All
I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.
The problem I have is within SQL Server Management studio experess. I have a table called OriginalDrawings that looks like this
CREATE TABLE [dbo].[OriginalDrawings](
[DrawingID] [int] IDENTITY(1,1) NOT NULL,
[OriginalFileName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[PartNumber] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[SheetNumber] [int] NULL,
[Revision] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[OriginalDirectory] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[DrawingCategory] [int] NOT NULL,
[ProductFamily] [int] NOT NULL,
[IsSalvage] [bit] NULL,
[FileSize] [int] NULL,
[DataQueryFlag] [bit] NOT NULL,
[DataQueryCode] [int] NULL,
CONSTRAINT [PK_OriginalDrawings] PRIMARY KEY CLUSTERED
(
[DrawingID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I've loaded it with 40,000 records from my test app. If I open it in SSMSE hit Ctrl-A to select all and then press delete SSMSE appears to hang - it freezes for 10 mins+ (after which I restart my PC. I can delete 1000 records at a time OK, I can delete all quite quickly from my test app by walking through the recordset and deleting each record.
Does anyone know why it appears to hang when I try the delete all?
Thanks
Alec
SQL Server 2005 Express 9.00.3042.00
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
View 1 Replies
View Related
Jul 13, 2007
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName
Table 4: Scores--StudentID, AssignmentID, Score
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
View 6 Replies
View Related
May 26, 2007
is there a way to delete multiple tables using the Management Studio GUI?
View 1 Replies
View Related
Feb 9, 2007
Hello,
I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.
Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3.
Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them)
Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)
I would be thankful for any advices.
View 4 Replies
View Related
Aug 22, 2007
What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database??
I would like a separate file for each object (table, view, or stored procedure).
Could someone point me in the right direction??
Thanks,
Paul
View 1 Replies
View Related
Jul 24, 2007
Hi,
I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID].
I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03.
I have come up with something like that but it does not seems to be correct.
CREATE PROCEDURE [sp_delete_test01_1] (@id [int])
AS
DELETE [test01] DELETE [test02] DELETE [test03]
WHERE ( [id] = @id)GO
Your advise please. Many Thanks.
View 4 Replies
View Related
Mar 1, 2004
i have a database sturcture :
| tbl_Customer | -> one to many ->| tbl_Orders |
What i want is to delete one customer from the tbl_Customer table, and automatically delete all the child orders to that customer.
i could by using loops, look through all the childs and delete the records one by one and the customer last.
so i wounder if there are an SQL state that automatically look up childs and drop them all by calling an stored procedure.
View 3 Replies
View Related
Jun 5, 2006
I have a sql server project where I have added 1 stored procedure (named StoredProcedure1) and 1 class (named MyClass). MyClass has two functions Func1() and Func2(). The stored procedure simply instantiates MyClass and calls Func1().
Func1() creates a SqlConnection object using "context connection=true" - opens the connection and then calls Func2(). Func2() also creates a SqlConnection object using "context connection=true" - and attempts to open the connection. However when attempting to the Open() call inside Func2(), the code just exits. What gives? The code is pasted below, thanks!
*******THE STORED PROCEDURE**************
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlServerProject1;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{
// Put your code here
MyClass obj = new MyClass();
obj.Func1();
obj = null;
}
};
*******THE CLASS "MYCLASS"**************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class MyClass
{
public MyClass()
{
}
public void Func1()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
conn.Open();
Func2();
conn.Close();
}
}
public void Func2()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
if ( conn.State != ConnectionState.Open )
conn.Open();
if ( conn.State == ConnectionState.Open )
conn.Close();
}
}
}
View 1 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
