Ive been tasked with writing a SQL query to move information from 2 tables(old1 and old2) into 1(new). table new already has all the information from old1, but also has some additional columns that are encompassed from old2. Also some of the columns in table new need to be assigned a 1 or a 0 if the information is present in old2. Here is what I came up with:
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1 May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2 ABCDE123 SER1 XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
Here's my string. I know it's way wrong right now SELECT binbox_receipt.partner_code ,binbox_receipt.link_id ,binbox_receipt_archive.partner_code,binbox_receipt_archive.link_id FROM binbox_receipt, binbox_receipt_archivewhere binbox_receipt_archive.link_id = binbox_receipt.link_idand binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'and (binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2684')I need to check 2 tables if in the first table the link_id and partner_code exist or the second table link_id and partner_code existany help would be greatly appreciated I'm a little new at this but having fun trying
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.
Table 2 Email ------- EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
I would like the result to be
Result Table ------------ Rx Id, RxDate, Name, Notes(or Subject), Type
Table 1 can be searched by date, or Name or DOB I want the result of this search to go through table 2 and return to me all emails that match thre previous results RxIds
If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005 I would like the resulting table to be RxId RxDate Name Note/Subject Type 99 1/1/2005 Person1 Note1 1 98 3/1/2005 Person1 Note2 1 98 3/1/2005 Person2 S2 2 98 3/1/2005 Person2 S5 2 98 3/1/2005 Person2 S8 2 95 6/1/2005 Person5 Note5 1 95 6/1/2005 Person2 S4 2 95 6/1/2005 Person2 S6 2
I have tried a combination of inner joins and Union with no luck any suggestions
In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.
1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?
2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?
i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?
If you have 2 tables with the same columns and you would like to see all distinct records in a result of a select and also the information in the records which table the record comes from (for instance: from table A or from table B or bot tables contain it) what should you do?
I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.
/* Original by: Bill Graziano (SQLTeam.com) Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40) */
declare @low int
select @low = low from master.dbo.spt_values where number = 1 and type = 'E'
select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved, (d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp, ((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused from sysobjects o
inner join (select distinct id, rowcnt from sysindexes where keys is not null and first != 0) ro on o.id = ro.id
inner join (select id, sum(reserved) reserved from sysindexes where indid in (0, 1, 255) group by id) r on o.id = r.id
inner join (select c.id, dpages+isnull(used, 0) data from (select id, sum(dpages) dpages from sysindexes where indid < 2 group by id) c full outer join (select id, isnull(sum(used), 0) used from sysindexes where indid = 255 group by id) t on c.id = t.id) d on r.id = d.id
inner join (select id, sum(used) used from sysindexes where indid in (0, 1, 255) group by id) i on d.id = i.id
I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo. StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.
GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.
What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.
Never assume someone knows what you are talking about.
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform. in the button click i am constructing the url : ************************************************************************************************************** http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null *************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report: when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters: UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
hi all..... iam trying to move or export database tables between two sql server 2000 databases..but i always lose the relations between the moved(exported) tables... please tell me how to export or import tables between two sql 2000 server databases without losing relations...thx for ur time... :) plz replay me quick guys...... :(
Just ran the 6.5 to 7.0 upgrade. It put all tables into a 1 large file. Is there a way to move a single table out of the file and into a second filegroup?
I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.
I am using SQL Server Management Studio Express to connect remotely to the server.
I have 2 databases. Currently all data is stored in database1
I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server
I have already generated a script which creates the schema for the tables on database2. The problem is moving the data. The web Host does not allow DTS
Here are the things they won't allow:
You can use all features of SQL 2000 (it is 2005) except for the following:
- DTS Packages - Database Replication - Mail Services - XML Support - Distributed Transactions - Database Maintenance Plans - Web Assistant - Multiple Instances
I can't for the life of me figure out how to transfer the data. I have tried copying and pasting the data and run into problems with Identity keys and constraints. I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.
Bottom-line I can't import and export directly. And I desperately need to.
I mange several large databases with multiple files and filegroups. During some maintenance I was trying to remove a filegroup and its files and noticed that I was unable to. It appears that some service broker objects were created on my user defined filegroup.
Looking at the system tables I see several system tables on my user defined filegroup
SELECT o.name +'.' +i.name FROM sysobjects o inner join sysindexes i on i.id = o.id where groupid = 2
I have about 300 tables scattered across 3 databases that I am trying to consolidate into a single database. There are other tables within these 3 databases that I don't wish to consolidate (so I don't want to copy the full database).
What are my options to move these tables to the consolidated database?
Are there options beyond, the pain and extensive run-time of copying from the DTS wizard into the new database (plus the indices won't copy will they?) or writing code that creates tables in the new database, populates them with data from the old tables and then deletes the old tables?
My understanding is sp_rename won't work with two separate databases --i.e., your destination db must be the same as the db of the object you are renaming. (if my understanding is wrong, that would seem to be the easiest way to accomplish this)
I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?
I have to move all the tables in a database from one file group to another file group.All my tables have millions of records and the indexes are in correct file group but not the tables. How much time will it take to complete the whole process ?
I am trying to move data from 8 different tables that are dependent on each other through foreign key relationship.
Basically they have millions of rows in each table and they have data for the past 5 years. I want to move data for the past 120 days and move it to 8 new tables in the same database. So I created the new tables along with their relationships. Now I need to move in the order (parent table first).
The child table has 50million rows data to move The intermediate tables have 10 mil 10mil 10mil and 40 mil 50mil and 20 mil rows to move The parent table has 10 mil rows to move
if I choose to move this data through an SSIS package what is the best way? Or is there a better way to move this data faster?
I will be doing this move only once. After that I have maintenance purge jobs that will cleanup data on a daily basis.
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
hi, I have a access database. In this database there are 10 tables and this tables are related to each other. I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
I've inherited the administration of two SQL Servers one 7.0 and the other 2000. We are looking into upgrading to 2005 so I'm gathering all the info I can on the DBs and applications using them.
Now, how can I check if a DB is currently in use? Is there a way to find out when a DB was last accessed?
By the way, I've never done DB administration so you can figure out how desperate I'm getting.
i need small information regarding how the picture image data can be stored in the database and how it can be made possible. is there for the need for any conversion.
Well i need to document what tables i have in my stored procedures. Im really new to sql as well. And the problem is if i try code i have or sp_depends for instance it will only tell me the tables that are in that DB so if I have multiple tables from different DB's they are left out. I was told that sp_MSForEachDb for go through each DB and I could try it that way. Any help or example code dumbed down for me would be awesome. This was posted in a previous thread but i though this would be a better explanation
Iam not sure if the Question is approriate for this forum but here goes....
I have just started playing around with ASP .NET and since i do not have a lot of money i downloaded and installed MSDE as my database. So far i havent been able to find any free tools to access MSDE. Iam looking for GUI tools from where i can create Databases , tables etc... I have experience with MySQL database and am looking for something like MySQLAdmin for MSDE..
