I have two tables that have the same column names, data type and length in each. The only difference is that one is the USA ( COUNTRY) and the other is International ( COUNTRY ). I want to make these two tables into one table. I don't think that a "UNION" will do that on a permanent basis. What other options do I have?
(SQL 7 on NT Server) I want to append a table with 1200 rows, using DTS. While I know it is better to do while no one is using the database, exactly what impact will it have if I do this while the database is online? Which leads me to my next question: Exactly what operations can I do while the db is online, and what ones should I not even think of. Most of my needs are data imports and exports. I haven't found much in Online Books about this. Any help would be appreciated very much.
Hello! I've got the following procedure: ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS select A_ProspectPipeline.ID, (case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE()) then '13' else case when YEAR([Completion Date])<year(GETDATE()) then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE()) then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE()) then '1' else '13' end end)as [CY],
(case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE())+1 then '13' else case when YEAR([Completion Date])<year(GETDATE())+1 then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE())+1 then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE())+1 then '1' else '13' end end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables. Can anyone help me with this?
I have two tables, one with data for one type of ID (call it key1) and a table where this ID (call it key2) is transformed to another. It is not one-to-one match with these types of ID and I want to check those key2 cases that have two or more key1 linked to it.
It is simple enough and for the easiest check I don't even need table1 to run it as table2 has both key1 and key2 variables.
However, not all doubles are of identical worth. Table1 (that has only key1) has a year variable. I am interested in doubles that have same year variable, ie. in table1 there are two key1 cases with the same year variable that are linked to one key2 case in table2.
So in essence in table1 I have key1, year and in table2 I have key1, key2 and I am interested in those key2-cases that have more than one key1 linked to it where years are the same.
SELECT query.key2 FROM ( SELECT DISTINCT a.key1, b.key2 FROM table1 AS a JOIN table2 AS b ON a.key1=b.key1 JOIN table1 AS c ON a.key1=c.key1 WHERE a.year=c.year) AS query GROUP BY query.key2 HAVING COUNT(*)>1
I tried it joining table1 twice and fiddling around with various JOIN and WHERE clauses (the one on show being the simplest and most naive one) but the query still returns key2-doubles whose key1 cases are linked to different years. It is simple enough if you give a distinct year value in where clause (and drop second table1 join as unnecessary) but I don't want to go through all years manually one by one. I was thinking some kind of iterative loop that changes the value of the year in where clause could do the trick (and be heavy computationally) but I don't really know how to go around doing it, haven't done any loops in SQL ever.
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are : setup_zipcode, setup_category, and listing
and inside setup_zipcode it has the columns: zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns: category_id, category_parent, category_path and category_name
and the final table for listing has the columns: listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT * FROM setup_zipcode, setup_category, listing WHERE listing_category LIKE '%-$cat-%' AND listing_zip = zip_code AND category_id = '$cat' ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
This seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
I need to select Server1, Server2, Server3 and other servers that does not have correct value in Table2. Results should return server name and number that server does not have like:
Server2 | 3 Server3 | 1 Server3 | 3
Table1 is updated time to time, Table2 - static table. The best would be to avoid loop or cursor. Is that possible to get these results in one query?
I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format: Customer Name Address ---------------------------------------------------------- ABC Add1 ABC Add2
XYZ Add1 XYZ Add2
Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report). Please give me a solution to this.
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT @@SERVERNAME as ServerName,DB_NAME() AS DatabaseName, SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013, SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012, SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010, SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009 FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
I have a query that joins two large partitioned tables and depending on the values in the where clause, I can get dramatically different performance results.
The first query completed in around 7s and has 47,000 logical reads.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (5339, 5341, 5342, 943842, 943866)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The second query takes 188s to complete and has 1.8m logical reads. The only difference between the two is the value of the monitor_ids in the where clause.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (152682, 5339, 5341, 5342, 268080)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The two tables have clustered indexes on monitor_id, testtime and site_id. Comparing the execution plan, I can see why there is such a difference in performance. The second query performs a clustered index seek on the monitor_object table starting at the lowest monitor_id, testtime & site_id through the highest monitor_id, testtime & site_id. The first query performs a clustered index seek where the monitor_id, testtime and site_id equals the same values from the monitor_raw table.
My question is, how can I force the second query to use the same execution plan as the first so that I can get better performance?
One possible workaround that I could use is to execute five individual queries, one for each monitor_id and then union the results together but this would require significant code changes to my stored procs.
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id, dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note into #temp_group_sessions FROM dbo.group_session_client, dbo.group_session WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services LEFT OUTER JOIN #temp_group_sessions on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id ;
-- Drop temporary tables
DROP TABLE #temp_group_sessions; DROP TABLE #temp_services;
Declare @SQuery nvarchar(3000) set @TblName1 = '[' + @TblName1 + ']' set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description, sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1 where a.stat_month <=' + @Month + ' and a.district=' + @District +' Group by a.commodity1, b.descrip_1 order by [Amount] desc'
I'm just starting off in SSIS and have a question that I can't find an answer to...
I'm loading in a number of files (in separate Data Flows) and performing some transformations on them before merging them back together. What I'm not sure about is what I should be doing with the data at the end of each of my "Import Data From XXXX Flat File" Data Flows. Am I better off using OLE DB Destinations (or SQL Server Destinations) and saving this intermediate data to temporary tables, or am I better off using a Raw File Destinations and saving this intermediate data to files? Or is there, perhaps, a better option that I'm currently unaware of?
If the Raw File Destination is the way to go, then isn't there a maintenance issue with cleaning up all the files created? And will there not be a management issue to ensure that there is sufficient disc space available on the drive you are saving to?
I'm a bit confused and overwhelmed by SSIS at the moment, so any help would be much appreciated!
Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?
I am trying to append the current row ID to a string I am trying to insert via a sproc. I have retrieved the @@Identity and I am passing it into a class with a parameter and calling it using:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
I know this must be simple, but I am stumpped, please help!
I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".
I do not know how to append the text in a field. How is that best done?
The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.
What is the best way to do this? Can anyone give me an example?
I know this must be simple, but I am stumpted, please help!
I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".
I do not know how to append the text in a field. How is that best done?
The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.
What is the best way to do this? Can anyone give me an example?
We're running SQL 6.5 SP3, we recycle our SQL server every day, somehow starting last December, the errorlog kept appending to the previous one without starting a new log, and it keeps on growing, any one knows anywhere I should look into ? If SQL behaves properly, it should starts a new log after each recycle. I checked from Technet this problem may occur in 4.2 but I haven't seen anything in 6.5....Thanks Anthony
I need to make sure I'm doing this correctly can you help me out guys please?? This is an Appending Stored procedure it should move values from the EmployeeGamingLicense table when the status is turned into TERMINATED to the GCEmployeeTerms table. Heres what I have so far, having problems with the rest of the script getting errors
AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [TerminationDate], [Title], [DepartmentName], [TermReason], [VoluntaryInvoluntary])
SELECT ( @TM_#, @FirstName, @LastName, @SocialSecurityNumber, @DateHired, @Status, @TerminationDate, @Title, @DepartmentName, @TermReason, @VoluntaryInvoluntary) FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED' GO
DTS wizard is not allowing me to append the data to a text file. Every time I run DTS and choose the destination to be this text file (say A.txt), it overwrites the data. I have a table whose data I am dumping to a text file. I truncate the table, then get the data again into it and want to append it to the same text file. But I end up overwriting the text file with the new data.
Hello all! I have a dts package exporting a text file. I would like for the dts job to append to the end of the file each time it is ran, rather than overwriting it. Is there a simple solution for this?
When I create a db backup on our network using BACKUP DATABASE...
BACKUP DATABASE [TKKCommonData] TO DISK = N'G:SQL_BACKUPSTKKCommonDataTKKCommonData_DATA.bak' WITH NOFORMAT, NOINIT, NAME = N'TKKCommonData_DATA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
I've specified the NOINIT so that it appends rather than overwrites the database, however the database is still overwritten. Any idea how to get the database to backup and append to the set rather than overwrite the backup ?
I have subscription records for five different magazine titles that i process by looping each one though a dataflow using a for-each loop. I am using the following instructions to append to the raw file: http://blogs.conchango.com/jamiethomson/archive/2005/12/01/2443.aspx
This works fine when I pass four different magazine titles. when i try to run all of the titles(five), i get the following errors:
[Raw File Destination [131195]] Warning: The parameter is incorrect. [DTS.Pipeline] Error: component "Raw File Destination" (131195) failed the pre-execute phase and returned error code 0x80070057.
Currently I have a maintenance plan running a Full backup weekly, differential backups nightly, and log backups hourly. The log backups are all going into a single backup file - but it's hard to see what's going on behind the scenes here.
Does this file get 'reset' when the full backup is performed? Will it just keep growing indefinitely and should I be creating new files for each log backup, or manually deleting the file each week during the full backup task?
unfortunaly i have two tables where usernames are stored. in the one table the column is called 'remote_U' in the other 'remote_u' ... note the upper case
so want to generate listof all usernames and retrieve them in a single a column.
like SELECT DISTINCT remote_U FROM table1 APPEND (SELECT DISTINCT remote_u FROM table 2)
i have tried joins and groups, but is not working. can i also do an alphabetical order after appending the second select? distinct is needed, 'cos one user may have more then one entry in the table, but may be also in both tables ....
