copy data from one table to another.

  • 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.

  • 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

  • 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.

  • 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.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • 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.

  • 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" 😉

  • you are write. I have changed the code now 🙂

  • 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