HELP ....difficulty In Insert Of 2 Tables Data With Id

Jun 3, 2007

 Hello frdz,
             I have two tables in sqlserver 2005.
             I have created the stored procedure for insert,update data.I m creating my application in asp.net with C#
 
 Table-1 CUSTOMER

Fields:

customerid int identity,
cardid int,
customername varchar(20) not null,
address varchar(20) not null,
city varchar(20) not null,
emailid varchar(20)



Table-2 CARD

Fields:

cardid int identity,
cardtype varchar(20) not null,
carddetails varchar(20) not null



INSERT INTO CUSTOMER (customername ,address,city,emailid)
VALUES (@customername,@address,@city,@emailid)
SELECT @customerid = SCOPE_IDENTITY()

/* HELP HERE NOT ABLE TO GET DATA OF CARD */
SELECT @cardid = cardid from CARD where customerid =@cardid  Pls tell me how to insert the data ...There is only one cardid for only one customerid both should be unique no duplication....One customer can have only one and one card...  


 

View 5 Replies


ADVERTISEMENT

Having Difficulty With FormView, Stored Procedures And Insert Parameter

Feb 17, 2005

I setup the databse and Visual Web Developer to use
stored procedures when the insert command is used. The
database also uses the field UserName that I pass using a
SessionParameter within the InserParameter block from the
Membership.GetUser.Username from the aspnet_ tables.

My difficulty is when using the "Formview" as the body to
with which to insert (I wanted the design versatility of
FormView) I get an error: "system.formatExpression: Input
string was not in a correct format" when I leave the
textboxes empty and invoke the insert command. I first
assumed that the bound textboxes are not being converted
correctly when left blank, so I used the
ConvertEmptyStringToNull=True, with no success. I then
coupled that with the Type=[correct format] still with
the same error. Alas, my final attempt was to set
defaults in the "ALTER PROCEDURE" within the stored
procedure itself.

Any help would be most appreciated. Thanks.
.

View 2 Replies View Related

Difficulty Tranferring Data Using DTS

May 25, 2006

I am trying to transfer data into SQL Server from Excel using DTS.

I get error for a particular field:

Data for Source Column 3('Col3') is too large for the specified buffer size.

I gathered from other resources, that this error is due to some record in the excel sheet for which that particular field is too big.
and it reads only the first 8 records to determine a data type.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q281517

the fix is to chnage the registry value, as mentioned in the above link. But hat is not an option for me, as that slows down the process a lot.

Does any body else has any other suggestion for this?

I have an idea, that worked, but for that I have to manually insert a record on the top in the excel sheet, with a big size data in that field (which gives problem), and then run DTS, later delete that record.

To automate this idea, I tried to do a DTS from a dummy table (with a record with big data in it) to this excel sheet, but it puts the record at the bottom of the sheet. Is there any way to put this record on the top?

Any help in this regard will be greatly appreciated.

Thanks
Nicky

View 3 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

How To Insert Data In To Two Tables?

May 12, 2005

hi,
i have got a problem, iam creating an asp.net application, but i pretend to insert data in two different tables, but i have no ideia how to do this.Can you help to solve this problem? please..........

View 3 Replies View Related

How To Insert Data In To Two Tables?

May 12, 2005

hi,
iam creating an asp.net application the database is created on the SQLSERVER, but i have a problem, i pretend to insert data in to 2 tables and i have no ideia how to do this. Can you help me to solve this problem? Please.....

View 1 Replies View Related

Why Can Not I Insert Data In My Tables

Jun 6, 2006

Hi I was watching this beginner webcast
Lesson 8:  Obtaining Data from a SQL Server 2005 Express Edition Database
http://msdn.microsoft.com/vstudio/express/visualcsharp/learning/#beginners
I tried to do the same exact thing.
But when I tried to insert data in the newly created table I got the follwoing error window:
Invalid value for cell (row1, column 2)Then changed value in this cell was not recognized as valid..Net Framework Data Type: Byte[]Error Message; You cannot use the Result pane to set this Field data to values other than NULL.
Type a value appropriate for the data type or press ESC to cancel change.
 
Any Idea why thing happing.
 
 
 

View 3 Replies View Related

Insert Data Into Tables

Jun 13, 2006

Need some help. In my db I created 3 tables. 1- tblClient 2- tblCategories 3- tblClient_Cat ( colums: 1) client_id 2) cat_id).What I'm trying to do is create a sp to insert data from a web form into the tblClient and into the tblCat using the same sp. this is all I got so far.CREATE PROCEDURE [usp_insetClient](@ClientName varchar(25),@Contact varchar(100),@Tel char(13),@Fax char(13),@WebAdd varchar (250),@email varchar(250),@CatID int) ASBegininsert tblClient(Client_name,client_contact,Lient_tel,client_fax,client_webadd,client_email)values(@ClientName,@Contact,@Tel,@Fax,@WebAdd,@email)select @@identityinsert tblclient_cat(cat_id,client_id)values(@Catid,@@identity)(I tried to use this  sp but didnot work)as you can see I need help inserting the client_id just created by the db into the tblclient_cat since the cat_id is passed from the web formThanksErnesto

View 1 Replies View Related

How To Insert Data Into 2 Tables ?

Nov 16, 2007

hi all ,
I need to get data from textboxes and insert data into 2 tables. I tried as following code but it didn't work..
TextBox6 is primary key for Book and it's auto number field at access database ( So, if i run "cmd = New OleDbCommand(str, cn)" then data will added to other colume and the primary key will auto create .) .
But I need to insert the data into Inventory too using same button . B_ID is foreign key in Inventory to connect with Primary Key (which is Book_ID) at Book. How can I insert data into those 2 table using vb.net ? Thanks in advanced and will appreciate if you could provite the code sample too .


---
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim cmd2 As OleDbCommand
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: estdb.mdb;")
cn.Open()
str = "insert into Book(Title,Arthur,Publisher,Year)" + " values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"
Dim str2 As String = "insert into Inventory(B_ID,Price,Quantity)" + " values (" & TextBox6.Text & ",'" & TextBox7.Text & "','" & TextBox8.Text & "')"
cmd = New OleDbCommand(str, cn)
cmd = New OleDbCommand(str2, cn)
cmd.ExecuteNonQuery()
cmd2.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error: " & ex.Message) 'catch error
End Try
cn.Close()
Call EnableReadOnly()

End Sub

View 11 Replies View Related

Data Insert In Tables

May 8, 2008

I have timecard records need to be inserted into the EMP_TC table automatically. This job should run every day to create EMP_TC records for the full current month for any ASSIGNMENT_ID€™s where ASGNMT_END_DATE is either null or greater than the run date of this job and PRD_START_DATE is in the current run month.
PRD_START_DATE is always Sunday, PRD_END_DATE is always Saturday. For example, if an employee starts a new project on 4/8/2008, the first timecard record in EMP_TIMECARD for that EMP_ASGNMT_ID should be 4/6/2008 €“ 4/12/2008. If PRD_END_DATE is null then the job would populate the following 4 records for the EMP_ASGNMT_ID:
i have currently stored procedure doing this but i had to specify the dates manually .how could i automate this process.
i can avoid stored procedures needed but i am really confused on what logic to apply to it.
We need to pick the assigment id and dates automatically from employment assignment table.
Assignment id is nothinb but a identity coloum please advise
please advise




View 3 Replies View Related

Insert Data In Multiple Tables

Oct 10, 2007

hi. can anyone help me, please.
i am using vwd2005 express edition and sql server 2005 express. i want to insert data in multiple tables at once. the tales are linked to each other through primary key and foreign keys. for example i have one table with a primary key. when i add data to it, i have to retrieve the id of the newly inserted record and then introduce this id in another  table as a foreign key. i have 10 tables linked in this way, and only one form to add data to the database.
can you help me, please? i'd be very greatful. thanks.

View 3 Replies View Related

Insert Data Into Related Tables

Mar 2, 2008

i am using ms sql server 2005 express for the following:

T1 = {t1.c1(PK), t1.c2, t2.c1(FK)}

T2 = {t2.c1(PK), t2.c2}

where T1 and T2 are tables from the same database, and tn.cn are CoulmnN of TableN
T1 and T2 are linked by the relationship t2.c1

i need to know how to add records to T1 and T2 using ms sql.

record for T2 needs to be added first then retreive t2.c1 from T2 to add record to T1 with t1.c1, t1.c2 t2.c1

please help, thanks

View 2 Replies View Related

Compare And Insert Data On Two Tables

Apr 29, 2008

Hi everybody...
looking for a way to compare and insert data on two tables..

I have two tables

Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com

tbl_email2
emailID email
1 info@sample1.com
2 info@sample4.com
3 info@sample5.com

I'm trying to compare tbl_email2 (email filed) with tbl_email1 (email field)
if the record exist it it does nothing if not it adds the email field in tbl_email1
the result would be

Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com
2 info@sample4.com
3 info@sample5.com

thanks

View 4 Replies View Related

SQL 2012 :: Insert Data Into Two Tables

Jan 3, 2015

I have excel file like below

sno name fname empid epsal
1 raju ravi 123 40000

Upload Import Excel Sheet data into SQL Server using ASP . in different tables like..

table_a
sno name fname
1 raju ravi

table_b
empid empsal
123 40000

View 9 Replies View Related

Insert Data In Multiple Tables

Apr 27, 2007

Hi there!!! Trying to figure something out, I have searched this forum but no answer to my dilemma.

I have three tables on a database that I have to insert new data and update the old one. The structure of the tables is like this:

Table1
custid int primary key,identity
fname varchar(30)
lname varchar(30)

Table2
fileid int primary key, identity
ssn varchar(11)
custid int foreign key

Table3
statusid int primary key,identity
title varchar(18)
fileid int foreign key


This is very general but that's the idea. Now I receive a text file with the necessary info, I've already parse and break down the file into the correct fields.

Now for my question, how I insert the relevant data onto the tables from this one parsed file? and also how I go about inserting the primary keys from the different tables onto the foreign keys of each tables?

I tried relationships and key indexes, but it just spew a bunch of errors, that I'm investigating.

Any ideas, pointers, tuts, something I'm missing?

BTW I'm using sql 2005 express and VB.net


Thanks in advance for any help.


_José

View 1 Replies View Related

Need To Insert Data Into 2 Tables, Relational Having Problems

Mar 7, 2007

Ok, I have a page on my website where we can add products to our database.  We are a music store, and most products have different versions or colors.  I've created 2 tables, Products and Subproducts.  The products table may hold info like Fender Stratocaster, and the subproducts would hold colors (Blue, Sunburst, etc).  The subproducts table has an integer field called MainProductID, which is linked to the mainproducts table field RecordID. So far the page uses a wizard where if first creates the main product using an sql datasource.  After the data has been added to the main products table, my page gives you the opportunity to add different sub products.  The problem I am having is actually feeding in the RecordID from the main products table to my insert parameter on the sub products data source.  This is what I have tried so far: There is a formview on the page that is bound to the main products table, after the entry is created I can physically see the info on my screen, so I know the data is there at my disposal SubProductsDataSource.InsertParameters.Add("@MainProductID", Formview1.datakey.item("RecordID"))SubProductsDataSource.Insert()Using this adds the data to the table, but the MainProductID is nullalso is there a cheap little way to refresh a page, because when I upload the product images I have it go to the next step where you are supposed to be able to see the images you uploaded, I don't see them which makes me think that the page is loading faster than the images are uploading.  Thanks   

View 1 Replies View Related

Insert Same Data To Two Different Tables With One Submit Button....

Apr 8, 2004

I would like to insert a set of data to my database to 2 different tables.
what will be the SQL Statement for such ? Is it possible to do so?

for example, a webpage contain 2 textbox which require user to key in data. Once click submit button. the info will saved to 2 tables....

View 3 Replies View Related

How To Update Stats Of Tables When Insert Data Into It

Feb 17, 2012

How do i update the stats of tables when we insert data into it. I believe Auto stats update happens only when 500+ 20% of the rows are changed for a table. Once we insert say some 1000 records in to a particular table the query time takes too long (more than 1 min). The same query executes faster once i manually update the stats.

View 3 Replies View Related

T-SQL (SS2K8) :: INSERT Data From Multiple Tables

Dec 1, 2014

I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.

I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.

insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;

It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.

View 3 Replies View Related

Insert Random Data Into Database Tables

May 28, 2008



Hi all


I have blank tables and i want to fill tables with data but i have many tables and i need script or whatever automated this operation like loop

thanks in advance


View 5 Replies View Related

Transact SQL :: Insert Data Into Two Tables At A Time

Apr 20, 2015

i have these two tables

create table [dbo].[test1](
[test1_id] [int] identity(1,1) primary key,
[test2_id] [int] not null
)

[code]...

I want to insert the data into two tables in one insert statement. How can i do this using T-SQL ?

View 6 Replies View Related

Insert Data Into Parent And Then Child Tables

Nov 16, 2006

hello -

i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.

For example, I have a table Users. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDepartment - it has a foreign key for the user id and a foreign key to the department being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.

I looked at multi-cast, but I don't know/think that this will work for me.

does anyone know of a good example or article like this?

thanks
- will

View 8 Replies View Related

How To Insert Data Into Two Tables Simulatneously, Using Stored Procedure?

Jul 4, 2007

 
Hi all,I have heard that we must insert into two tables simultaneously when there is a ONE-TO-ONE relationship.
Can anyone tell me how insert into two tables at the same time, using SP?
Thanks
Tomy

View 1 Replies View Related

Insert, Update && Delete On Two Tables With Same Data Structure...

Jun 30, 2006

I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.

Following Points to Consider.

1. Both tables are in the same database.

2. Table1 is using for data entry & I wants the same data in the Table2.

3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.

4. I need real time data insert, update & delete on Table2.

I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.

I want to understand how can I impletement this successfully without any ambiguity.

I have attached data structure for tables. Thanx...

View 10 Replies View Related

Merge Data From Two Tables Into One Table - No Updates/only Insert

Sep 10, 2007


Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.
I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:
Table A
--------------
1 test test1 test2 test3 test4 test5
2 test test6 test7 test8 test9 test10

Table X
------------
1 test test1 test2 test99 test4 test5
2 test test98 test97 test 96 test95 test94
--------------------------------------------------------
Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..
The new Table A should look like

NEW Table A'
-----------------

test test1 test2 test3 test4 test5
test test6 test7 test8 test9 test10
test test98 test97 test 96 test95 test94


------------------------------------
I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

View 6 Replies View Related

SQL Server 2012 :: Open EDI File And Insert Data Into Tables

Sep 27, 2012

Is there a way to open EDI file in SQL Server and insert the data into tables?

View 9 Replies View Related

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

Oct 31, 2015

Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?

For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.

View 3 Replies View Related

Create Tables And Insert Data In Sql Server Mobile On Dekstop

Mar 2, 2006

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

View 8 Replies View Related

Insert Data From Flat File Into Serveral DB Tables Having Many-to-many Relations

Nov 4, 2007

Hi everyone,

I am new to SSIS and I thought maybe someone would give me tips for solving the problem I am facing.


Overview:
I want to insert data contained in a flat file into several DB tables, which have N-M relations.

For illustration, I would explain the problem on a very simple DB:
1. The database contains the following 3 tables:
EMPLOYEE (EMP_ID, EMP_NAME)
PROJECT (PROJ_ID, PROJ_NAME)
EMP_PROJ (EMP_ID, PROJ_ID) , where EMP_ID and PROJ_ID are foreign keys referencing records in the EMPLOYEE and PROJECT tables respectively.


2. Each entry in the falt file contains the following data:
EMP_ID, EMP_NAME, PROJ_ID, PROJ_NAME


3. In SSIS, I have created a Data Flow Task containing:
- a path from a Falt File Source to an SQL Server Destination (Table: Employee)
- a path from a Falt File Source to an SQL Server Destination (Table: Project)
- a path from a Falt File Source to an SQL Server Destination (Table: Emp_proj)

Note: I used SQL Server Destination, because I need to import a huge amount of data and I read that this component performs better than the OLE DB Destination!


Questions:
1. I would like to eliminate EMP_ID and PROJ_ID from the Flat File Source. Instead, I would like these fields to be generated automatically upon insertion.
a. How can I do this and propagate the generated key among the different paths, which I have explained previously?
b. Can I first generate the two keys somehow then the parallel insertions into the different tables should start using the generated keys?


2. Is my solution correct in the first place? Or is there another better way for inserting data which belong to N-N relations?


Thanks in adavance,
Samar


View 5 Replies View Related

Correct Way To Insert Data Into Multiple Tables (Stored Procedure)

Nov 3, 2007



Hi

I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first:
I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).

Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like.
To add a simple image to a given album, I am trying to do the following:
* Retrieve name, description from the UI
* Insert a new row into images with this data
* Get the ID from the newly created row
* Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.

I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.

Any help is appreciated ... even if it means telling me that I am doing something terribly wrong

View 9 Replies View Related

How Do I Export Data From MS SQL Server Tables To Text File With 'insert' Statements

Jan 3, 2005

I am trying to take an entire MS SQL database and put it in an sql file. I have succesfully copied the tables into an sql file by highlighting the tables in enterprise manager and choosing 'generate sql script'.

That gives me the structure, but now I would like the data (in insert statements). I have looked in enterprise manager's export wizard and sql analyzer to no avail. There seem to be a lot of options for exporting data except this one! Please point me in the right direction.

At the end of the day, I would like to be able to put everything in a text file. Then, should I have problems, I can just copy my text into query analyzer and have a brand new database.

Thank you in advance.

View 4 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Insert Data Into A Table From Two Tables Into A Single Table Along With A Hard Coded Value?

Feb 9, 2012

I'm trying to insert data into a table from two tables into a single table along with a hard coded value.

insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)

I get the following-

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Do I need to use a cursor?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved