Avatar of parpaa
parpaa
 asked on

SQL SYNTAX Question

Hi,

I am trying to do a simple..

Select * into @table_varible from table A

Table A has an identity column and the insert fails when i execute the above command.. so is there any way i can enable the identity insert on the @table_varible before inserting..
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
DavidInLove

8/22/2022 - Mon
Ioannis Paraskevopoulos

What error do you get?
Scott Pletcher

Not that I know of, for a table variable.

But you can add a separate identity column to @table_varible to give it its own identity column, separate from tableA's.


DECLARE @table_variable TABLE (
    ident2 int IDENTITY(1, 1) NOT NULL, --@table_variable's own table identity
    id int NOT NULL, --identify from tableA
    col2 ..., --all other columns from tableA
    ... --...
    )

Select * into @table_variable from tableA
parpaa

ASKER
Cannot insert explicit value for identity column in table  when IDENTITY_INSERT is set to OFF.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
parpaa

ASKER
@scott - The idea behind "select * into " was to create the table and insert as part of the same statement itself. And i dont want the identity insert otpion on @table_variable.. I am trying to ignore the create table before insert if possible..
Sharath S

You cannot "INSERT INTO" a table variable. You need to use a temp table for "INSERT INTO". You can insert data into temp table even though the original table has identity column.
 Check this small test
 
declare @table table(id int identity ,col int)
insert @table values (91),(10)
select * into #tmp from @table
select * from #tmp
 

Open in new window

DavidInLove

Hi parpaa,

Try that

insert into @table_varible
select
max(id_table_varible)+1,[all columns from table A]
from table A


Happy to hear from you.
If you have problem send to myself the create script of your tables
and I'll do it!


best Regards
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
parpaa

ASKER
Here is the part of query i am writing..

declare @tablename            nvarchar(60),      --table to archive
      @howold                  datetime,                  --how old does the data have to be
      @sql                  nvarchar(1000),      --text holder
      @year                  nvarchar(10),            --year that is being archived                        
      @oldest                  datetime,            --oldest date available to archive
      @limit                  datetime,
      @max                  datetime



set @oldest = ( SELECT convert(varchar(10),min(contact_received_date),101) from contact.dbo.contact_base (nolock))


set @howold = (Select DATEADD(dd, DATEDIFF(dd, 0, max(contact_received_date)), -365) from contact.dbo.contact_base (nolock))


--While @oldest < @howold
While @oldest < '01/30/2012'

begin

      select @year = (select datepart(yyyy,@oldest))

      SELECT @tablename = 'Contact_base_archive_' + @year

begin transaction

      IF OBJECT_ID(@tablename) IS NULL
            begin

        CREATE   @tablename  (
        contact_id     int   NOT NULL,
        root_system     tinyint   NOT NULL,
        root_contact_id     varchar  (30) NULL,
        contact_received_date     datetime   NULL,
        contact_type     tinyint   NULL,
        service_level     tinyint   NULL,
        emp_id     varchar  (50) NULL,
        agent_id     varchar  (50) NULL,
        center     tinyint   NULL,
        outcome     smallint   NULL,
        resolved     tinyint   NULL,
        contact_resolved_date     datetime   NULL,
        resolution_time     int   NULL,
        wait_to_work     int   NULL,
        work_time     int   NULL,
        last_action_time     datetime   NULL,
        queueid     int   NULL,
        queuename     varchar  (100) NULL,
        last_event_id     smallint   NULL,
        language     int   NULL,
        country     int   NULL,
        area_id     int   NULL,
        silo_id     int   NULL,
        dept_id     int   NULL,
        accountNumber     varchar  (50) NULL,
        emailaddress     varchar  (50) NULL,
        track_num     int   NULL,
        ext_type     int   NULL,
        lastedited     datetime   NULL,
        lastedit     tinyint   NULL,
        system_center     tinyint   NULL,
        phone_dest_group     int   NULL,
        to_node     tinyint   NULL,
        from_node     tinyint   NULL
) ON   DATA  

            set @sql = ('Insert into ' + @tablename + ' select * from contact.dbo.contact_base (nolock) where contact_received_date < ''@oldest+1'' ')

            exec(@sql)
ASKER CERTIFIED SOLUTION
appari

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DavidInLove

Here is the most important part, I believe, as far as concerned. BUT can you just give me the structure of the  TABLE contact.dbo.contact_base
Is there an auto indent (id increased always by one automatic. Are there exactly the same fields,
contact_id     int   NOT NULL, is not auto indent

 set @sql = ('Insert into ' + @tablename + ' select * from contact.dbo.contact_base (nolock) where contact_received_date < ''@oldest+1'' ')

Please send the script creation of the table contact.dbo.contact_base  and I'll see that.
parpaa

ASKER
@appari - i followed as you suggested below but gettng different error this time..
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

@davudinlove - the above script itself is create script.. i have pasted below the complete sql.. i didnt change the datatype but only removed the 'IDENTITY' from the original table..

declare @tablename            varchar(60),      --table to archive
      @howold                  datetime,                  --how old does the data have to be
      @sql                  nvarchar(1000),      --text holder
      @year                  nvarchar(10),            --year that is being archived                        
      @oldest                  datetime,            --oldest date available to archive
      @limit                  datetime,
      @max                  datetime



set @oldest = ( SELECT convert(varchar(10),min(contact_received_date),101) from contact.dbo.contact_base (nolock))


set @howold = (Select DATEADD(dd, DATEDIFF(dd, 0, max(contact_received_date)), -365) from contact.dbo.contact_base (nolock))


--While @oldest < @howold
While @oldest < '01/30/2012'

begin

      select @year = (select datepart(yyyy,@oldest))

      SELECT @tablename = 'Contact_base_archive_' + @year

begin transaction

IF OBJECT_ID(@tablename) IS NULL
            begin

exec( ' CREATE TABLE ' + @tablename  + ' (
        contact_id     int   NOT NULL,
        root_system     tinyint   NOT NULL,
        root_contact_id     varchar  (30) NULL,
        contact_received_date     datetime   NULL,
        contact_type     tinyint   NULL,
        service_level     tinyint   NULL,
        emp_id     varchar  (50) NULL,
        agent_id     varchar  (50) NULL,
        center     tinyint   NULL,
        outcome     smallint   NULL,
        resolved     tinyint   NULL,
        contact_resolved_date     datetime   NULL,
        resolution_time     int   NULL,
        wait_to_work     int   NULL,
        work_time     int   NULL,
        last_action_time     datetime   NULL,
        queueid     int   NULL,
        queuename     varchar  (100) NULL,
        last_event_id     smallint   NULL,
        language     int   NULL,
        country     int   NULL,
        area_id     int   NULL,
        silo_id     int   NULL,
        dept_id     int   NULL,
        accountNumber     varchar  (50) NULL,
        emailaddress     varchar  (50) NULL,
        track_num     int   NULL,
        ext_type     int   NULL,
        lastedited     datetime   NULL,
        lastedit     tinyint   NULL,
        system_center     tinyint   NULL,
        phone_dest_group     int   NULL,
        to_node     tinyint   NULL,
        from_node     tinyint   NULL
) ON   DATA  ')

            set @sql = ('Insert into ' + @tablename + ' select * from contact.dbo.contact_base (nolock) where contact_received_date < ''@oldest+1'' ')


            exec(@sql)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
parpaa

ASKER
I surpassed that error by taking the creat table statement into  a variable and then executing it..

set @table = ( ' CREATE TABLE ' + @tablename  + ' (
.
.
.
.
.
exec(@table)

But now this error pops up..
 
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'int'.
parpaa

ASKER
I am not sure if i am think it way too complicated or if there is an easy way to achieve this.. let me rephrase my initial request..

I need to copy TABLE B contents into TABLE A (If it exists)

If table A doesnt exist, then i need to create TABLE A without identity and then insert.
Sharath S

<< I need to copy TABLE B contents into TABLE A (If it exists)

If table A doesnt exist, then i need to create TABLE A without identity and then insert.>>

See this
CREATE TABLE #TABLEB ( id INT IDENTITY, col1 VARCHAR(10)); 

INSERT #TABLEB 
VALUES ('1st record'), 
       ('2nd record'); 

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL 
  BEGIN 
      INSERT #TABLEA 
      SELECT col1 
        FROM #TABLEB 
  END 
ELSE 
  BEGIN 
      CREATE TABLE #TABLEA ( col1 VARCHAR(10)) 
      INSERT #TABLEA 
      SELECT col1 
        FROM #TABLEB 
  END
SELECT * FROM #TABLEA

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
appari

if possible post complete script of your existing procedure.
DavidInLove

Hi,
Here is the solution

1/respect the datatype when you create the tables (exactly the same
not varchar and date time for example.
2/ Keep the id auto indent in the original table you want to copy it is necessary to perturbate not what exists.
3/ do the insert select specifying the colums not the select *
set @sql = ('Insert into ' + @tablename + ' (column1,column2,column3... )select

select max(id) +1 from  ' + @tablename + ' ,  column2,column 3,column4... from contact.dbo.contact_base (nolock) where contact_received_date < ''@oldest+1'' ')

You understand?
it must be exactly the same column mostly datatype but the id is not auto indent you do it with select max(id)+ 1


set @sql = ('Insert into ' + @tablename + ' select * from contact.dbo.contact_base (nolock) where contact_received_date < ''@oldest+1'' ')
parpaa

ASKER
@davidinlove - I will follow your instruction and give it a shot now.. But pardon my knowledge, i didnt quiet understand your step 3 on how to handle identity column??

@sharath_123 - Your query would work for me if i knew the table name i am going to create, but i need to create the table dynamically.. thats the tough part..

@appari - attached is the complete sql
sql-contact-archive.txt
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DavidInLove

@davidinlove - I will follow your instruction and give it a shot now.. But pardon my knowledge, i didnt quiet understand your step 3 on how to handle identity column??


You can do that :
put not select *  but  select column 2,columon3.... from Table the identity columon are automaric increased by 1 do you understand put all the columns but not the automatic increased
parpaa

ASKER
Sorry for the late response guys as i was busy with other stuff.. herez my update..

@sharath_123 - Similar approach was already suggested by appari and thats what i am following here and i am getting the "Conversion failed when converting datetime from character string." all the time. This happens when it executes the create table statement..

declare @tablename            varchar(60),      --table to archive
      @howold                  datetime,                  --how old does the data have to be
      @sql                  varchar(1000),      --text holder
      @year                  varchar(4),            --year that is being archived                        
      @oldest                  varchar(15),            --oldest date available to archive
      @limit                  varchar(10),
      @max                  datetime,
      @table                  varchar(4000),
      @table_name            varchar(100),
      @min_date            datetime,
      @max_date            datetime

select @min_date = (select convert(varchar, min(contact_received_date), 101) from contact.dbo.contact_base(nolock))

select @max_date = (select convert(varchar, max(contact_received_date), 101) from contact.dbo.contact_base(nolock))

--select @oldest = (select cast((DATEADD(dd, DATEDIFF(dd, 0, @min_date), 0)) as varchar(15)))

set @howold = (select cast((DATEADD(dd, DATEDIFF(dd, 0, @max_date), -365)) as varchar(20)))

print @min_date
print @howold



While (@min_date < @howold)
--While (@oldest < '01/30/2012')

begin

      set @year =  (select datename(yyyy,@min_Date))
      SET @tablename = ('Contact_base_archive_' +  @year)
print @year
print @tablename

begin transaction

IF OBJECT_ID(@tablename) IS NULL
            begin

set @table = ( ' CREATE TABLE ' + @tablename+ '(

Its failing right at above statement. Any suggestions.


@Davidinlove - I dont want the auto indent on my new table. I just want to dump everthing from my table.. here is my req again which i pasted earlier..

I need to copy TABLE B contents into TABLE A (If it exists)

"If table A doesnt exist, then i need to create TABLE A without identity and then insert. "
DavidInLove

@ parp I need to copy TABLE B contents into TABLE A (If it exists)

Answer from @Davidinlove
TABLE A must be exactly the same TABLE SCRIPT creation as TABLE B BUT take care
to put no auto indentity with auto increment in table A.
Only that.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.