SQL SYNTAX Question

What error do you get?

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

Cannot insert explicit value for identity column in table  when IDENTITY_INSERT is set to OFF.

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

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
 

Select allOpen in new window

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

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)

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.

@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)

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

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.

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

Select allOpen in new window

if possible post complete script of your existing procedure.

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'' ')

@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

@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

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

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