I have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from db1 to db2, My table contains
employee_no INT,
date_of_attendance datetime,
present varchar(20),
shift_type VARCHAR(20),
marked_by VARCHAR(50)
I would like to know that how can I move 70 plus tables that are on sql 7.0 to sql 2012 via SSIS.I know its a two step process but what is the best route and how I can process.
View 0 Replies View RelatedI would like to move the data / findings into a perm table?The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.
USE [MMAUDIT]
GO
CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS
BEGIN
[code]....
hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedI don't know what to do here please help! I have 2 tables here and I want to move selected data in one table to another but im not sure how to do it.
Thanks in advance =)
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
Is there a way to move the Distribution database (that is currently on the publisher server) to a different server without having to reync the subscriptions.
View 0 Replies View RelatedOkay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this:
INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED)
SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED
FROM dbo.TABLENAME
WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888'
GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together
Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
We need to move duplicate data from this sheet to other table also having issue that sometime verifiedmemberID is null as well as verifiermember name is null and also having the values in BCP authorisationcode as well as FPoveridecode but transactionmode/bcpmode is 'n' and also having condition that transactionmode/bcpmode is 'y' but bcpauthorisationcode is blank.
MemberStatecodeMemberDistrictCodeURNCompanyCodeHeadMemberIDHeadMemberNamePatientID
PatientNamePatientGenderPatientAgeTerminalIDHospitalCodeRegistrationNoBlockingUserDateUnblocking
InvoiceNoDischargeInvoiceNoDischargeDescDischargeUserDateAmoutClaimedTransactionMode/BCPMode
UnspecifiedAuthCodeUnspecifiedAuthDateBCPAuthorizationCodeBCPAuthorizationDateFPOverideCode
[code]....
I have a production application that I am building some upgrades usinga second (empty) copy the database. A few of the upgrades includedchanging the datatypes of a few fields from varchar to int(all thevalues in this column are already numbers)I am not trying to move the data from the production database into theblank database using DTS and the fields that had their datatypeschanged are getting dropped by dts.Is there anyway to move this data easily through dts or through anothermethod and not have the fields with the datatype changes get dropped?I have about 60 tables so it is not as simple as just copying andpasting the data..
View 2 Replies View RelatedI have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
View 9 Replies View RelatedHey all,
Another varbinary question.
I am trying to move an image stored in a table varbinary(max) directly from one table to another programmatically.
The rest of the data is just nvarchar(50) so I just use a T-SQL select statement in the code behind and feed all of the date into an SqlDataReader, I do this becuse there is some user interaction when the data is moved, so there may be some new values updated when transfering from one table to another, so once the old and possibly new data is stored in seperate variables then I use a T-SQL insert statement to move all of the data into the other table.
Problem is I am not really sure how to handle the image data(varbinary(max)) to just do a straight up transfer from the one table to another. I get conversion errors when trying to handle the data as a string which makes sense.
Not sure what to put for code examples since I really am stumped with this, but here is what is not working.
Dim imageX As String
SqlDataReader Code - imageX = reader("imageData")
Insert code - myCommand.Parameters.AddWithValue("@imageData", imageX)
Thanks in advance,
I have an application , user will read information in Excel file and insert that data into my application, I think it spend a lot of time. I want to make a tool which move data from Excel file to a table in My application (MS SQL) automaticly. How to do it, anybody has tool or know how to do, pls help me.thanks.
View 2 Replies View RelatedI have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum
Hello All,
I do have one large table, say "emp" having 80 columns. now as the requirement changes, i have to partition the "emp" table to 8 tables.
I want all of my existing data ["emp" table data] to be there in my new tables . i don't want to delete the existing data from "emp" table.
Cal any one please help me out to resolve this issue.
Thanks
Prashant Hirani
There is a table in which the data is coming in a massive rate, what are the ways to move those data from that table to another DB in another server (kind of archiving).
View 5 Replies View RelatedI am using a WriteBack Partition to receive data from various inputs and appends any new data that I add to the WB partition.
I am able to read the data immediately in the WB partition through a Fact partition query. This is working at this point as desired.
Eventually I want to move the data from the WB partition into Fact Partition. How can I do this, manually and through automation.
I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.
find ddl below:
GO
CREATE TABLE [dbo].[GetPerson](
[GetPersonId] [uniqueidentifier] NOT NULL,
[LinedActivityPersonId] [uniqueidentifier] NOT NULL,
[CTName] [nvarchar](100) NULL,
[SNum] [nvarchar](50) NULL,
[PHPrimary] [nvarchar](50) NULL,
[code]....
Hi, I'm using sql 2005,
what i want to do is:
first, i want filter data from table A if IC_NO and passport_Number no is null
then after get the result, i want move the result into table B.
my table as below:Table A
NUMBER IC_NO PASSPORT_Number
======= ====== ===========
1. 123 A123
2.
3. 456 A456
your post really highly appreciated
thanks
how to copy a content of a table from one remote server to another,. server A does not see server B (B doesn't see A) - I cannot even ping to one from another.I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.So what I did so far is to periodically:
1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like
DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above
6. INSERT INTO
SELECT
ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );
so it works. now there is a requirement to schedule this update to run periodically and I need to implement it..
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
How to export data from MDS 2012 entity to SQL 2012 user database table ?
View 6 Replies View RelatedI have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.
View 4 Replies View RelatedNeed to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes
I have requirement of sending table data or result of an query as an email with SSIS package.
View 6 Replies View RelatedI have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
I never created table on the basis of Data model diagram . I have to create the 3 table on the basis of given Data model diagram. There are 3 tables
1.md_geographyleveltype
2.md_geographylevel
3.md_geographylevelxref
I have tried to create 2 table but unable to create 3rd table. Need to review the script of first 2 table and to create table script for 3rd table.
I have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
[Code] ....
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I am looking for a way to convert the following format into a sql table. The format it is Bib Tex.
Essentially a new row in the table would be for each entry, denoted by an @ logo and each column is denoted by an =, as you can see from the example data no one contains all the possible columns and some fields can be over two lines long.
To load this I was considering loading it into a table as each line being a row. Adding a row number, then a column counting the @ signs in order and essentially grouping each record, then for each group running through and looking for the column keywords 'author' , 'title' etc then splitting the data out into those constituent parts using substring and charindex.
@Book{hicks2001,
author = "von Hicks, III, Michael",
title = "Design of a Carbon Fiber Composite Grid Structure for the GLAST
Spacecraft Using a Novel Manufacturing Technique",
publisher = "Stanford Press",
year = 2001,
[Code] ....
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)
I get the following error: Invalid object name 'inserted'
I know I can do:
Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)
But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?