Transposing rows and Columns in SQL - Database Discussion Boards

Regarding replication of image type data between two database servers

Dhisni De Costa1-Dec-09 17:29

1-Dec-09 17:29

Hi,

I need some advice regarding replication of database tables which includes binary data stored as Images.

Currently the database in the server stores PDF files as binary Images in the tables. A web service is used to get data from the .NET application and to store it in the database. There is a new requirement to replicate all this data to another database in a server located at a different site.

Can someone please advise me on the following?
1. Do you think storing PDF files as binary images is the best approach or would it be good to store in a file server? Most PDF file sizes are less than 1MB, but there can be thousands of these created. Currently this is implemented as storing the PDF files as binary images in the database. But, I would like to know which would be the best solution.

2. How will the performance be if, Two-Way replication is used for databases located in different sites and which includes image data?

3. According to my understanding Two-Way replication can only be implemented using SQL Server 2005 and SQL Server 2008. Is that correct or can it be implemented using older versions as well?

4. Is there any other way than, using Two-Way replication to replicate data (which includes image types) to a database server located at a different site? Please note that image data on one server needs to be deleted after a certain action is made and it will remain on the other to gather the history.

5. What sort of an internet connection is required to perform replication between two sites?

I would greatly appreciate if someone could advice me on the above.

Thanks!

query needed to swap column values for duplicate

xcavin1-Dec-09 13:26

xcavin

1-Dec-09 13:26

userid	status	phone_number
1	0	111
2	1	111	
3	0	222
4	1	222
5	0	333
6	1	444

For the above table I want write a SQL query to swap the value of "status" column for duplicate rows. Duplicates can be identified if phone number is same. The output would look like as below (affecting only 1,2,3 and 4).

userid	status	phone_number
1	1	111
2	0	111	
3	1	222
4	0	222
5	0	333
6	1	444

Any help is appreciated. I always find it always hard to write even a simple SQL query Frown | :(

Re: query needed to swap column values for duplicate

_Damian S_1-Dec-09 13:48

_Damian S_

1-Dec-09 13:48

You will need to do it in two parts...

First, create a query that returns duplicate phone numbers, like this:

select phone_number
from TABLENAME
group by phone_number
having count(phone_number) > 1

Then, you can use some white-man-magic to reverse your statii (assuming you want to switch 0 for 1, and 1 for 0), like this:

update TABLENAME
set status = abs(status - 1)  -- This means if status is 0, you get abs(-1) which = 1
where phone_number in 
(
select phone_number
from TABLENAME
group by phone_number
having count(phone_number) > 1
)

See how we use the first query to restrict the update query?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: query needed to swap column values for duplicate

xcavin1-Dec-09 13:59

xcavin

1-Dec-09 13:59

That was real quick! thank you!

Re: query needed to swap column values for duplicate

_Damian S_1-Dec-09 14:05

_Damian S_

1-Dec-09 14:05

No worries! Thanks for your feedback...

Re: query needed to swap column values for duplicate

Luc Pattyn1-Dec-09 15:06

Luc Pattyn

1-Dec-09 15:06

No need for abs: new = 1 - old suffices.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Re: query needed to swap column values for duplicate

_Damian S_1-Dec-09 15:58

_Damian S_

1-Dec-09 15:58

True...

Transposing rows and Columns in SQL

Leo Smith1-Dec-09 8:05

Leo Smith

1-Dec-09 8:05

I have a MQ-SQL 2005 stored procedure that returns a dataset that is fine except I would like to change the rows to the columns and the columns into the rows. Is there an easy command to do this, or is pivot the only option (which is not easy)?

Thanks,

Leo T. Smith
Program/Analyst Supervisor

Re: Transposing rows and Columns in SQL

Niladri_Biswas1-Dec-09 15:54

Niladri_Biswas

1-Dec-09 15:54

Hi,
This kind of problems are called Cross Tab Queries and can be solved easily using PIVOTING (a feature from SQL SERVER 2005+).

Here is an example

Pivots with Dynamic Columns in SQL Server 2005[^]

You can even look into

a) Cross-Tab reports in SQL Server 2005[^]

b)Using PIVOT and UNPIVOT[^]

In Sql Server 2000, Pivoting was achieved using Case along with an aggregate function.

Look here

Pivot Table Data in SQL Server 2000 and 2005[^]

However, a few days back I gave a similar kind of answer in Bytes. U can have a look

Month to month yearly results[^]

Hope this helps
Smile | :)

Niladri Biswas

user import

AndyInUK1-Dec-09 6:30

AndyInUK

1-Dec-09 6:30

Hi,

I need to import 1000s of users to the database. And there values need to be added to 6-7 different tables. Doing it manually would be a nightmare.

I think i can use Cursor for this sort of stuff but never dealt with it in past. Any suggestions?

Thank you .

Re: user import

loyal ginger1-Dec-09 6:44

loyal ginger

1-Dec-09 6:44

You can first create a text file containing the user records, if you don't already have one.

Then use your database engine's import function to load the text file in to a table in one step. I can't give you the actual command to do this because I don't know what database engine you are using.

You said you need to load them into many different tables. You may need to modify the text file a bit to load them into these tables because these tables may have different structures.

If an import function is not available for the database engine you are using, you will need to write a small program to insert the records to the tables.

Hope that helps.

Re: user import

AndyInUK1-Dec-09 22:08

AndyInUK

1-Dec-09 22:08

Sql server 2008.

i have already imported the excel spreadsheet with all the users info to database. Now for instance there are different table for address field, different table for name field , differnt table for email etc. So my question is do i have to use curser so that i can insert the users value from this temp table to all the necessary table. if yes then how or if not then what's the best way ?

Thank You.
Andyyy
Reply·

Re: user import

loyal ginger2-Dec-09 3:43

loyal ginger

2-Dec-09 3:43

In this case all you need to do is to create these "other tables", and then use "INSERT...SELECT..." query to pull out the necessary fields from the temp table and insert the records to the new tables.

You can also create the tables and insert the records in one step by using the "SELECT...INTO..." statement.

Good luck!

Re: user import

Shameel1-Dec-09 6:56

Shameel

1-Dec-09 6:56

What does 'user' mean here ? Are they database users or your application users (stored in something like Users table) ?

And what database do you use ?

Re: user import

loyal ginger1-Dec-09 7:01

loyal ginger

1-Dec-09 7:01

Good point! The question did not describe that clearly.

Re: user import

AndyInUK1-Dec-09 22:08

AndyInUK

1-Dec-09 22:08

Re: user import

Shameel2-Dec-09 6:53

Shameel

2-Dec-09 6:53

If your database is properly designed and normalized, then simple update statements will work, otherwise you may have to use cursors.

Re: user import

AndyInUK3-Dec-09 0:34

AndyInUK

3-Dec-09 0:34

simple update statement works to enter one record by manually writing the details that i need to update but how to go to the next row in the table, get all the details and enter it to specific tables.
So i am not interested in this manual process. Looking for something automatic - say cursor ? but not getting how to use that.

Re: user import

Shameel4-Dec-09 6:59

Shameel

4-Dec-09 6:59

The INSERT and UPDATE statements have a syntax that can be used to update multiple rows in the destination table from values in one or more source tables. The syntax is different for each database.

These are examples for SQL Server:

SQL

INSERT INTO EmployeeMaster (FirstName, LastName, EmpID)
SELECT EmpFirstName, EmpLastName, EmpID
FROM EmployeeTemp
WHERE (...........)

SQL

UPDATE EmployeeMaster
SET EmployeeMaster.FirstName = EmployeeTemp.EmpFirstName,
    EmployeeMaster.LastName = EmployeeTemp.EmpLastName,
    EmployeeMaster.EmpID = EmployeeTemp.EmpID
FROM EmployeeTemp T INNER JOIN EmployeeMaster M ON T.EmpID = M.EmpID
WHERE (...........)

TSQL for creating SQL Server Logins and Users

David Mujica1-Dec-09 7:44

David Mujica

1-Dec-09 7:44

Check these referencs out ...

Logins
http://msdn.microsoft.com/en-us/library/ms189751.aspx[^]

Users
http://msdn.microsoft.com/en-us/library/ms173463.aspx[^]

Re: TSQL for creating SQL Server Logins and Users

AndyInUK1-Dec-09 22:08

AndyInUK

1-Dec-09 22:08

String search problem in SQL Server

hvgyufg28fh38tyr78hf1-Dec-09 1:57

hvgyufg28fh38tyr78hf

1-Dec-09 1:57

Hai all,

I am searching one string (ex.Hello-Welcome) in text file using SQL query.
its searching Hello and Welcome seperatly and giving a result , i think bcoz hyphen(-) is a word breaker
in SQL .can any one give me the idea how to resolve the problem.
i need to use hyphen in searching string, and i need a combined string search result.

how to form the query?, if any one have sample pls send me.

thanks
mahe

Re: String search problem in SQL Server [modified]

Niladri_Biswas1-Dec-09 2:04

Niladri_Biswas

1-Dec-09 2:04

Let me know if u r not looking for this -

declare @t table(record varchar(100))
insert into @t 
	select 'Hello-World' union all
	select 'code - project codes are good' union all
	select 'hello' union all
	select ' world' union all
	select ' how are u-?'

Query

select * from @t where record like '%[-]%'

Output:

record
Hello-World
code - project codes are good
 how are u-?

Niladri Biswas

modified on Tuesday, December 1, 2009 8:12 AM

Re: String search problem in SQL Server

hvgyufg28fh38tyr78hf1-Dec-09 2:27

hvgyufg28fh38tyr78hf

1-Dec-09 2:27

thanks 4 ur reply

its like same but i am searching the hyphenated string
in text file.

if u know any SQL query to searchin the string in any file pls let me know.

thanks
mahe

Re: String search problem in SQL Server

The Man from U.N.C.L.E.1-Dec-09 2:28

The Man from U.N.C.L.E.

1-Dec-09 2:28

How are your searching?
Using Like, or some varieant of FullText searching?

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.