SEARCH YOUR SOLUTION HERE  

SQL stmt to transfer data between different table structure

How to write a sql statement to transfer data between two or more different table structure

My Old Table structure:
Patient
Name,DOB,Add1,Add2,Add3,Postcode,DateCreated

My New Table structure:
tbl_Patient
PatientID(auto),PRN,PatientName,DOB,Add1,Add2,Postcode,DateCreated

I would like to map the data like below:
tbl_Patient.PatientID -> auto increment
Patient.Name -> tbl_Patient.PatientName
tbl_Patient.PRN-> increase as records added into table
Patient.DOB -> tbl_Patient.DOB

Is it possible to write a sql to do the above requirement I am thinking that stored procedure might do but i have never write a store procedure before.

Posted On: Friday 26th of October 2012 12:00:17 AM Total Views:  484
View Complete with Replies




Related Messages:

Query a list of databases   (372 Views)
anyone know how to query a list of databases current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2
Referencing data from a query   (175 Views)
I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure: Quote: CREATE PROCEDURE dbo.SP_Check_Login ( @arg_UserEmail VARCHAR(255), @arg_UserPassword VARCHAR(255)) AS BEGIN SELECT a.userArchived,a.userPasswordDate FROM app_users a WHERE a.userEmail = @arg_UserEmail AND a.userPassword = @arg_UserPassword END; GO What I want to do is some conditional statements on the query results. i.e: IF (userArchived = 1) RETURN "Archived" ELSE IF (userPasswordDate < dateadd(month,-3,getdate()) RETURN "UpdatePassword" ELSE RETURN "OK" So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code
How do I restore a database   (119 Views)
, I am using SQL Server 2000 and I have been given an mydata.mdf and mydata.ldf file combo. I have been told that I can restore this database, however, I do not know how to interact with these files. Can someone please tell me how to restore this database Thank you, Crystal
How to exclude null data?   (157 Views)
I'm new to MS SQL server. Is there a select statement that will include fields that are not null
Need some advise on loading data from Excel Sheet   (114 Views)
I use DTS
money data type - no bigmoney :(   (119 Views)
Hi Guys, We are looking for advice on what to do here; we started out with our db holding ex VAT pricing now. Now the problem is the money type can only hold a few decimal places so when we are converting prices to incl vat it can't actually output the correct pricing and it comes our very ugly. Unfortunately there is no bigmoney data type so we are in a bit of an awkward position Any idea's
How to transfer data between sql and msmq automatically   (179 Views)
Hi , I am a beginner to sql server 2000 and msmq. How to make the automatic data transaction from msmq to sql. I develop the application using asp.net with c#. In the application,the user entered data is saved into the Msmq. These msmq data need to be transferred to sql database for the particular time period. Please help me regarding this. My application is too slow when I execute it due to bulk of data in the database. Please help me regard this. Is there any other way to improve the speed of the application running with sql database, please guide me.
Data transfer from mysql to mssql server   (174 Views)
Hai there !! I want to know, how to transfer data from mysql to mssql. Actually i dont know how to use mysql.Iam using sql server. As we have options in the sql server for import and export of database and transfer all the data to other database.How can we do that from mysql to mssql server through import option of mssql server. What is the type of file of the mysql database which we can import in the mssql server.(like *.mdb file from access to mssql server) .Please let me know the solution.
How to transfer data from sql server2005 to Oracle10g.   (213 Views)
How to transfer data from sql server2005 to Oracle10g, Sql server is installed in Winodws OS and Oracle10g is in Unix.and data will be automatically updated in Oracle10g.
How can i use DTS(data transfer system)?   (301 Views)
For example I have a data on cobol and I need to transfer part of that data to my SQL data base how can I tranfer that HELP ME PLZ......
transferring datetime data between databases   (228 Views)
i'm trying to transfer existing tables to a new database. the tables i'm transferring from/to don't have the same structure/field names (trying to improve what i already have), so what i'm doing is opening a connection to both databases, getting a recordset from one, creating a sql string to execute, and executing that on the new database for each record. my problem is with the datetime field. in both tables it's labelled as "DBTimeStamp" (i created it as datetime), but when i try to copy it into the new database, i get the error "The name 'Fri' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." of course, when i put single quotes like it's a string, i get the "date cannot be converted from string" error... how can this be done what do i need to do to the existing datetime to make it suitable for inserting into the new database
Script to transfer data between databases   (218 Views)
I'm a newbie to SQL Server. I'm wondering if someone could help me get started with writing a script with the following assumptions 2 databases named db1 and db2, each with a table that are named identical table1. I would like a script that would move any rows that exist in db2.table1 and don't exist in db1.table1 to be moved there. Any rows that do exist in db1 as well as db2 I would like to move the contents of one of the fields from db2.table1 to db1.table1. I hope this makes sense, and
how to transfer a database from one server to another   (245 Views)
Im sure this is somethign obvious im missing i have a database running on my local server and i want to transfer its contents and structure to a remote one on the internet. ive used the DTS wizard and selected my local dbase as the source and the server on the internet as the destination. Ive then selected all the tables and data to transfer. problem is i get lots of errors "violation of PRIMARY KEY constraint 'PK_tablename'. Cannot insert duplicate key in object 'tablename' I presume this is because within the remote database some old records remain and when copying from my database its adding these records as well as the new ones but cause the old ones are already there it causes the problem any way around this help appreciated
Combine update stmts into one   (188 Views)
How I can combine these two update statement. I tried using a If Else but I received the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression. Update Revenue set Difference_Flag = 'Yellow' From Revenue where ((TY_Revenue - LY_Revenue)/LY_Revenue)*100 = 0 go Update Revenue set Difference_Flag = 'Yellow' From Revenue where ((TY_Revenue - LY_Revenue)/LY_Revenue)*100 = 0 go
sql stmt - urgent   (122 Views)
hi everybody, as i need to display a list of departments, i want the word 'OTHERS' to b listed either top or bottom of the drop down list isit possible as now my list is in alphabet order. I m using asp for my programming pls give me some advice
Problems transferring from Ms Access to MS SQL   (241 Views)
, I am currently transferring from MS Access to MS SQL, I have imported all the data successfuly from my MS Access database to my MS SQL database. I have updated my connection details with the SQL server configuration, and the files seem to feeding most of the data through correctly, however I am experiencing some problems and could really do with some advice on what I need to watch out for... The things I do know about is dates in access are between # and in MS Sql it should be between ' and I know some of my boolean fields, rather than searching for... where field = true, this should be a 1. 2 areas which are now not working and I do not have a clue as to why are... A. The following code just does not seem to work anymore for a login page, and I have checked to ensure the MasterPanel table has been imported correctly... but it just does not seem to work anymore sqlLogin = "SELECT * FROM MasterPanel WHERE User = '" & session("user") & "' AND Password = '" & session("password") & "'" set LoginRs = connMain.execute(sqlLogin) B. The following code, again, for some reason just no longer works... it hits an error saying : Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. sqlCheckCurrency = "SELECT COUNT(*) FROM (SELECT DISTINCT Moneda FROM Bookings WHERE ADvertiserID = " & Cint(PanelRs("ID")) & ")" set checkCurrencyRs = connMain.execute(sqlCheckCurrency) Any help would be SO SO SO appreciated as I am really lost and worried that all my pages are not going to work and I just do not know why Many
Linked Server data transfer problem   (212 Views)
I've created a linked server (SQLREMOTE). I can read and write data to the remote server using QueryAnalyser. I've also created a trigger (update) in a table. If data is changing, that row should be inserted in the remote server. I'm getting the following error : 'Another user has modified the contents of this table or view, the database row you are modifying no longer exists in the database. Database error : '[Microsoft][ODBC SQL Server Driver][SQL SERVER]MSDTC on server C0458 is unavailable'. The logic of the trigger : CREATE TRIGGER trgUpdate ON [dbo].[tbl_Product] AFTER INSERT, UPDATE AS INSERT INTO SQLREMOTE.edison.dbo.tbl_Operator (operator) SELECT ID, Brandcode, description, Trays, Packs, Weight FROM inserted Can anyone tell me what I'm doing wrong !
Single SQL stmt in asp   (142 Views)
Please help. I currently have an .asp page that take way to long to complete because of my sql. Currently I am executing 3 stmts with a do/loop to get this data. I would like to count how many records are Y, but only if the latest entry for that person is a Y. DB: Fields - RecNo (Unique), UserNo (Unique to user), Agreed. RecNo UserNo Agreed 1 111 Y 2 222 Y 3 111 N 4 333 Y 5 111 Y 6 444 Y 7 444 N 8 333 N 9 555 Y 10 222 N Results should be 2 - User 111 and 555. This is a large db and getting biggier. There's probably a way to declare var in a procedure and possibly if stmts, but I just can't figure it out.
Help! how to transfer MSSQL 2000 to MSSQL 2005   (250 Views)
hi there! I hve already backed-up my database in MSSQL 2000 and I want to transfer that one in my MSSQL 2005.. anyone here got an idea how to do it
How transfer access tables to sql server 2000 ?   (294 Views)
Hi . I got a db in access and want to transfer it to sql server with all its reletionship and connect my access bounded forms to sql server . i be happy if some one show me how to transfer my access 2000 tables to sql server 2000 and make connection to sql sever tables from bounded access 2000 forms