Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test.
I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server?
I'm using the Enterprise manager tool to create tables.... does it have something i can use?
Thanks
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
Hi,
I am able to do these actions interactively from SQL 2005 (not developers nor enterprise edition, just using SQL 2005 Mgmt Studio) and want to "script/batch" them so I can have them automatically run at a pre selected time.
First: I am able to delete the table by performing a right click on the table, then click Delete from Mgmt Studio SQL 2005. I verify the table is completely gone with a refresh. (I pulled the code that did this ..... DROP TABLE etc. to Notepad)
2nd: I am able to import the table (again from Mgmt Studio SQL 2005) and have saved this action as a SSIS. Execute the script and "waLa" I have all 17K rows of data. I pulled this create table code into notepad also.
Now I put the code of both of the above actions together (drop table and create table) into one SQL query and execute it. This does not give me the same results of above, instead my table is blank now.
Maybe there is a better way. The business problem I am attempting to solve: I am refreshing the data in a as/400 table weekly. I want that refreshed data to be available in the SQL2005 database without my having to press buttons first thing Monday morning. Can any one help? Thanks in advance.
Below is the Code:
USE [400kas]
GO
/****** Object: Table [dbo].[navar100] Script Date: 09/07/2007 16:09:04 ******/
DROP TABLE [dbo].[navar100]
GO
USE [400kas]
GO
/****** Object: Table [dbo].[Query] Script Date: 09/07/2007 16:12:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[navar100](
[CMPNO] [decimal](3, 0) NOT NULL,
[ARTDT] [datetime] NOT NULL,
[AUDDT] [datetime] NOT NULL,
[ARDDT] [datetime] NOT NULL,
[CCUS#] [decimal](6, 0) NOT NULL,
[CCNAM] [nvarchar](25) NOT NULL,
[CUSNO] [decimal](6, 0) NOT NULL,
[CNAME] [nvarchar](25) NOT NULL,
[SHPNO] [decimal](4, 0) NOT NULL,
[ARRCD] [nvarchar](1) NOT NULL,
[AUDUS] [nvarchar](10) NOT NULL,
[INVNO] [decimal](6, 0) NOT NULL,
[CUSPO] [nvarchar](15) NOT NULL,
[REFNO] [decimal](6, 0) NOT NULL,
[COMNT] [nvarchar](10) NOT NULL,
[SHPPO] [nvarchar](15) NOT NULL,
[AMONT] [decimal](13, 2) NOT NULL,
[AMOUNT] [decimal](24, 8) NOT NULL,
[REMAN] [decimal](13, 2) NOT NULL,
[INREG] [decimal](3, 0) NOT NULL,
[INSAL] [decimal](3, 0) NOT NULL,
[TMCOD] [nvarchar](2) NOT NULL,
[CRHLD] [nvarchar](1) NOT NULL,
[CRLIM] [decimal](13, 0) NOT NULL,
[CRDAY] [decimal](3, 0) NOT NULL,
[TCRCD] [nvarchar](3) NOT NULL,
[TEXRT] [decimal](11, 6) NOT NULL,
[R1RGL] [decimal](13, 2) NOT NULL,
[TAXAM] [decimal](13, 2) NOT NULL,
[TFRTX] [decimal](13, 3) NOT NULL,
[TFRGT] [decimal](13, 2) NOT NULL,
[TSPCH] [decimal](13, 2) NOT NULL,
[SPCST] [decimal](13, 2) NOT NULL,
[IRPFT] [decimal](13, 2) NOT NULL
) ON [PRIMARY]
Can someone help me to understand something about SQL Server? I will use Access for comparison.
Suppose you have a SQL Server database and you want users to be able to enter data into the fields just like you could with an Access database and forms. How would you set that up with SQL Server? I have noticed that you can convert an Access database into SQL Server, but you still need to use Access forms to enter data into the tables stored in SQL Server.
What if you wanted to create an SQL Server database from the ground up and set it up for user entry?
What am I missing here?
Using SQL Server 2005 Standard
The basic question
What is the best way to export data from an excel spreadsheet into a sql server table?
My Application
Getting data indicating hours worked from employee timesheets into a centralised DB, then running analysis reports on it.
The columns and datatypes in the excel sheet are as follows:
Week (int) | EmployeeID (int) | JobNum (int) | ActivityNum (int) | Hours (int)
There will be a new excel file each week that, once the employee has filled out the data, would need to be saved and exported to the sql table. The columns in the sql table are exactly the same as the excel table with the addition of a RecordID primary key column.
Can I create a macro button that they can push when they have completed their timesheet OR would it be better to tell the employees to save copies of their timesheets in a certain folder on the company network and then run a batch on all the files in the folder at the end of the day?
Or is there another more efficient solution? Would I use SSIS for this or something else?
I've never used SSIS before and am a newbie at SQL Server too.
Thanks for any help you can give me.
Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way? Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
Hi, I am creating a table called products, which sellers will post their items to. How do I create a column which auto fills according to which user posts the product? Thanks, Jon
View 4 Replies View RelatedHi all...
I noticed that when transferring tables from the local server to the remote server that some colums lost its properties ... For example, If I am sepcifying a column to have identity seed and identity increment, it is transferred to the server without these properties...
How make a carbon copy of tables from my local to the remote server ?
Thanks in advance
Hi,
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.
Hi,
I am using SQL 2000. I have two DB's one is live and the other is developement.
I need to copy the live DB over the developement DB but I can't overide certain tables.
How do I go about? Do I need to this using transact?
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedHi everyone... sorry, but I'm really new at this! I'm trying to make a table out of an imported Excel file (which is automatically generated as an all-text data type). I wrote a query that takes this excel file and puts it into the format we need (i.e., appropriate column headers, removing "garbage" characters and spaces). Is there any way to format the data going into the table by a specified datatype?
Sorry if this is simplistic or wasting your time, but I'm really stumped :o. However, keep in mind that I started learning SQL this morning and I've already gotten this far :).
I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.
I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.
What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.
Any help will be greatly appreciated!
Hello everyone, I am new to mssql so please excuse all my silly questions.
I can create databases and tables. This is fairly easy, now I am having trouble with inputting data into the database using the data entry browser (or whatever it is called to enter data directly into a table).
I usually get an error when trying to submit the entry, firstly I have a "id" field which I think I have set to auto increase (Identity is ticked, seed and increment is set to 1).
when I go to add data all fields have "Null" in them and from what I understand it allows the fields to be empty, except the primary key which is the id field in this case and I expect that to be filled in automatically like with mysql but isn’t.
Can some kind person instruct or direct me somewhere on how to enter data into a table without it failing.
Thanks in advanced for any information provided.
Stealth549
It was suggested to me to try and post this here in the sql express forum.
I am trying to query a specific field from my sse database based on two different variables. And use this information to fill a textbox with an integer.
The first variable is a combobox that is filled with data in one of the other tables in the sse database. This table has a PKcolumn that is an integer and the identity of the column. The only other column is a nvarchar(30) string (which is the actual data in the combobox)
The second variable is a numeric up down that isn't actualy using info from the database but will be a reference. It has a max value written in to the code:
if NumericUpDown1.Value > 20 Then
MessageBox.Show("This form only supports characters up to level 20")
NumericUpDown1.Value = 20
The texbox value will be found in a table that has several columns, the first column is an identity column (also the PK) then it has a column that holds an integer value (the filter criteria for the numeric up down) the next column has the value that will fill the text box, then several other columns that are irrelevant for this, and then the final column is a foreign key that is linked to the primary key of information found in the table that feeds the combo box.
I will post a picture of the database diagram to show all of this information for a better understanding of this post.
I am not looking for someone to write the code for me, but I am totally stumped as to what I need to do, If someone can help me out with this I would greatly appreciate it, thanks in advanced.
http://static.flickr.com/75/154867997_a55d3b0b9e.jpg - this is the table diagram
Hi there. i have the following excel spreadsheet that needs to be moved into a database:
ADDRESS
STREET
SURNAME
GIVEN
PHONE
CITY
POSTAL
NOTES
TERR
40
Goodless Crt
You
M
(416) 123-1234
SC
M1B 1A1
SC-123-12
59
Bellevue Ave
Doe
Jon
(416) 123-1234
SC
M1B 1A3
SC-123-13
Most of these fields are going to be created as reference tables... and the main contact list table will only include a fk to the actual value.
eg) the contact table for the above will look like:
ADDRESS
STREET
SURNAME
GIVEN
PHONE
CITY
POSTAL
NOTES
TERR
40
1
You
M
(416) 123-1234
1
M1B 1A1
5
59
2
Doe
Jon
(416) 123-1234
1
M1B 1A3
6
My question is, what is the best way to move all this data into SQL Server Express?
I've been playing around with the LinkedServers section in sql and have managed to create a connection to my excel workbook. I am able to select all worksheets as tables... and i've also been able to select all records from the main contacts sheet.
not knowing any better, my initial thoughts are to create separate sql statements for each reference table.
eg) select street from excellink...contact$
and then combine it somehow with an insert statement into the streets table in my database.
Is this the right approach? if it is, can you help with the syntax of the sql statement?
Please and thanks.
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
Hi,
We currently have about 900 stored procedures which have logic to group healthcare claims into different 'Edit Groups' depending on the logic within each 'Edit' stored procedure.
Examples of the logic for the Edit stored procedures would be something like:
Edit1: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P1, P2, P3....P345) and a diagnosis code in (D1, D2,...D123) and does NOT have a modifer code in (M1, M2, M3)
Edit2: Find all claims from same patient and same provider (matching on SubsriberID and ProviderID) which has a procedure code in (P7, P8, P9....Pxxx) and a diagnosis code in (D1, D2,...Dyyy) and has a modifer code in (M3, M4, M7), which are dated within 120 days of each other.
Do you think one of the SQL Server 2005 Data mining algorithms (Clustering or Classification or Association Rules) could play some part in this? Most of the 900 stored procs can be grouped based on logic, I mean the logic is similar for each group and only the parameters (in brackets above) vary for each stored proc within the same group.
We're totally new to data mining, although we do have some moderately complex cubes running. Which algorithm (if any) would be the most appropriate for our needs?
Thanks for any help,
JGP
Hi there,
Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
Hi all,
I've exposed my data (that exists in a proprietary format) with the ADO.NET provider interfaces (IDbConnection, IDataReader, IDbDataAdapter and IDbCommand). I can't seem to find any examples of how to get Integrated Services to hookup to this .NET code in my class library. Is it possible? My goal is for this provider to be both a destination and a source and for others to be able use IS to manipulate the data however they want.
Some links or examples would be great.
Thanks,
Dave
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:
Null_Count
Null_Percentage
Total_Record_Count
I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.
Hi all,
Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables? If yes- what would be the query structure for that? Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it
Thank you in advance.
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks
Hello, I like to know how to transfer current data displayed in my datagrid into a table in sql server. Any help is much appreciated.Thanks!
View 1 Replies View RelatedHi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example:
If I have 2 tables linked (by Foreign Key):
One table:
ITEM idITEM NAMEITEM CATEGORY (FK)
1cheese 2
And another:
Category IDCATEGORY NAME
1 household
2 food
3 general
How do I enter the load of data
Do I have to enter it as
1cheese2
or is there some way of entering it as
1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though)
I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
When executing an inline insert into command from isqw/w the command executes perfectly. Yet when I try to create a stored procedure with exactly the same insert into statement it will not create. It does not like the database.owner.table prefix on the selected rows. Any suggestions ??
Thanks.... Tom
Merging table :
--------Dummy TABLE
create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200
[Code] .....
I want Result like this :
date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600
1. Take a subset of data from about 100 tables that have multiple references to other tables in this group of 100 from a first DB.
2. Insert the above data into a second DB, a database that already has data in the 100 tables, while maintaining the correct references.
As a general approach, the best way I can think of doing this is as follows:
1. Create mapping tables for every ID that is referenced in a different table (OldID NewID)
2. Insert the old data into the new table and output the OldID and NewID into the mapping table.
3. Use that mapping data to make sure all tables that use those IDs have the new IDs in DB2.
This approach is extremely labor intensive both on initial implementation and would require a fairly substantial amount of work to maintain going forward.
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------
I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),
[Code] ....