SQL Server 2012 :: New Column In Existing Table And Uploading Data
Jan 13, 2015
Need 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
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch. 4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
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.
I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.
We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/ 1/1/2015 $150 First Transaction 1/3/2015 $24.233 Second Transaction BBBB.5678 1/1/2015 $350 Third Transaction 1/3/2015 $24.233 Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.
Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234 1/1/2015 $150 First Purchase AAAA.1234 1/3/2015 $24.233 Second Purchase BBBB.5678 1/1/2015 $350 Third Purchase BBBB.5678 1/3/2015 $24.233 Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericTable WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers!
Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.
TRUNCATE TABLE GenericImportTable; ALTER TABLE GenericImportTable DROP COLUMN RowID; BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6) ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericImportTable WHERE RowHeader LIKE '____.____%'
After i run the sql which adds some columns on one particular table.I am getting this Warning
Warning: The table 'usac499_499A' has been created but its maximum row size (9033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I got a series of the above warning message , but the coulmn wa created.
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
I have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.
If I alter a table and add a column, would this column automatically get picked up in replication?
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
Is there a way of uploading column definitions from the Flat File Connection Manager into a SQL Server table definition. Since I have over a dozen data sources to process each with around 200 columns and of course like many BI techies I have little immediate influence over the structure of these flat files. I just know that these data sources are business critical.
Judging by looking at similar threads I can't be the only one who would greatly benefit from being able to upload column definitions from the Flat File Connection Manager into a SQL Server table definition as opposed to doing this manually.
I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name] FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
I need to recover some data in a table but i'm not 100% sure the right way to do this safely.
I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?
If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?
I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.
I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
Iam currently loading the data using dts by way of exporting the tables in textfile and then importing it at the destination. But this takes hours to do that. So i would like know best way for a big database.
Please bear with me because I am brand new to SQL Server, and I may not be using the correct wording to explain everything...
I`m using Access 97 to upload a table to a SQL Server 6.5 database. I also have SQL Enterprise Manager. The allocated space on the SQL Server for my database is 20 MB and the space for my database log file is 4 MB. The first time I tried to upload a table to the database, I got the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server] Can`t allocate space for object "syslogs` in database `testpropcontdb` because the `logsegment` segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment (#1105)
I went into the Enterprise Manager and right clicked on my database and selected edit. It shows that I have 19.89 of the 20 MB free, and all 4 MB of my log space free.
When I select GET EXTERNAL DATA -> LINK TABLES from the FILE menu in Access, and link to the table I just uploaded to SQL Server, the structure (field names and attributes) is there, even though I got that error message earlier. There is just no data being uploaded.
Could someone please point me in the right direction? I`ve been reading help files and searching the net, but I haven`t figured out what is causing this error message.
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I just started with SQL and SQL Server 2005. I cantfind in my books how to add a column to an existing table. It should be a primary key, auto_increment column.
I need to add an existing shared folder to a SQL FileTable. So this is the path and I created a SQL Filetable and now I need to add it to the filetable.
I have searched the forum for the code and found one. But, I encounter a problem which i can't understand.Can anyone help me with this?I encounter a "Keyword not supported: Provider"But then i have try to take out the provider and the result is they ask for a provider.Help!! ' Create the connection object for the Excel file Dim excelConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & filepath & ";" & "Extended Properties=Excel 8.0;") excelConn.Open() ' Get the name of the Excel spreadsheet Dim schemaTable As DataTable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) Dim excelSheetName As String = schemaTable.Rows(0).ItemArray(2) ' Create the connection object for the SQL Server database Dim sqlConn As New SqlConnection(strSqlConnString) sqlConn.Open() Try ' Create new OleDbDataAdapter that is used to build a DataSet Dim excelDataAdapter As New OleDbDataAdapter("SELECT * FROM [" _ & excelSheetName & "]", excelConn) Dim excelDataSet As New DataSet ' Treat newly added rows as inserted rows, so they will all ' be inserted into SQL table excelDataAdapter.AcceptChangesDuringFill = False excelDataAdapter.Fill(excelDataSet, tablename) Dim excelTable As DataTable = excelDataSet.Tables(tablename) ' Create new SqlDataAdapter that is used to build a DataSet Dim sqlDataAdapter As New SqlDataAdapter("select * from " & tablename, sqlConn) Dim sqlDataSet As New DataSet sqlDataAdapter.Fill(sqlDataSet, tablename) Dim sqlTable As DataTable = sqlDataSet.Tables(tablename) ' Loop through each column name in the Excel DataSet and make sure it matches a ' column name in the SQL Server DataSet Dim excelCol, sqlCol As DataColumn Dim allColsCorrect, matchFound As Boolean allColsCorrect = True For Each excelCol In excelTable.Columns matchFound = False For Each sqlCol In sqlTable.Columns If excelCol.ColumnName.ToLower.Equals(sqlCol.ColumnName.ToLower) Then matchFound = True Exit For End If Next sqlCol 'CloseMonth is a field in the Excel sheet, but not in SQL DB, so just ignore If matchFound = False Then If Not (excelCol.ColumnName.ToLower.Equals("CloseMonth".ToLower)) Then Response.Write("<br>**Column '" & excelCol.ColumnName & _ "' in Excel file does not exist in SQL Server table.") allColsCorrect = False End If End If Next excelCol ' If all columns in Excel table match those in SQL table, then delete current ' contents of the SQL table and insert the data from the Excel table If allColsCorrect = True Then Dim deleteCommand As New SqlCommand("TRUNCATE TABLE " & tablename, sqlConn) deleteCommand.ExecuteNonQuery() Response.Write("- Deleted old data from SQL Server table.<br>") ' Create the CommandBuilder object to create the Transact SQL (TSQL) commands ' that are necessary to update and to insert records into the data source. Dim x As SqlCommandBuilder = New SqlCommandBuilder(sqlDataAdapter) Try 'sqlDataAdapter.ContinueUpdateOnError = True sqlDataAdapter.Update(excelDataSet, tablename) Response.Write("- Updated data in SQL Server table.<br>") Catch Exc As Exception Response.Write("<br>Error(message): " & Exc.Message) End Try Else Response.Write("<br>(The spreadsheet could not be loaded into the table " & _ "because of the above errors.)<br>") End If Catch Exc As Exception Response.Write("<br>Error: " & Exc.Message) End Try ' Clean up objects. excelConn.Close() sqlConn.Close()
Client side: 1. my DSN is CMDB.dsn on a mapped network drive(only way for now that I get a connection) 2. My client does not fail to connect, but I am having a hard time linking my database tables to each other. Through Access 03. 3. My client is remote 4. I can ping the server, 5. but I cannot telnet 6. My application is Access 2003, 7. I am on a different domain 9. I have table names that match, 10. I do not force encryption.
Server side: 1. SQL SERVER 2005 2. Standard Edition 3. TCPIP 4. The server starts fine 5. The SQL browser is enabled YES 6. Domain Account 7. Not applicable 8. NO
Platform: 1. Client runs Windows XP, Server runs Windows Server 2003
My question then is this: Does anyone know of code that can be used to import data from a CSV file to a server on a different domain? This is tricky cause the connection is not really allowing this kind of access. Which is my next question: How do you get these two applications to connect? I don't want to do it with ADP, and linking the tables I always seem to have to place the DSN on a mapped network drive to get results. Then after that I go to link tables from the SQL Server and the tables I created there are no where to be found. Why is this happening? It gives me a list of System tables but none of my
I have a table for blog comments I want to add a column that counts the number of comments for each article. existing table looks like this: CommentID ArticleID (FK) commentAuthor authorEmail comment commentDate (getDate()) I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool: "SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)" But can I add that to the table somehow so it does it automagically???
I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.
The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.
How can I force these values into the table?
I have a script transformation object in the package with this code:
If IsNumeric(Row.OCCode) Then
Row.ItemCode = CStr(Row.OCCode)
Else
Row.ItemCode = Row.OCCode
End If
ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar
