November 5, 2012 at 2:22 pm
Hello everyone, I'm stumped right now. Here is the deal, I need to replicate data from one table to another (basically archiving data) but there is a TON of logic happening before the archiving query (shown below) is called. To give you some background, I am dynamically keeping my local table schemas mirrored to the external tables I am pulling down. Some of these tables have over 500 columns (yes I am getting that warning about exceeding my limit). Either way, the reason I share this is that dyncamic SQL is not going to work, because I can't fit the insert statement into a variable due to the amount of columns.
Now I have performed an sp_columns on BOTH table1 and table2, and they are EXACTLY the same... obviously table 1 has a column that table 2 doesnt (Record_Change_Date) and that column is set as a DateTime. Either way, when I run this, I get the "Conversion failed" error.
Any ideas??
--------- CODE ------------
Declare @changeDate As DateTime
Set @changeDate = GETDATE()
Insert Into [Table1]
Select *, Convert(DateTime, @changeDate) As [Record_Change_Date]
From [Table2]
Where 1=1
And Record_ID NOT IN (Select Record_ID From Table1)
November 5, 2012 at 2:30 pm
Why are you trying to convert a datetime into datetime? isn't that redundant?
November 5, 2012 at 2:34 pm
Yes... I was trying everything possible. I originally didn't do anything with it.. it used to be select *, @changeDate As [Record_Change_Date]
So I tried it just using the var, I tried casting it, I tried converting it. I went to the source table and made sure that every DateTime field in the source table returned 1 when the IsDate() function was called. (which meant getting rid of all the nulls).
So now every datetime field in the source table returns 1 when is checked against the IsDate function.
I have no idea why this is happening, and if this doesn't work, I'll need to rework the whole thing, which I really dont want to do.
November 5, 2012 at 2:48 pm
Hi,
Execute the following on both the tables and paste the output
sp_help 'tableA'
Thanks
Satyen
November 5, 2012 at 2:53 pm
also Please try executing the query without 'where 1=1' statement
Thanks
Satyen
November 5, 2012 at 3:14 pm
LOL I dont know why everyone gets cross-eyed at the 1=1 (it's just a syntax used for easier troubleshooting and it's ignored by the query engine, so no overhead)
I've attached the output of sp_help to this post.
Oh and just FYI... to get you the appropriate sp_help, I went ahead and stuck the results of my select statement into a temp table, so the sp_help results for table 1 was pulled from a temp table.
In essence the file represents this:
Insert Into DestTable
Select T1.*, @changeDate As [Record_Change_Date]
Into ##tempTable
From SourceTable
so sp_help for 3 tables temp, source, and dest tables.
November 5, 2012 at 3:29 pm
Looks like all you need is:
Insert Into [Table1]
Select *, GETDATE()
From [Table2]
Where Record_ID NOT IN (Select Record_ID From Table1)
You could also make your Record_Change_date not null with a default of getdate().
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 3:45 pm
Hi David,
You have an incorrect query thats the reason why I asked for sp_help tables.
1. When you do an insert with record_change_date the end then it means the last column is record_change_date
2. But record_change_date is your 128th column and not the last
3. Hence you will have to specify the query like this
Insert Into [TableB]
Select column1,column2...column 127th,getdate(),column128th...column146th
From [tableA]
And name NOT IN (Select name From TableA)
Regards
Satyen
November 5, 2012 at 3:48 pm
Sean Lange (11/5/2012)
Looks like all you need is:
Insert Into [Table1]
Select *, GETDATE()
From [Table2]
Where Record_ID NOT IN (Select Record_ID From Table1)
You could also make your Record_Change_date not null with a default of getdate().
I dont think I can do this, how would SQL know where "GETDATE()" goes? I have to alias it so it can match it up to the destination schema.
I could use getdate(), I've just always followed a practice of putting into a variable so I can use the same date throughout my process.
November 5, 2012 at 3:50 pm
menon.satyen (11/5/2012)
Hi David,You have an incorrect query thats the reason why I asked for sp_help tables.
1. When you do an insert with record_change_date the end then it means the last column is record_change_date
2. But record_change_date is your 128th column and not the last
3. Hence you will have to specify the query like this
Insert Into [TableB]
Select column1,column2...column 127th,getdate(),column128th...column146th
From [tableA]
And name NOT IN (Select name From TableA)
Regards
Satyen
I really hope that's not true, I figured as long as the schema's match it would work (I even thought I tried this before). If i have to list the columns it's just not going to work. That would kill the ability to dynamically update the schemas.
I'll take what you say into account and realize it just might not be possible.
Thanks
November 5, 2012 at 3:52 pm
David F (11/5/2012)
Sean Lange (11/5/2012)
Looks like all you need is:
Insert Into [Table1]
Select *, GETDATE()
From [Table2]
Where Record_ID NOT IN (Select Record_ID From Table1)
You could also make your Record_Change_date not null with a default of getdate().
I dont think I can do this, how would SQL know where "GETDATE()" goes? I have to alias it so it can match it up to the destination schema.
I could use getdate(), I've just always followed a practice of putting into a variable so I can use the same date throughout my process.
As Satyen said above it seems your columns are out of sequence. I did not download an office document to peruse over 500 columns to see if they were in the same order.
Aliasing a column does not make that value go to a different column in the update. The problem is that your date column is in the middle. That means you are going to have write out all 500 columns in your update, which of course should be done anyway. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 3:54 pm
David F (11/5/2012)
menon.satyen (11/5/2012)
Hi David,You have an incorrect query thats the reason why I asked for sp_help tables.
1. When you do an insert with record_change_date the end then it means the last column is record_change_date
2. But record_change_date is your 128th column and not the last
3. Hence you will have to specify the query like this
Insert Into [TableB]
Select column1,column2...column 127th,getdate(),column128th...column146th
From [tableA]
And name NOT IN (Select name From TableA)
Regards
Satyen
I really hope that's not true, I figured as long as the schema's match it would work (I even thought I tried this before). If i have to list the columns it's just not going to work. That would kill the ability to dynamically update the schemas.
I'll take what you say into account and realize it just might not be possible.
Thanks
That is absolutely true. You should be referencing columns explicitly anyway. Select * should not be used for a number of reasons.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 3:59 pm
Sir,
Your date time column is in the middle and not at the end so your query will not work!!!!!!
I would humbly request you to try the query first as I asked you to do.
Let me know if it works or not?
Regards
Satyen
November 5, 2012 at 4:02 pm
To test what the others have said, you can use this code:
CREATE TABLE TestOne(
col1int,
col2varchar(20),
col3varchar(20),
col4datetime)
CREATE TABLE TestTwo(
col1int,
col2varchar(20),
col3varchar(20))
INSERT INTO TestOne VALUES
(1,'A', 'XYZ', '20121101'),
(2,'B', 'TWa', '20121102'),
(3,'C', 'ASD', '20121103'),
(4,'D', 'POI', '20121104')
INSERT INTO TestTwo
SELECT col1,
col3 AS col3,
col4 AS col2
FROM TestOne
SELECT * FROM TestTwo
It's simple, but it can show you that you shouldn't rely on aliasing the columns, that won't work (but it can help you to read what is supposed to be each column).
The following is a better practice (more work but less headaches)
TRUNCATE TABLE TestTwo
INSERT INTO TestTwo( col1, col3, col2)
SELECT col1,
col3 AS col3,
col4 AS col2
FROM TestOne
SELECT * FROM TestTwo
November 5, 2012 at 4:03 pm
I do appreciate the help...
But I 100% disagree that I "should" explicitly define the columns. I understand the performance benefit but I "should" do whatever I need to do to accomplish my task. And my task is to have my local schema AUTOMATICALLY updated based on schema located in an external object. So I need to dynamically check the remote object schema, add the missing fields into my own schema and then write data to the updated schema. So if I explicitly define the columns that means it will require manual intervention to list out the columns OR I'd need to use Dynamic SQL which will not work because of the number of columns, so I'm left with what I'm currently trying to do.
By the book developers will fail, when the solution requires and outside the box solution.
But I appreciate the information, and I will try to figure another way to do what I'm doing. Hopefully I will not end up that it has to be done manually. thank you all again!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply