Database

Re: copuing the whole table from one server to another server

21-Jul-09 6:13

Hi Experts

iam having two sqlservers .where i want to move entire table from the server1 database with the data to anothers server(server2) datbase using query .plz guide me......

regards
venu

modified on Friday, July 24, 2009 5:55 AM

DoctorMick21-Jul-09 6:24

DoctorMick

21-Jul-09 6:24

You could use SSIS or linked servers.

Re: copying the whole table from one server to another server

venu65621-Jul-09 6:28

Re: copying the whole table from one server to another server

21-Jul-09 6:28

i know using linked servers but using query i shuld get it becoz this query is used in c#..... to execute so many tables ...

Robin_Roy21-Jul-09 17:56

Robin_Roy

21-Jul-09 17:56

Hey Venu,

Please elaborate a bit more on what exactly are you looking for inside your C# code.
Are you trying to transfer data between different databases having identical schema from inside your C# code or you want to query different database at the same time???

Robin

Re: copying the whole table from one server to another server

venu65621-Jul-09 18:26

Re: copying the whole table from one server to another server

21-Jul-09 18:26

hi robin.
actual i want to transfer the entire table structure along with datafrom local sqlserver to hosting sqlserver using c#

Mycroft Holmes21-Jul-09 19:39

Mycroft Holmes

21-Jul-09 19:39

This is usually achieved by backing up the local database and restoring it on the host.

Additionaly you can script the database and uses SSIS to move the data

Or use SQL Compare from http://www.red-gate.com/index.htm?gclid=CLDSwcDM6JsCFcItpAod-kAU5Q to move the code and Data Compare to move the data. This is what I use.

Never underestimate the power of human stupidity
RAH

Re: copuing the whole table from one server to another server

icanmakeiteasy21-Jul-09 19:32

icanmakeiteasy

21-Jul-09 19:32

hi venu,

server1 database structure is static or dynamic??

icanmakeiteasy

Re: copuing the whole table from one server to another server

venu65621-Jul-09 19:37

Re: copuing the whole table from one server to another server

21-Jul-09 19:37

Server1 is static server

icanmakeiteasy21-Jul-09 19:50

icanmakeiteasy

21-Jul-09 19:50

then if the table structure is static create a script file for server1 database.., read that file from c#, then manipulate it...

icanmakeiteasy

command to find specific value in select

dabuskol21-Jul-09 1:58

dabuskol

21-Jul-09 1:58

Help!!!

How can I find specific value from a field in a select list where the specific value varies in position.
ex;
Record1 A-'Telec:144'
Record1 B-'Telecom:145'

How to find the ":" using the SQL so I can get the 2 value in one field.. pls..

Dabuskol

Dabsukol

Re: command to find specific value in select

Ashfield21-Jul-09 2:08

Re: command to find specific value in select

21-Jul-09 2:08

Use substring to get the characters, and charindex to find the :

declare @a varchar(20)
set @a = 'Telec:144'
select substring(@a,1,charindex(':',@a)-1),substring(@a,charindex(':',@a)+1,len(@a))

will return Telec for the first 1 and 144 for the 2nd

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

dabuskol21-Jul-09 2:20

dabuskol

21-Jul-09 2:20

You are correct... Thanks for the immediate reply you save me on my last minute.

Thanks...

Dabsukol

Re: command to find specific value in select

Ashfield21-Jul-09 4:48

Link Directory Submitter (3016)

21-Jul-09 4:48

No problem

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

maliksp20-Jul-09 23:47

maliksp

20-Jul-09 23:47

Link Directory Submitter is the perfect solution for sending your sites to a multitude of pre-configured online directories. Link Directory Submitter comes with 2700+ link directories to submit to, more than half ranging from PR4 to PR7. Visit us at: http://www.clicknearn.net/3016-53.html

Re: Link Directory Submitter (3016)

Ashfield21-Jul-09 2:11

Re: Stored Procedure logic

21-Jul-09 2:11

If you want to advertise, try paying for it - and if your product was that good you would be found straight away by anyone googling.

And its a rubbish web site, full of spelling and grammatical errors anyway.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Stored Procedure logic

LucBite20-Jul-09 23:40

LucBite

20-Jul-09 23:40

I am using the following stored procedure to insert into two tables and it works fine

ALTER PROCEDURE createEmployee(@loginId VARCHAR (20), @username VARCHAR (25), @passwrd VARCHAR (25),@empId VARCHAR (20),@title varchar(5),@branchNo VARCHAR(20),@Fname VARCHAR(50),@idNo VARCHAR(13),@physicaladdress varchar(50),@province VARCHAR(50),@city VARCHAR(50),@telephone varchar(15),@cellNo VARCHAR(15),@Email VARCHAR(30),@employeetype varchar(15),@salary NUMERIC(11,2))
AS
INSERT INTO LoginInfo VALUES(@loginId,@username,@passwrd)
--SET @loginId=@@IDENTITY
INSERT INTO EMPLOYEE VALUES(@empId,@loginId,@title,@branchNo,@Fname,@idNo,@physicaladdress,@province,@city,@telephone,@cellNo,@Email,@employeetype,@salary);

But Note the @branchNo parameter to be inserted into the EMPLOYEE table. I need to get it from a table named BRANCH based on some criteria. Currently I have hard coded its(@branch parameter)its value.I've tried to use the column level Select subquery but it is not allowed. please give a junior developer some logic

ML Lingwati

J4amieC21-Jul-09 0:10

J4amieC

21-Jul-09 0:10

How about:

INSERT INTO EMPLOYEE 
SELECT
 @empId,
 @loginId,
 @title,
 branchNo,
 @Fname,
 @idNo,
 @physicaladdress,
 @province,
 @city,
 @telephone,
 @cellNo,
 @Email,
 @employeetype,
 @salary
FROM BRANCH
WHERE someCriteria

In case thats a bit confusing, you can pull the branchNo from another table while still using all your variable for the other columns to be inserted.

Avoining duplicates while joining tables

nainakarri20-Jul-09 23:19

nainakarri

20-Jul-09 23:19

Hi

I have 2 tables, one is with primary key and the other is foreign key. There is one to many relation between the two tables.

i have written query by joining them as below.

But as the 2nd table has more than 1 row, duplicates are coming in the output.

Hence i want to query in such a way that no duplicates should come. Is there any way for this?

Is it possible to write as below....

select table1.col1, (loop through all the rows in table2 and form a srting of the values) as col2 from table1

Thanks in Advance

Regards
Naina

Blue_Boy20-Jul-09 23:31

Blue_Boy

20-Jul-09 23:31

You can fix by using distinct or grouping.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

nainakarri21-Jul-09 2:02

nainakarri

21-Jul-09 2:02

No i couldnt do so.....it is not fixed. Still there are duplicates coming out...

Ashfield21-Jul-09 2:14

21-Jul-09 2:14

What you are trying to do is called pivoting. If you google for that you wll find loads of examples.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

nainakarri21-Jul-09 2:35

nainakarri

21-Jul-09 2:35

I dont know about pivot in sqlserver 2005. Thanks for giving me clue. I will check for that.

Regards
Naina

Ashfield21-Jul-09 4:43