June 12, 2012 at 2:39 am
CREATE TABLE [dbo].[abcd](
[Aid] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NULL
) ON [PRIMARY]
insert into abcd (Title) values ('test1')
insert into abcd (Title) values ('test2')
insert into abcd (Title) values ('test3')
insert into abcd (Title) values ('test4')
insert into abcd (Title) values ('test5')
CREATE TABLE [dbo].[efg](
[Fid] [int] IDENTITY(1,1) NOT NULL,
[Aid] [int] NULL,
[Title] [varchar](50) NULL
) ON [PRIMARY]
I want a script to copy the data from abcd to efg table. As you can see Fid is and identity column.
The result for efg should look something like this
Fid Aid Title
1 1 Test1
2 1
3 2 Test2
4 2
5 3 Test3
6 3
7 4 Test4
8 4
Thanks.
June 12, 2012 at 2:41 am
Whats the reason behind having the blank titles in the second rows for each AID?
CREATE TABLE [dbo].[abcd](
[Aid] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NULL
) ON [PRIMARY]
insert into abcd (Title) values ('test1')
insert into abcd (Title) values ('test2')
insert into abcd (Title) values ('test3')
insert into abcd (Title) values ('test4')
insert into abcd (Title) values ('test5')
CREATE TABLE [dbo].[efg](
[Fid] [int] IDENTITY(1,1) NOT NULL,
[Aid] [int] NULL,
[Title] [varchar](50) NULL
) ON [PRIMARY]
insert into efg
select
aid, title
from
abcd
union all
select
aid, ''
from abcd
order by aid, title desc
select * from efg
June 12, 2012 at 2:47 am
I have an application where a user can upload the file to a directory. Coz the requirment of the user was only one upload first. I did the upload in the same table abcd. But not the requirment changed and they need two uploads. So I have created a new table for the uploaded files. It uses scope identity to retrieve the Aid after every insert and insert that Aid into the new table efg. So one Aid can have many Fid. I want to tranfer the data coz I want to put the changes live.
June 12, 2012 at 2:56 am
The following Query should do it for you:
--Creating Tables
CREATE TABLE [dbo].[abcd](
[Aid] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NULL
)
CREATE TABLE [dbo].[efg](
[Fid] [int] IDENTITY(1,1) NOT NULL,
[Aid] [int] NULL,
[Title] [varchar](50) NULL
)
--Inserting Sample Data
insert into abcd (Title) values ('test1')
insert into abcd (Title) values ('test2')
insert into abcd (Title) values ('test3')
insert into abcd (Title) values ('test4')
insert into abcd (Title) values ('test5')
--Inserting into Second Table
Insert Into efg
Select Aid, Title From
(Select * From abcd
union
Select Aid, '' As Title From abcd) As a
Order By Aid, Title Desc
Edit: Oops!!!!....Sorry bout that Anthony. I had opened the thread and had gone for lunch. Came back and posted the query. Didn't see that you had already posted it.
June 12, 2012 at 2:59 am
freecoder (6/12/2012)
I have an application where a user can upload the file to a directory. Coz the requirment of the user was only one upload first. I did the upload in the same table abcd. But not the requirment changed and they need two uploads. So I have created a new table for the uploaded files. It uses scope identity to retrieve the Aid after every insert and insert that Aid into the new table efg. So one Aid can have many Fid. I want to tranfer the data coz I want to put the changes live.
So why insert the blank row at this stage, can you not wait until they upload the second file? What if they never upload a second file, your just wasing storage space.
June 12, 2012 at 3:34 am
freecoder (6/12/2012)
I want a script to copy the data from abcd to efg table. As you can see Fid is and identity column.
If you want to capture all data including the identity value, which is how I've interpreted your request, you'll need to include all columns in the SELECT list from abcd and enable the identity insert option on efg using
SET IDENTITY_INSERT dbo.efg ON
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 12, 2012 at 3:36 am
you are write. I have changed the code now 🙂
June 12, 2012 at 3:46 am
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply