How Can I Insert A Column Using A Script (instead Of Appending)?
Jul 20, 2005
Hi,
I recently had to add a new column to a table that resides off-site,
in a customer's environment. Previously, the table had around 300
columns, all of which were in alphabetical order.
I found that the ALTER TABLE command appeared to be only capable of
appending a new column, but wasn't capable of inserting one into the
middle of the table.
Since alphabetical order wasn't a requirement, but just a "nice to
look at" feature, I appended it anyway, and forgot about it.
Anyone know of a clever way to do this though?
Curiously,
Warren Wright
Scorex Development Team
View 1 Replies
ADVERTISEMENT
Apr 17, 2008
I have a simple select statement. What I need is to append a 2 digit number infront of the relationship_type_id.
select distinct
customer_id,
acct_id,
parent_flag,
relationship_type_id
from customer_account sca
inner join account act
on sca.acct_id = act.account_number
the logic is that
If primary flag = 0 then 1st two digit of relationship_type_id = 10
else 1st two digit of relationship_type_id = 00
For Example:
Primary Flag Relationship_type_id After appending
Relationship_type_id
1 5 005
1 3 003
0 3 10
1 5 005
0 3 103
Can anyone help me with that?
Thanks
View 8 Replies
View Related
Mar 13, 2008
Hello guys,
here is my issue.
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2
1,2
3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this-
Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2
1,2
3,4
Col1,Col2
5,6
7,8
Can anyone help me to get the file as shown in the highlighed
Any help would be appreciated .
Thanks
View 3 Replies
View Related
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
May 3, 2007
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies
View Related
Jul 27, 2007
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
View 2 Replies
View Related
Sep 18, 2014
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
View 2 Replies
View Related
Feb 27, 2008
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.
Beli
View 4 Replies
View Related
Jun 13, 2012
I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?
View 2 Replies
View Related
Mar 3, 2008
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
View 3 Replies
View Related
Jun 1, 2007
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 ?
View 2 Replies
View Related
Dec 19, 2005
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();
AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN);
I would like to add the current row ID to "FN" like:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN +
ID);
Thanks in advance,
Justin.
View 1 Replies
View Related
Feb 9, 2000
Can anyone tell me the best ways (DTS, BCP, Insert?)
to append data from one table to another
(both tables have identical structures)?
Thanks
Ziggy
View 1 Replies
View Related
Jul 9, 2004
hi there...
plz guide me how can I append the data from two different tables in a new table.
plz help me...
Take care
Regards
SyedShan
View 1 Replies
View Related
Jan 24, 2004
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?
View 3 Replies
View Related
Jan 24, 2004
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?
View 2 Replies
View Related
Feb 2, 2007
Hi i'm trying to append data from one table to another both with exactly the same table structure, using a SProc. The code i have used is
CREATE PROC sp_append_DB1_to_DB2
AS
INSERT INTO DB2.dbo.datatable1
SELECT *
FROM DB1.dbo.datatable1;
GO
i run this and get an error message-
Server: Msg 547, Level 16, State 1, Procedure sp_append_DB1_to_DB2, Line 5
It appears i'm having a problem copying into a foriegn key field, how do i get around this?
View 1 Replies
View Related
Oct 4, 2007
Hi,
can any one say is it possible to add two rows of a table and store it in a single row in Sql server.assume the row consist of only one field
View 7 Replies
View Related
Mar 8, 2006
Hi,I wanted to know if it is possible to do to append two tables into athird table.For example, consider these two tablesTable 1--------------------------------------------------------------| Part_num | Prt_name | Desc1 | Desc2 |--------------------------------------------------------------| PRT1 | PartA | abc | xyz || PRT2 | PartB | def | aaa || PRT3 | PartC | ghi | bbb |--------------------------------------------------------------Table 2---------------------------------------------------------------| Cat_num | Cat_name | SDsc1 | SDsc2 |---------------------------------------------------------------| CAT1 | CatalogA | abc | xyz || CAT2 | CatalogB | def | aaa || CAT3 | CatalogC | ghi | bbb |---------------------------------------------------------------Now, I want to append them to get this :Table 3-----------------------------------------------------------------------------------------------------------------------------| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |SDsc1 | SDsc2 |-----------------------------------------------------------------------------------------------------------------------------| PRT1 | PartA | abc | xyz ||| PRT2 | PartB | def | aaa ||| PRT3 | PartC | ghi | bbb ||| | | | | CAT1| CatalogA | abc | xyz || | | | | CAT2| CatalogB | def | aaa || | | | | CAT3| CatalogC | ghi | bbb |----------------------------------------------------------------------------------------------------------------------------The blanks in Table 3 are , well ,blank.Now can it be done or not?Awaiting your replies,Regards,Shwetabh
View 13 Replies
View Related
Dec 10, 2007
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?
Thanks.
View 3 Replies
View Related
Jan 19, 2000
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
View 3 Replies
View Related
Aug 9, 2004
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
CREATE PROCEDURE [insert_GCEmployeeTerms_1]
(@TM_#_1 [int],
@FirstName_2 [nvarchar](50),
@LastName_3 [nvarchar](50),
@SocialSecurityNumber_4 [int],
@DateHired_5 [datetime],
@Status_6 [nvarchar](50),
@TerminationDate_7 [datetime],
@Title_8 [nvarchar](50),
@DepartmentName_9 [nvarchar](50),
@TermReason_10 [ntext],
@VoluntaryInvoluntary_11 [nvarchar](50))
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
View 14 Replies
View Related
Sep 8, 2004
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.
Kindly let me know where I am going wrong.
View 1 Replies
View Related
Mar 23, 2004
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?
Thanks
View 3 Replies
View Related
Aug 13, 2013
Aim – Append the results from #tablecut on to table #Deletedupes
Table name #tablecut
Columns within #tablecut
Records = 186
Fdmsaccountno
Seller_Code
mcc_code
chain_chain_no
Dba_name
se_number
pca
post_code
rolling12
Segmentation
Table name# #Deletedupes
Columns within # Deletedupes
Records = 11195
Fdmsaccountno
Seller_Code
mcc_code
chain_chain_no
Dba_name
se_number
pca
post_code
rolling12
Segmentation
End result 11389 records
Put into a table called #Results
View 1 Replies
View Related
May 9, 2007
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 ?
Programmers HowTo's -- http://jhermiz.googlepages.com
View 10 Replies
View Related
Jun 26, 2006
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.
Any ideas?
Thanks.
Frank
View 25 Replies
View Related
Sep 6, 2007
Quick question regarding log backup files.
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?
Thanks
View 1 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Nov 27, 2014
I came from DW admin background and new to SQL programming. I have 3 tables:
CUSTOMER - cust_id, cust_name, cust_state
SALES_HEADER - sales_id, cust_id, sales_amount
SALES_DETAIL - prod_id, sales_id, prod_name, prod_price
I am trying to insert into SALES_HEADER table from CUSTOMER and SALES_DETAIL tables. Here are the SQL queries that I am trying to insert.
insert into dbo.SALES_HEADER (SALES_ID,SALES_AMOUNT)
select SALES_ID, sum(PROD_PRICE)
from BOBJ.dbo.SALES_DETAIL
group by SALES_ID;
The above query is working good & to get cust_id from CUSTOMER:
Update dbo.SALES_HEADER
set CUST_ID = (select CUST_ID from dbo.CUSTOMER
where dbo.SALES_HEADER.CUST_ID = dbo.CUSTOMER.CUST_ID)
where CUST_ID is null;
This query is not working & getting message - 6 rows updated successfully.
But using select * from dbo.SALES_HEADER, CUST_ID is still showing as NULL.
What did I do wrong and is there a way to write single query rather than two queries.
View 3 Replies
View Related
Aug 29, 2001
(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.
Rob
View 2 Replies
View Related
Apr 12, 2006
hi,
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 ....
View 4 Replies
View Related
Jul 22, 2004
I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement
UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0)
I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement.
Thanks,
SBR
View 1 Replies
View Related
