I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
View 3 RepliesI am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU
Integer NOT
NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
BUYER(Buyer,Department)
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it?
i think; it should be implemented through transaction statements but not much aware about these
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies View Related
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2
My Problem:
It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.
Total Specification Requirements:
i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.
Any Help would be graetlty appreciated
Kind Regards
Carel Greaves
I am developing a Content management system of sorts and I want it
to be pretty flexible. I have noticed that with web content (News,
Articles, Events, FAQs) that there is a lot of similarity between these
items. They all have the same basic fields:
TitleSynopsisBodyStartDateEndDate
(for News, Articles, FAQs start and end date can be used for content
expiration. For events, they are used for the actual event dates.)
But for some types of content, I will need fields specific to that
type. For instance, I want to have a few custom settings for a "Photo
Gallery" type, like, how many rows/columns of thumbnails to display per
page.
I feel like I have 3 options, but would really appreciate your advice. I created diagrams for the 3 options, located here: http://nontalk.com/dbdesign/
Hi guys,I have a problem with my query. What i want to happen is to populate my table EV_NOTIFICATIONDETAILS (Docownerid, CurrentSentDate, LastSentDate, detailsID, GeneralRemarks) using the datas from the two different tables EV_NOTIFICATIONHEADER and EV_DOCDETAILS.I tried to create some a query but im having a error. The problem is once i insert the data from datas to the columns Docownerid, CurrentSentDate, LastSentDate the datas are stored in the database and when i tried to insert the remaining columns TO EV_NOTIFICATIONDETAILS detailsID, GeneralRemarks getting the datas from EV_DOCDETAIL it creates a new set of records in the database. Meaning it doesn't update the records in the table EV_NOTIFICATIONDETAILS but it creates a new set of records.here's my code:INSERT INTO EV_NOTIFICATIONDETAILS (Docownerid, CurrentNoticeSentDate, LastNoticeSentDate) SELECTDocownerid, CurrentSentDate, LastSentDateFROMEV_NOTIFICATIONHEADERINSERT INTO EV_NOTIFICATIONDETAILS (detailsID,GeneralRemarks) SELECTdetailsID,GeneralRemarksFROMEV_DOCDETAIL Any ideas and suggestions will be greatly appreciated.
View 2 Replies View RelatedHello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View 5 Replies View RelatedHey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2
Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou
View 1 Replies View RelatedI have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
View 5 Replies View Relatedi have such a error in my sql server db
i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS
but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters .
i am using nchar and nvarchar and ntext
My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
[URL] .....
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
I can't see what is going on, this is the situation:
I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:
I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.
So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
------------------------------------------------------------------------------------------------------------------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);
IList _tableNames = new ArrayList();
IList _queries = new ArrayList();
############
Code that prepares tables and queries
############
for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}
the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?
If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.
The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
Hi,
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1
Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA
Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1
Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11
Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11
Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
1) prospect (id, testrecordnumber, prospectID, matchscore)
2) inputfieldind (id, prspid, inputword, inputfield)
3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
Thanks
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
I have below DB structure in MSSQL for a small application which follow relational approach. Data retrieval (for Hostels) will need several Join, may be Key-Value approach where data retrieval will be fast.
Hostels
------------
HostelId,
Name,
Address,
CategotyId,
SubCategoryId,
FoodCategoryId,
LandLordId
Data:
1 H1 Address1 1 1 2 20
2 H2 Address2 1 2 2 21
3 H3 Address3 2 2 1 17
Category
----------
CategoryId,
CategoryName
[code]...
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
I have a text file which has rows 7 rows.I want to insert the data into SQL table using ssis In text file we have a column which has values as Y or N...I wanted to take only those rows which are Y...But we have only 6 rows in SQL table.It does not have the column with Y or N.
View 2 Replies View RelatedHi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies View RelatedHi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies View RelatedBelow is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
Hi, I have two tables in a data base and i'm inserting the data from one into the other...no probs. What i was wondering is, in table1 i have an column of ID numbers. In the table2 i have a matching set of ID numbers. There are 5 PersonID numbers in table one and 10 in table two, the same 5 numbers as in table1 but each ID has a duplicate with different data in the two rows.
SAMPLE:
INSERT
INTO Table1 (PersonID, level1, Level2, Level3, Level4)
SELECT PersonID,level1, Level2, Level3, Level4
FROM Table2
When i insert the data into table1 it leaves the first 5 rows of data as null and then populates the table with all the data from table two. Is there anyway of preventing these first 5 columns from remaining empty....
I hope that makes sense
Hi, I'm fairly new to SQL Server 2005.
i have a table that creates customer id's along with other data (let's call it Customer)
I would like to take the same customer_id data and import it into a different table (HQ_Customer) the new table also has different column names.
Is there a script that can be used for this problem?
Well, I think this should be an easy question, but here goes:
I'm taking data from one table and inserting it into another. According to the SQL Server Mobile Book Online, the syntax goes like this:
INSERT INTO Table1 (col1, col2) SELECT (col1, col2) from Table2
So while I can do this with my tables:
INSERT INTO sensor_stream (sensor_stream_id) SELECT (sensor_stream_id) FROM sensor_stream_temp
If I add any more columns, I get an error. Like this:
INSERT INTO sensor_stream (sensor_stream_id, sensor_stream_type_id) SELECT (sensor_stream_id, sensor_stream_type_id) FROM sensor_stream_temp
The error is "There was an error parsing th equery. [ Token line number =1, Token line offset = 98, Token in error = ',' ]"
Anyone have any ideas about why I cannot do more than one column at a time?
TIA,
-Dana
Hi,
Anyone can help me?
How to create Table A by inserting all the data from Table B?
Cheers,
Daniel.