Dreaded TEXT column

I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:

Example: table name is CREDITS

vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.

Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.

Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.

Lars

Yes, this would work, you could also leave your tables intact the way they were and implement bi-directional transactional replication.

However I assume you're on SQL 2000, correct? If you're on SQL 2005, immediate updating would work just fine if you replaced text datatype with varchar(max) datatype, it's also more optimal and efficient in terms of data storage.

|||

Thank you,

yes, we still are on SQL 2000 and won't upgrade until another 6 months. I have never heard of bi-directional replication since it wasn't mentioned anywhere. I found a great link on MS com and will take a look at it. Thanks again.

L

Drawing line between table rows and columns

how to insert line between rows and columns in reporting services 2005Couldn't you just set the borderstyle top, bottom, left, or right according to where you wanted the lines?

Dragging multiple fields to a table?

Ok, there has to be a way of dragging multiple fields from a dataset
to a table like there is in Access. I have a dataset of about 30-40
columns and it will be excruciating to right click on a column in the
table 30-40 times to add more columns so that I can then drag one at a
time all the fields to each new column.
Please tell me that I don't have to do this one by one. Thanks for
any input.You can drag one field at a time from the field list and drop it in the
cell. You don't to right-click on the cell and add it through the
expression builder. you can select multiple fields and drag&drop them but I
don't think that will work unless your using a list.
"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
news:e876e3c8.0407211308.39c6da3a@.posting.google.com...
> Ok, there has to be a way of dragging multiple fields from a dataset
> to a table like there is in Access. I have a dataset of about 30-40
> columns and it will be excruciating to right click on a column in the
> table 30-40 times to add more columns so that I can then drag one at a
> time all the fields to each new column.
> Please tell me that I don't have to do this one by one. Thanks for
> any input.

Drag columns around?

Hello,

I am a complete newbie to CR and am evaluating CR-XI. I have a requirement to create user-customizable reports - i.e. reports where the user can move columns around to get a layout he/she likes for printing. For example, if there were 5 fields in the report:

Column1 Column2 Column3 Column4 Column5

and the user wanted to print the report with the field order:-

Column1 Column2 Column5 Column3 Column4

is it possible for the user to be able to drag the columns around? Being an amateur, could someone please list the steps I need to go thru to achieve this? Is this possible programatically - via VB6/VB.Net and if so could someone direct/show me some detailed code for this?

Apart from what I described above, the bigger requirement is to create reports at run-time from scratch. I need to create an application that will accept SQL queries from the user, parse it, run it, and display the resultant recordset as a CR. Could someone please direct/show me some code on how to do it - I am a beginner and so will need as much help as possibe.

I use Delphi 7/.Net but am pretty sure I will be able to convert VB to Delphi.

I very much appreciate any help I can get.

Thanks,
VrijeshSee if you find solution at the suppoet section of this site
www.BusinessObjects.comsql

Doubt in Index

Hi EverB,

I have created a table say T1 with 5 columns (col1.....col5).
I have created an index on col4 and col5.

iam executing a select query say.

select col1,col2,col3 from t1 where col1 =0;

In the above query i hav not used the columns for which i hav created the index.My ques is-

Does the index really works when u execute the above query?

Any answer please.......
It wuld be better if u culd suggest some sites for this.

Thanks and Regds,
Arunno, for that query, the index is ignored

a good tutorial is Optimizing your MySQL Application (http://www.sitepoint.com/print/optimizing-mysql-application) even if you're using something other than mysql

double unique index

Hello.

I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:

Row 1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 2

this shouldn't be possible.

Row1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 3

this should be possible

is there any way I can do this?

thanks in advance

You can create a UNIQUE constraint or a unique INDEX.

ALTER TABLE YourTable ADD CONSTRAINT UK_YourTable_ColumnA_ColumnB UNIQUE (ColumnA,ColumnB)

Or

CREATE UNIQUE INDEX IX_YourTable_ColumnA_ColumnB ON YourTable (ColumnA,ColumnB)

The constraint or index can also be clustered if you do not have a clustered PK/index/constraint yet.

Double quotes in ASCII file

I've an issue with double-quotes in CSV file. One of the columns may contain this kind of value: "STATUS ""H"" "

I've got quote set to "

The file source fails on such records.

I found this thread and Scott tells us there that the file can't contain " in data.

Is this 100% correct?

I've got mutliple text columns and the pain is that I don't know which column might have these cases in future. To create a script means to write my own file parser for all files I use.

Any ideas?

Dima.

Hi,

If you have a CSV file, do you actually need to specify the text delimeter - or do you have some text that has commas in the string as well?

If your source file contains things like

"Status ""H"" ", "Next string", 1234, etc,

then I would try to solve the problem at source. That formatting suggests to me that the data is from another database where the actual data is ,Status "H", and the CSV export is trying to escape the " character by doubling it up.

Perhaps if you can export the CSV with no text delimiters, or with a character that is guaranteed not to appear, you may be OK.

Hope this helps,

Richard

|||

Richard,

you are correct in your gueses. I do have data like this:

"Status ""H"" ", "Next string", 1234, etc,

I can't remove quoation mark since I may have data like this:

"Status ""H"" ", "Next,string", 1234, etc,

So, if I removed " I'd have another error like this:

Status "H", Next,string, 1234, etc,

So, it wouldn't parse it correctly.

I can't change quotation, symbol since I don't control source system...

Now, what I could change is data within the column. So, I could replace:

Status "H"

with something like

Status &quot;H&quot;

Then when I read this - I'd convert it back to:

Status "H"

What do you think?

Dima

|||

Dima,

That looks like a solution to me - assuming you are going to make this change prior to bringing the data into the database, ie by pre-processing the CSV file.

What process are you going to use to make the changes? I'm sure you've thought of this, but if you were just to globally change "" to &quot then it will fall over when you three or more " in a row.

eg. """H"" is my status" should become "&quotH&quot is my status" , not &quot"H&quot is my status"

I've not tried it, but you can probably do this with an SSIS script of some sort, passing regular expressions. That would be better than having an external process change the file.

Regards,

Rich

|||

The file I'm dumping is on Unix. So, I was thinking about creating sed script, using regular expressions to post-process the file after it's dumped...

Thank for the feedback BTW it helped me think this thru

double quotes

Hi,
I am creating a flat file connection to a .csv file
In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them.
They do not have "" in the .csv file.
ThanksDo you have " set as the text qualifier for the flat file connection manager? You may have to put this in there. Also, how are you looking at and/or creating the csv file? If you are using Excel, it will automatically enclose text with " and when you view it in Excel it will parse it for you. Try looking at the csv file using Notepad or another basic text editor.

Doing the same replace on several columns?

In the derived column task you can choose each column and write an expression for each column. But when you need to do a <ISNULL(status) ? "0" : statusdato> on 40-50 columns it get kind of irritating. Is there a way easy to do the sam expression on a selection of columns like a sort of derived column task, where you write an expression and assign that to a selection of columns (otherwise this would be a wish :-) )

Sorry, there is no such feature. Sounds like a good candidate to enter as a suggestion.

Note: it would be possible to write code that programmatically does what you are looking for, by loading a package, finding the derived column transform, getting the input column collection, and setting the expression properties.

Thanks
Mark

|||Ascential's (now IBM) DataStage had a really nice interfaces for doing just what you've asked. while SSIS doesn't have this interface, it does have the Script Component, which I find much more flexible than DataStage's Transformer stage.

I had a similar dilemma with having a good number of my input fields requiring trimming. Following the lead of several custom component examples I got rid of the ProcessInput_Row (I think that's what's in there) and overrode PreExecute and ProcessInput with the following in a Script Componet, of type Transform.

The biggest drawback is having to select all the checkboxes on the Input Columns tab and set them all to ReadWrite, but with some keyboard skills, that can go pretty quickly.

I've not been a programmer in any of my previous lives, so please forgive the poor form:

Public Class ScriptMain
Inherits UserComponent

Private _inputColumnInfos As ColumnInfo()

Public Structure ColumnInfo
Public bufferColumnIndex As Integer
Public lineageID As Integer
End Structure

Public Overrides Sub PreExecute()
Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim _inputColumnInfos(input.InputColumnCollection.Count - 1)

For x As Integer = 0 To (input.InputColumnCollection.Count - 1)
Dim column As IDTSInputColumn90 = input.InputColumnCollection(x)
_inputColumnInfos(x) = New ColumnInfo()
_inputColumnInfos(x).bufferColumnIndex = input.InputColumnCollection.FindObjectIndexByID(column.ID)
_inputColumnInfos(x).lineageID = column.LineageID
Next
MyBase.PreExecute()
End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
Dim columnInfo As ColumnInfo

While (Buffer.NextRow())

For x As Integer = 0 To (_inputColumnInfos.Length - 1)
columnInfo = _inputColumnInfos(x)

Dim trxVal As String = Buffer.GetString(columnInfo.bufferColumnIndex).ToString().Trim

Buffer.SetString(columnInfo.bufferColumnIndex, trxVal)
Next
End While
End Sub

End Class

doing a FREETEXTTABLE on more than one columns -OR- creating a SECOND FT INDEX for a tab

Is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Alternatively, is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?
SQL 2005 allows you to select which column you want returned in a full-text
search - this is for both Contains and FreeText. SQL FTS also allows you to
full-test index indexed views, which might also help you.
You can only create a single Full-Text index per table. I think what you
might have to do in SQL 2000 is to partition your table into multiple child
tables containing the columns you wish and then full-text index these
tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||SQL2000 also lets you select a column, but only one! Does 2005 allow
multiple columns?
Partition the table to smaller ones? How do you mean that exactly?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
> SQL 2005 allows you to select which column you want returned in a
> full-text
> search - this is for both Contains and FreeText. SQL FTS also allows you
> to
> full-test index indexed views, which might also help you.
> You can only create a single Full-Text index per table. I think what you
> might have to do in SQL 2000 is to partition your table into multiple
> child
> tables containing the columns you wish and then full-text index these
> tables.
|||Denis,
Yes, this can be done, see "SQL Server FTS across multiple tables or
columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
and substitute freetexttable for containstable in the examples.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included in the index. Any ideas?
Or perhaps you could show me how CONTAINSTABLE could be applied in this
case?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uKI3DMLKFHA.2628@.tk2msftngp13.phx.gbl...
> Denis,
> Yes, this can be done, see "SQL Server FTS across multiple tables or
> columns" at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> and substitute freetexttable for containstable in the examples.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> statement,
>
|||No SQL 2000 allows you to search one column or all columns. SQL 2005 lets
you search one column, all columns, or any combination of columns.
To partition a table you have to make one or more tables with the same PK.
So if you have a table like this:
Create Table parent
( pk int not null identity constraint primarykey primary key,
charcol1 char(20),
charcol2 char(20),
charcol3 char(20),
charcol4 char(20))
You could create the following child tables
Create Table child1
( pk int not null identity constraint primarykey1 primary key,
charcol1 char(20))
Create Table child2
( pk int not null identity constraint primarykey2 primary key,
charcol2 char(20))
Create Table child3
( pk int not null identity constraint primarykey3 primary key,
charcol3 char(20))
And then use triggers or replication to keep them in sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:eIOgqtKKFHA.1500@.TK2MSFTNGP09.phx.gbl...
> SQL2000 also lets you select a column, but only one! Does 2005 allow
> multiple columns?
> Partition the table to smaller ones? How do you mean that exactly?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included And if EITHER of the fields
contain the search string i want them displayed.
|||SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.
|||Create Table tblJobsDataWareHouse
(uid int not null identity constraint primarykey4 primary key,
fldRequirementshtm char(20),
fldJobTitle char(20),
fldCompanyName char(20))
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values ('test',null,null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL,'test',null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'test')
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'rest')
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'test','create'
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
GO
sp_fulltext_catalog 'test','start_full'
go
select * from tblJobsDataWareHouse where contains(*,'test')
go
select * from tblJobsDataWareHouse as tbl join (
select * from containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
union
select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
)as ft
on ft.[key]=tbl.uid
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
> I looked at what you said but I am not sure how that would work. Let me
> give you an idea of the type of how i am trying to do the search.
>
> -- Old Search
> --SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
> FT_TBL.uID = KW.[KEY]'
> --SET @.RankField = 'KW.RANK'
>
> -- NEW SEARCH
> SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
> ''') as KW ON FT_TBL.uID = KW.[KEY] '
> SET @.RankField = 'KW.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
> KWw ON FT_TBL.uID = KWw.[KEY]'
> SET @.RankField = 'KWw.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
> as KWww ON FT_TBL.uID = KWww.[KEY]'
> SET @.RankField = 'KWww.RANK'
>
> I want my new search to look at Title, Description and Company name only,
> rather than every field that was included And if EITHER of the fields
> contain the search string i want them displayed.
>
|||many thanks for your code example.
After some thinking i decided that due to the structure of the database is
best not to break-down the table into smaller ones, so i guess i will have
to find another way of doing it.
But thank ytou very much for the time you took to answer my question.
again, many thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e%23msOZZKFHA.656@.TK2MSFTNGP14.phx.gbl...
> Create Table tblJobsDataWareHouse
> (uid int not null identity constraint primarykey4 primary key,
> fldRequirementshtm char(20),
> fldJobTitle char(20),
> fldCompanyName char(20))
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values ('test',null,null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL,'test',null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'test')
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'rest')
> GO
> sp_fulltext_database 'enable'
> GO
> sp_fulltext_catalog 'test','create'
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add',
> 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
> GO
> sp_fulltext_catalog 'test','start_full'
> go
> select * from tblJobsDataWareHouse where contains(*,'test')
> go
> select * from tblJobsDataWareHouse as tbl join (
> select * from
> containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
> union
> select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
> union
> select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
> )as ft
> on ft.[key]=tbl.uid
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
>

doing a distinct insert?

hi there,

i was just wondering if it's possible to do a INSERT INTO, but with a DISTINCT on three columns at ones?

i want to insert values from a SELECT DISTINCT column_1, column_2, column_3 FROM table

thanks

SJB

Is this what you want to do?

INSERT newTab (col1, col2, col3)
SELECT DISTINCT col1, col2, col3 FROM otherTab

This works just fine.

/Kenneth

|||

yes

if i understand your ideea , this is the scenario:

I have 2 similar tables :

CREATE TABLE [dbo].[person](

[last] [nvarchar](50) NULL,

[first] [nvarchar](50) NULL,

[age] [nvarchar](50) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[person](

[last] [nvarchar](50) NULL,

[first] [nvarchar](50) NULL,

[age] [nvarchar](50) NULL

) ON [PRIMARY]

person have 3 rows with 2 duplicates

insert into person1

select distinct last,first,age

from person

insert only 2 rows

Doing a CONTAINS search on multiple tables and columns combined.

I want to do a CONTAINS search on several columns combined.
This example searches each column separately, so that a record is not
included that has one of the search terms in the Desired Position and the
other two search terms in a section body.
SET @.SearchPhrase = 'Engineer AND SQL AND VB'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
FROM
dbo.tblResumes LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID
WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
How could I return a resume that has all three terms, but not in any one
column?
Also, I don't understand how the search works when their are several
tblResumeSections records for one tblResumes record. Does it search each
section record?
I am grateful for any suggestions.
As I've played with this in light of other recent similar posts, the only
approach I can see at this point is to create the query in code and send it
to the database server. This allows me to dynamically build the query to
allow for a variable number of search terms.
This must be a very common search and newsgroup queston: how to do an 'AND'
search invoving multiple tables and columns where all of a variable number
of search terms must be found in a row, but not any one column..
I can't find a way to do this with FULL TEXT searches in a stored procedure
so far. I get an error if I try to concatenate columns in a CONTAINS search
as below.
Again, any suggestions are very welcome.
The following query is constrcted in code on the web page and does a LIKE
search on a concatenation of the relevant columns. It works but falls short
in that a resume that has the search terms in different sections (child
table) is not returned. But it seems the best I can do for now.
DECLARE @.SearchPhrase VarChar(50)
, @.Role VarChar(15)
SET @.SearchPhrase = 'SQL AND VB AND Engineer'
SET @.Role = 'Job Seeker'
SELECT DISTINCT
dbo.tblResumes.ResumeID
, dbo.tblResumes.DesiredPosition
, dbo.tblPostionTypes.Abbr AS PType
, dbo.tblContacts.FirstName + ' ' + dbo.tblContacts.LastName AS
JobSeekerName
, dbo.tblResumes.DateRevised AS Posted
, dbo.tblContacts.City + ', ' + dbo.tblContacts.State AS CityState
FROM
dbo.tblResumes INNER JOIN
dbo.tblContacts ON dbo.tblResumes.ContactID = dbo.tblContacts.ContactID
INNER JOIN
dbo.tblPostionTypes ON dbo.tblResumes.PositionTypeID =
dbo.tblPostionTypes.PositionTypeID LEFT OUTER JOIN
dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
dbo.tblResumeSections.ResumeID LEFT OUTER JOIN
dbo.tblExperiences ON dbo.tblResumeSections.ResSectionID =
dbo.tblExperiences.ResSectionID LEFT OUTER JOIN
dbo.tblQualifications ON dbo.tblResumes.ResumeID =
dbo.tblQualifications.ResumeID
WHERE dbo.tblResumes.DateStart < GETDATE()
AND dbo.tblResumes.DateStop > GETDATE()
AND dbo.tblResumes.Deleted Is Null
AND dbo.tblResumes.Active = 1
AND dbo.tblResumes.Approved = 1
AND dbo.tblContacts.Active = 1
AND dbo.tblContacts.Approved = 1
AND dbo.tblContacts.Role = @.Role
-- Keyword tests
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%SQL%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%VB%'
AND dbo.tblResumes.DesiredPosition + ' ' +
dbo.tblResumeSections.SectionTitle + ' ' + dbo.tblResumeSections.Body LIKE
'%Engineer%'
"GM" <gmdevREMOVE@.starband.net> wrote in message
news:OiI%23kzSREHA.3140@.tk2msftngp13.phx.gbl...
> I want to do a CONTAINS search on several columns combined.
> This example searches each column separately, so that a record is not
> included that has one of the search terms in the Desired Position and the
> other two search terms in a section body.
> SET @.SearchPhrase = 'Engineer AND SQL AND VB'
> SELECT DISTINCT
> dbo.tblResumes.ResumeID
> , dbo.tblResumes.DesiredPosition
> FROM
> dbo.tblResumes LEFT OUTER JOIN
> dbo.tblResumeSections ON dbo.tblResumes.ResumeID =
> dbo.tblResumeSections.ResumeID
> WHERE (CONTAINS(dbo.tblResumes.DesiredPosition, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.SectionTitle, @.SearchPhrase)
> OR CONTAINS(dbo.tblResumeSections.Body, @.SearchPhrase))
> How could I return a resume that has all three terms, but not in any one
> column?
>
> Also, I don't understand how the search works when their are several
> tblResumeSections records for one tblResumes record. Does it search each
> section record?
> I am grateful for any suggestions.
>