March 3, 2009 at 11:39 pm
Hi,
There is a table with 50 columns:
I need to select 48 or 49 columns only.
Is there any way to do this?
Ex:
select col1,col2,........,col48 from myTable
i do not need col49 and col50
(the two columns might be any two from those 50)
Thanks
Thanks,
Santhosh
March 4, 2009 at 12:40 am
Yup, list all the column names (as you've done in your example)
If you don't want to type, and I don't blame you, then there's a quick way. Expand out object explorer until you can see the table. Expand that out as well. Click on "Columns" and drag that to the query window and you'll have a full list of all the columns in the table, comma delimited. Then you can remove the two that you don't want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 12:50 am
You already have the query you are looking for... Then what else you need??
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
March 4, 2009 at 2:08 am
Hi,
Personly I din't like my own idea, but with dynamic sql you can have a shot-
DECLARE @COLUMNS VARCHAR(1000)
DECLARE @Col1 varchar(100), @Col2 varchar(1000)
SET @COL1 = 'DATE'
SET @COL2 = 'dateId'
SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from INFORMATION_SCHEMA.columns
WHERE Table_name ='TimeDimension'
AND COLUMN_NAME != @col1
AND COLUMN_NAME != @COL2
FOR XML PATH ( '' ) ), 3, 1000)
EXEC('SELECT '+ @Columns +' FROM TimeDimension')
John Smith
March 4, 2009 at 2:33 am
Yet another way:
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)
SET @table_name = 'Person.Contact'
-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Suffix')
INSERT INTO @excluded_columns VALUES ('rowguid')
SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
SET @columns = RIGHT(@columns, LEN(@columns) - 2)
EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)
Best regards
Flo
March 4, 2009 at 2:42 am
florian.reischl (3/4/2009)
Yet another way:
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)
SET @table_name = 'Person.Contact'
-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Suffix')
INSERT INTO @excluded_columns VALUES ('rowguid')
SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
SET @columns = RIGHT(@columns, LEN(@columns) - 2)
EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)
Best regards
Flo
Hi florian,
Thanks
I think one line is missed out here
I have added it below:
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)
SET @table_name = 'csd_temp_1'
-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Recommendations')
INSERT INTO @excluded_columns VALUES ('Lessons Learned')
SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
--missed line
and name NOT IN(select name from @excluded_columns)
SET @columns = RIGHT(@columns, LEN(@columns) - 2)
EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)
Thanks,
Santhosh
March 4, 2009 at 2:53 am
Oups... did not yet get my breakfast...
Thanks and sorry
Flo
March 4, 2009 at 2:55 am
florian.reischl (3/4/2009)
Oups... did not yet get my breakfast...Thanks and sorry
Flo
Its ok Flo,
that gave me a chance to think a little.. 😎
Thanks,
Santhosh
March 4, 2009 at 3:11 am
Thanks Flo and Gail,
but the requirement is
1. to compare two tables
2. in these two tables there are 50 columns
3. 1 or 2 or 3(max) columns are of type NTEXT
4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.
the code i'm using for this is:
CODE_BLOCK-1
select 'table1' as tblName, * from
(select * from table1
except
select * from table2) x
union all
select 'table2' as tblName, * from
(select * from table2
except
select *
from table1) x
If I need to write all the columns it will be confusing(rather than confusing it will be difficult)
now I have this code:
CODE_BLOCK-2
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)
SET @table_name = 'csd_temp_1'
-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Recommendations')
INSERT INTO @excluded_columns VALUES ('Lessons Learned')
SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
--missed line
and name NOT IN(select name from @excluded_columns)
SET @columns = RIGHT(@columns, LEN(@columns) - 2)
EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)
i'm planning to create a procedure combinig the above 2 code blocks
input parameters will be: table1, table2, col1, col2, .....coln (NTEXT columns)
I need help in this, I do not know how many NTEXT columns will be there,
so the procedure should accept all the input parameters(first 2 being table names and remaining NTEXT columns)
In CODE_BLOCK-2, I need the sproc to insert the input NTEXT columns into @excluded_columns as below
-- Excluded columns
INSERT INTO @excluded_columns VALUES ('col1')
INSERT INTO @excluded_columns VALUES ('col2')
INSERT INTO @excluded_columns VALUES ('col3')
.
INSERT INTO @excluded_columns VALUES ('coln')
I am just trying to make it in general rather than only for a fixed columns.
I will update here whatever I have done ASAP.
Please let me know if this post is confusing, I will try to modify...
Thanks
San
Thanks,
Santhosh
March 4, 2009 at 3:34 am
Santhosh (3/4/2009)
Thanks Flo and Gail,but the requirement is
1. to compare two tables
2. in these two tables there are 50 columns
3. 1 or 2 or 3(max) columns are of type NTEXT
4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.
Thanks
San
Then it is very easy without column variable
DECLARE @COLUMNS VARCHAR(1000)
DECLARE @Col1 varchar(100), @Col2 varchar(1000)
SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from INFORMATION_SCHEMA.columns
WHERE Table_name ='TimeDimension'
AND Data_type != 'Ntext'
FOR XML PATH ( '' ) ), 3, 1000)
EXEC('SELECT '+ @Columns +' FROM TimeDimension')
John Smith
March 4, 2009 at 4:22 am
Mangal Pardeshi (3/4/2009)
Santhosh (3/4/2009)
Thanks Flo and Gail,but the requirement is
1. to compare two tables
2. in these two tables there are 50 columns
3. 1 or 2 or 3(max) columns are of type NTEXT
4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.
Thanks
San
Then it is very easy without column variable
DECLARE @COLUMNS VARCHAR(1000)
DECLARE @Col1 varchar(100), @Col2 varchar(1000)
SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from INFORMATION_SCHEMA.columns
WHERE Table_name ='TimeDimension'
AND Data_type != 'Ntext'
FOR XML PATH ( '' ) ), 3, 1000)
EXEC('SELECT '+ @Columns +' FROM TimeDimension')
I am sorry, I would have mentioned this
"this is for a linked server"
(one table is of SQL, and another is of Access-linked server)
How to achieve the samething to query a linked table,
Is there any T-SQL statement to do this?
like
SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from LINKED_SERVER...Table
WHERE Table_name ='TimeDimension'
AND Data_type != 'Ntext' -- this will not be applicable itseems.
to get columns list we havesp_columns_ex AccessDB --linked server name
Thanks
Thanks,
Santhosh
March 4, 2009 at 5:17 am
Hi,
I have a sproc usp_compare
exec usp_compare table1,table2, c1,c2 --,c3,c4,c5
inside sproc I have
alter proc usp_compare_temp
@t1 sysname,
@t2 sysname, --please ignore this for now
@c1 varchar(100),
@c2 varchar(100)
--is it possible to declare array here to accept parameters c1,c2,...,cn?
as
BEGIN
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @columns NVARCHAR(MAX)
/*
Below comes a FOR loop to insert the items in ARRAY to @excluded_columns
How to do this?
*/
-- Excluded columns
INSERT INTO @excluded_columns VALUES (@c1)
INSERT INTO @excluded_columns VALUES (@c2)
SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@t1)
and name NOT IN(select name from @excluded_columns)
print @columns
SET @columns = RIGHT(@columns, LEN(@columns) - 2)
print @columns
EXECUTE ('SELECT ' + @columns + ' FROM ' + @t1)
END
The above code gives the output without selecting the columns passed as parameter
Is it possible to declare an array variable there?
If so how to do that,
Please provide me any links or help on this.
Thanks
San
Thanks,
Santhosh
March 4, 2009 at 6:42 am
Hi,
Here is the full code that I have written till now,
ALTER PROCEDURE USP_Compare_Gen
@table1 SYSNAME,
@table2 SYSNAME,
@memo1 VARCHAR(100) = '' --have to declare an ARRAY variable or something like that
/*,
@memo2 VARCHAR(100) = '',
@memo3 VARCHAR(100) = '',
@memo4 VARCHAR(100) = ''
*/
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @table1='['+@table1+']'
--If there is no NTEXT field then compare whole table
IF @memo1 = ''
--IF (@memo1 | @memo2 | @memo3 | @memo4) = ''
BEGIN
SET @sql = 'SELECT ''SQL'' AS DB,''' + @table1 + ''' AS tblName, * FROM
(SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + ') x
UNION ALL
SELECT ''Access'' AS DB,''' + @table2 + ''' AS tblName, * FROM
(SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 +') x'
EXEC(@sql)
END --IF
--If do exists NTEXT field then compare tables without that column
ELSE
BEGIN
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @Access_Columns NVARCHAR(MAX),
@SQL_Columns NVARCHAR(MAX)
--===========================
-- Excluded columns
--To be added a FOR loop to insert NTEXT fields FROM input parameters
INSERT INTO @excluded_columns VALUES (@memo1)
--===========================
SET @Access_Columns = ''
SELECT @Access_Columns = @Access_Columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@table1)
and name NOT IN(SELECT name FROM @excluded_columns)
SET @Access_Columns = RIGHT(@Access_Columns, LEN(@Access_Columns) - 2)
SET @SQL_Columns = ''
SELECT @SQL_Columns = @SQL_Columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@table1)
and name NOT IN(SELECT name FROM @excluded_columns)
SET @SQL_Columns = RIGHT(@SQL_Columns, LEN(@SQL_Columns) - 2)
SET @sql = 'SELECT ''SQL'' AS DB,''' + @table1 + ''' AS tblName, * FROM
(SELECT '+@SQL_Columns +'FROM ' + @table1 + '
EXCEPT
SELECT '+@Access_Columns +'FROM ' + @table2 + ') x
UNION ALL
SELECT ''Access'' AS DB,''' + @table2 + ''' AS tblName, * FROM
(SELECT '+@Access_Columns +'FROM ' + @table2 + '
EXCEPT
SELECT '+@SQL_Columns +'FROM ' + @table1 +') x'
EXEC(@sql)
END --ELSE
END --DECLARE
Any help at this stage will really help me!!
Thanks,
San
Thanks,
Santhosh
March 4, 2009 at 6:57 am
Santhosh (3/4/2009)
but the requirement is1. to compare two tables
If all you're trying to do is compare two tables, have you considered using the tablediff utility that comes with SQL?
http://msdn.microsoft.com/en-us/library/ms162843(SQL.90).aspx
If you use the -b flag and specify 0, it won't compare any of the LOB columns. It's a lot easier than writing custom code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 10:48 pm
GilaMonster (3/4/2009)
Santhosh (3/4/2009)
but the requirement is1. to compare two tables
If all you're trying to do is compare two tables, have you considered using the tablediff utility that comes with SQL?
http://msdn.microsoft.com/en-us/library/ms162843(SQL.90).aspx
If you use the -b flag and specify 0, it won't compare any of the LOB columns. It's a lot easier than writing custom code.
Hi Gail,
In the Remarks section I found the below statement
Remarks
The tablediff utility cannot be used with non-SQL Server servers.
I'm Comparing a table of Access database(Linked Server) with a table in SQL Server.
Is it possible to do this?
Thanks
San
Thanks,
Santhosh
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply