insert data into two tables
-
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
plz write qurie
-
2 queries and 2 passes is the best way to do this I think. You could try the Import Wizard in SSMS as a starting point.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
shashianireddy (1/3/2015)
i have excel file like belowsno 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
plz write qurie
That disconnects the salary from the employee because you've not identified anything in common between the two tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
i give example only..
how to insert two tables in single quarie ...like above
-
shashianireddy (1/3/2015)
i have excel file like belowsno 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
plz write qurie
Two tables, two insert queries.
-
shashianireddy (1/3/2015)
i give example only..how to insert two tables in single quarie ...like above
As the others have said, two tables, two queries. It can't be done in a single query.
To load a spreadsheet in T-SQL, you need to have the ACE drivers loaded and then we can start. Please see the following URL for where to get the drivers from and how to install them.
http://www.microsoft.com/en-us/download/details.aspx?id=13255
And it would be nice if your examples were accurate so we get it right the first time. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
shashianireddy (1/3/2015)
how to insert two tables in single quarie ...like aboveIt's possible to do two inserts in a single statement like this:
INSERT INTO dbo.table_b (empid, epsal)
SELECT empid, epsal
FROM
(
MERGE INTO dbo.table_a
USING dbo.excel AS e ON 1=0
WHEN NOT MATCHED THEN INSERT (sno, name, fname) VALUES (sno, name, fname)
OUTPUT e.*
) t;
Not sure I would generally recommend that approach over using two INSERTs. MERGE probably isn't going to give you the best execution plan and IMO two INSERTs also has the advantage of greater clarity and simplicity.
-
<Head-Desk> Thanks, sqlvogel. I keep forgetting about MERGE because I'm still stuck in a 2005 world (working on upgrades to 2012). And, I agree... I'd still probably use two Inserts instead of Merge.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
sqlvogel (1/3/2015)
shashianireddy (1/3/2015)
how to insert two tables in single quarie ...like aboveIt's possible to do two inserts in a single statement like this:
INSERT INTO dbo.table_b (empid, epsal)
SELECT empid, epsal
FROM
(
MERGE INTO dbo.table_a
USING dbo.excel AS e ON 1=0
WHEN NOT MATCHED THEN INSERT (sno, name, fname) VALUES (sno, name, fname)
OUTPUT e.*
) t;
Not sure I would generally recommend that approach over using two INSERTs. MERGE probably isn't going to give you the best execution plan and IMO two INSERTs also has the advantage of greater clarity and simplicity.
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insert;-)
Find it hard to imagine a more efficient way for this, consider the sample code below
😎
USE tempdb;GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SECOND') IS NOT NULL DROP TABLE dbo.TBL_SECOND;
IF OBJECT_ID(N'dbo.TBL_FIRST') IS NOT NULL DROP TABLE dbo.TBL_FIRST;
CREATE TABLE dbo.TBL_FIRST
(
FIRST_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_FIRST_FIRST_ID PRIMARY KEY CLUSTERED
,SNO INT NOT NULL
,LAST_NAME VARCHAR(50) NOT NULL
,FIRST_NAME VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.TBL_SECOND
(
SECOND_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SECOND_SECOND_ID PRIMARY KEY CLUSTERED
,FIRST_ID INT NOT NULL
,EMP_ID INT NOT NULL
,EMP_SAL NUMERIC(12,0) NOT NULL
);
;WITH SAMPLE_DATA(sno, name, fname, empid, epsal) AS
( SELECT * FROM (VALUES
(1, 'raju' , 'ravi' , 123 , 40000 )
,(2, 'Poo' , 'Winnie' , 124 , 25000 )
,(3, 'Robin', 'Christopher' , 125 , 45000 )
,(4, 'Tiger', 'Tigger' , 126 , 17800 )
) AS X(sno, name, fname, empid, epsal)
)
INSERT INTO dbo.TBL_SECOND (FIRST_ID,EMP_ID,EMP_SAL)
SELECT
X.FIRST_ID
,X.empid
,X.epsal
FROM
(
MERGE dbo.TBL_FIRST AS DST
USING
(
SELECT
SD.sno
,SD.name
,SD.fname
,SD.empid
,SD.epsal
FROM SAMPLE_DATA SD
) AS SRC
ON SRC.sno = DST.SNO
WHEN NOT MATCHED
THEN INSERT ( SNO, LAST_NAME, FIRST_NAME )
VALUES ( sno, name, fname)
OUTPUT inserted.FIRST_ID
,SRC.empid
,SRC.epsal
) AS X;
SELECT
*
FROM dbo.TBL_FIRST F
INNER JOIN dbo.TBL_SECOND S
ON F.FIRST_ID = S.FIRST_ID;
Results
FIRST_ID SNO LAST_NAME FIRST_NAME SECOND_ID FIRST_ID EMP_ID EMP_SAL----------- ----------- ----------- -------------- ----------- ----------- ----------- --------
1 1 raju ravi 1 1 123 40000
2 2 Poo Winnie 2 2 124 25000
3 3 Robin Christopher 3 3 125 45000
4 4 Tiger Tigger 4 4 126 17800
Execution Plan
-
Eirikur Eiriksson (1/4/2015)
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insertFind it hard to imagine a more efficient way for this, consider the sample code below
That's true but MERGE still scares the hell out of me because of all the problems reported with it since its inception. I also just have the possibly insane personal preference to use two separate inserts, in most cases, just for "separation of tasks" for troubleshooting purposes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insertFind it hard to imagine a more efficient way for this, consider the sample code below
That's true but MERGE still scares the hell out of me because of all the problems reported with it since its inception. I also just have the possibly insane personal preference to use two separate inserts, in most cases, just for "separation of tasks" for troubleshooting purposes.
It hasn't been without problems but one gets used to it, kind of like an old car that steers to the right when you accelerate and the left when you brake. Otherwise it drives fine;-)
😎
-
Eirikur Eiriksson (1/4/2015)
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insertFind it hard to imagine a more efficient way for this, consider the sample code below
That's true but MERGE still scares the hell out of me because of all the problems reported with it since its inception. I also just have the possibly insane personal preference to use two separate inserts, in most cases, just for "separation of tasks" for troubleshooting purposes.
It hasn't been without problems but one gets used to it, kind of like an old car that steers to the right when you accelerate and the left when you brake. Otherwise it drives fine;-)
😎
Heh... strange analogy. MERGE isn't exactly the "old car" of SQL Server. To continue the analogy, MERGE is (or at least was) a shinny new car with lot's of showroom appeal that had all the problems that you just mentioned. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insertFind it hard to imagine a more efficient way for this, consider the sample code below
That's true but MERGE still scares the hell out of me because of all the problems reported with it since its inception. I also just have the possibly insane personal preference to use two separate inserts, in most cases, just for "separation of tasks" for troubleshooting purposes.
It hasn't been without problems but one gets used to it, kind of like an old car that steers to the right when you accelerate and the left when you brake. Otherwise it drives fine;-)
😎
Heh... strange analogy. MERGE isn't exactly the "old car" of SQL Server. To continue the analogy, MERGE is (or at least was) a shinny new car with lot's of showroom appeal that had all the problems that you just mentioned. 😀
Being a model 2003 SQL standard, it may not be a rusty heap but it's not new and shiny, maybe the dealer snake oiled it before placing it in the showroom.
😎
-
Eirikur Eiriksson (1/4/2015)
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
Jeff Moden (1/4/2015)
Eirikur Eiriksson (1/4/2015)
The MERGE does produce an optimal plan for the operation, single scan of both the source table and the first destination table, one merge operation on the first table and insert on the second. As the output clause in the merge statement allows for output outside the inserted pseudo table, no joins are needed for the second insertFind it hard to imagine a more efficient way for this, consider the sample code below
That's true but MERGE still scares the hell out of me because of all the problems reported with it since its inception. I also just have the possibly insane personal preference to use two separate inserts, in most cases, just for "separation of tasks" for troubleshooting purposes.
It hasn't been without problems but one gets used to it, kind of like an old car that steers to the right when you accelerate and the left when you brake. Otherwise it drives fine;-)
😎
Heh... strange analogy. MERGE isn't exactly the "old car" of SQL Server. To continue the analogy, MERGE is (or at least was) a shinny new car with lot's of showroom appeal that had all the problems that you just mentioned. 😀
Being a model 2003 SQL standard, it may not be a rusty heap but it's not new and shiny, maybe the dealer snake oiled it before placing it in the showroom.
😎
May be a 2003 SQL Standard, but it was introduced in SQL Server 2008.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply