|
I am Just Know that on the same instance name or different
if you have same instance then i write down the select query as
when u r using database db1
insert into tblabc select * from db2..tblabc
and if you are at remote location then
you have to make the link server
sp_addlinkserver
like 3 sp's are used.
first clear me about your requirements
Thank U
|
|
|
|
|
we need to move data from one db to another db both are in remote location(i.e). both db is in same server.
|
|
|
|
|
USE LINK SERVER
-- ========================================
-- Add Linked Server Access MDB template
-- ========================================
EXEC sp_addlinkedserver
@server = N'<linked_server_name, sysname, NorthwindMDB>',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'<MDB_file_path, nvarchar(4000),C:\AccessDBs\Northwind.mdb>'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'<linked_server_name, sysname, NorthwindMDB>',
@useself = N'TRUE',
@locallogin = NULL,
@rmtuser = N'<MDB_username, sysname, Admin>',
@rmtpassword = <MDB_username_password, sysname, NULL>
GO
-- List the tables on the linked server
EXEC sp_tables_ex N'<linked_server_name, sysname, NorthwindMDB>'
GO
-- Select all the rows from the Customer table
SELECT * FROM <linked_server_name, sysname, NorthwindMDB>...Customers
|
|
|
|
|
Gee...there are several ways. The best way to accomplish this feat of digital magic depends on the version of SQL Server (if indeed you are using SQL Server) you are using.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
I don't know about which database you are talking about, but
If it is in SQL SERVER, the answer is
a) Use the Backup and Restore facility
b) Attach and Detach facility
c) Use Link Server for remote connection
If it is Oracle
Use the export and import tool (exp.exe and imp.exe)
Niladri Biswas
|
|
|
|
|
Depending on your version of SQL Server you could also use the DTS Copy Wizard, bcp, or SSIS, or the detach-copy-attach method
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi
i have a problem when im using CONTAINS OR FREETEXT in WHERE CLAUSE im using MS SQL Server
EXEC sp_fulltext_catalog 'MyCatalog', 'create'
EXEC sp_fulltext_table 'MyTable', 'create', 'MyCatalog', 'idx_MyIndex'
EXEC sp_fulltext_column 'MyTable', 'column1', 'add'
EXEC sp_fulltext_table 'MyTable', 'activate'
EXEC sp_fulltext_catalog 'MyCatalog', 'start_full'
everything is fine when i executed those storeprocedures but when i run the QUERY SELECT * FROM MyTable where CONTAINS(column1,'sal')
i have two record have 'saleh'
but i got 0 records
also when i view the property of MyCatalog i got Item Count = 0
Please help i don't know why i got 0 record ???
|
|
|
|
|
Try something like this;
SELECT column1
FROM MyTable
WHERE FREETEXT (column1, 'sale') or
SELECT *
FROM MyTable
WHERE CONTAINS(column1,'sal*') (Note the * in the second query)
Reference[^] for this answer.
I are troll
|
|
|
|
|
I need to populate a new colum with data from one other column in the same table:
Table Has columns id, name, part:
Id name part
"122" "E3, SS, ABC" "NULL"
"123" "E3, BB, JKL" "NULL"
… … …
I want to have a script that fills the part column with the string to the right of ‘,’ from the name column. The result should look like this:
Id name part
"122" "E3, SS, ABC" "ABC"
123" "E3, BB, JKL" "JKL"
… … …
_____________________________
...and justice for all
|
|
|
|
|
SELECT m.id,m.name,
SUBSTRING(m.[Name],LEN(m.[Name])-CHARINDEX(',',m.[Name])+1,LEN(m.[Name])) AS Part
FROM mytable m
Hope this will help you.
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.
|
|
|
|
|
Hi man,
You solution/query is good for this context but it fails in some other cases:
e.g.
a) INPUT:
'aaa,bbb,ccc'
SELECT SUBSTRING('aaa,bbb,ccc',LEN('aaa,bbb,ccc')-CHARINDEX(',','aaa,bbb,ccc')+1,LEN('aaa,bbb,ccc'))
OUTPUT:
,ccc
b) INPUT:
'A,B,C'
SELECT SUBSTRING('A,B,C',LEN('A,B,C')-CHARINDEX(',','A,B,C')+1,LEN('A,B,C'))
OUTPUT:
,C
c) INPUT:
AQQQQQ,BFFFFF,CYYYYY
SELECT SUBSTRING('AQQQQQ,BFFFFF,CYYYYY',LEN('AQQQQQ,BFFFFF,CYYYYY')-CHARINDEX(',','AQQQQQ,BFFFFF,CYYYYY')+1,LEN('AQQQQQ,BFFFFF,CYYYYY'))
OUTPUT:
,CYYYYY
Niladri Biswas
|
|
|
|
|
Hi, I have written a Stored Procedure for doing this.
The Table Name is TBLEXTRACT_STRING with the same columns and the same values
ALTER PROCEDURE SP_UPDATELASTCOLUMN
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- VARIABLE DECLARATION SECTION
DECLARE @CNTRECORDS INT
DECLARE @NAMEVALS VARCHAR(50)
DECLARE @I INT
-- SETTING THE INITIAL VALUES
SET @I = 1
SELECT @CNTRECORDS = COUNT(*) FROM TBLEXTRACT_STRING
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROWID,* INTO #TEMP FROM TBLEXTRACT_STRING
WHILE ( @I <= @CNTRECORDS )
BEGIN
SELECT @NAMEVALS = [NAME]
FROM #TEMP
WHERE ROWID = @I
UPDATE TBLEXTRACT_STRING
SET PART = (SELECT TOP 1 STRINGVAL FROM DBO.FNSPLIT(@NAMEVALS,',')
ORDER BY COUNTER DESC)
WHERE ID = (121 + @I)
SET @I = @I + 1
END
DROP TABLE #TEMP
END
GO
And the Split function(fnSplit) is as under
ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin
Declare @newstring as varchar(100)
Declare @pos as int
Declare @i as int
Declare @c as int
set @newstring = '';
set @i = 1
set @c = 0
set @pos = CHARINDEX(@delimeter, @oldstring)
WHILE (@i != 0)
Begin
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))
set @pos = CHARINDEX(@delimeter, @oldstring)
set @i = @pos;
if (@i = 0)
Begin
set @i = 0;
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
End
End
return
End
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi i have table which is having 0.5 billion entries... In that table, i have added 5 columns for indexing.all are Non-Unique and Non-Clustered. One application is frequently insert new rows to this table...
My question is Will the INDEX cause, the INSERT operation to take more time to completed?
My small attempt...
|
|
|
|
|
sujithkumarsl wrote: Will the INDEX cause, the INSERT operation to take more time to completed?
Yes, but whether it is significant is down to testing and usage - if it makes a significant improvement in data retrieval is it worth the slow down with the insert? Its up to you to decide if the trade off is worth it.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks.... actually several SELECT as well as INSERT operation are running at same time.. What will be the wise way to manage the indexes?
My small attempt...
|
|
|
|
|
sujithkumarsl wrote: several SELECT as well as INSERT operation are running at same time
So there may be blocking on the table anyway. The only way to really find out is to test under real life load.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
In SQL Studio Manager, under the Query menu, choose the option, "Display Estimated Execution Plan" while testing your Select / Insert logic to verify that the indexes you have created are actually being used.
If an index is not being used, you may want to recosider whether or not you really need it.
david
|
|
|
|
|
Hello,
may somebody here had asked it before, but i didn't find any answer which helps me yet.
So, can somebody tell me, if i need for this software-architecture an license for mySQL?
I have one DB-Server and one "working-Server", where the Server-Application is installed. The Clients communicate with the working Server and so only the working Server has an DB-Connection.
Here a little sketch:
[DB-SERVER]*------DB-Connection----*[working-Server]------TCP-Connection-----[Client]
|---------------Server-Side-----------------------||-----------Client-Side----------|
The DB-Server is a MySQL-DB and i connect from the working Server to the DB-Server with the .NET Communicator for MySQL. Well a few people says, that i need a License, because i use the .NET Communicator, other people say, that i don't need any license, because i don't give any code or assembly from MySQL to the Customer/Client.
It's like a PHP-Webpage from the architecture, and there, the customer or the user get only the webpage and the connection to the DB holds the PHP-Server, and there... no one has to pay to MySQL.
So what's now the right answer... Do i need a license or not. Because, i could ask MySQL directly, but i think, that they would say, that i need one
Maybe for your info, my project is not open-source and i don't think, that i sell the client-app (or the user-accounts) but what if i do it?
And if i need a MySQL License, which "really" open DB could you prefer? PostgreSQL should be great...
Thanks
|
|
|
|
|
Hi I am writing the following SQL command in the MS Access and it is working in access. When I add it to the command string in C# to execute it with the OleDbSqlDataAdapter I get a syntax error. Can someone help please?
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
I used \" only in the C# part
|
|
|
|
|
jonhbt wrote: SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
try this for your string:
"USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = 'I' AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));"
|
|
|
|
|
Nothing happened U tried it that way before and still the same error. The error is in second select statement because when i removed that part it worked. So if there is a way I can replace that sql part it would solve it. The funny thing is that when running that in the access queries it works
|
|
|
|
|
LOL... that would have been pertinent information beforehand...
The second query (or more correctly - subquery) is failing because you are referencing a table that doesn't exist in the query... Consider saving the subquery as a view/query then referencing the saved view/query instead.
|
|
|
|
|
can you relate me to an example please of how this is done. Thanks
|
|
|
|
|
Save this as a query, call it something like qryMaxLogID
SELECT MAX(LOGID) as MaxLogID, UserID FROM CHECKINOUT
group by UserID
where CHECKTYPE = 'I'
Order by UserID;
Then, reference it in your existing query like this:
"select USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) inner join qryMaxLogID on userinfo.UserID = qryMaxLogID.UserID inner join INNER JOIN CHECKINOUT as main ON qryMaxLogID.MaxLogID = main.LogID;"
|
|
|
|
|
Replace CHECKTYPE = \"I\" with CHECKTYPE = 'I'
Niladri Biswas
|
|
|
|
|