https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/246310&feed=atom&action=history
Microsoft KB Archive/246310 - Revision history
2024-03-28T14:46:08Z
Revision history for this page on the wiki
MediaWiki 1.39.3
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/246310&diff=315806&oldid=prev
X010: Text replacement - ">" to ">"
2020-07-21T08:43:34Z
<p>Text replacement - ">" to ">"</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 08:43, 21 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l127">Line 127:</td>
<td colspan="2" class="diff-lineno">Line 127:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.<br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>To map the columns correctly, on the first row, change the source to <Ignore<del style="font-weight: bold; text-decoration: none;">&gt; </del>and ensure the destination is ID (the identity column). On the second row, change the source from <Ignore<del style="font-weight: bold; text-decoration: none;">&gt; </del>to Desc (the description column) and match it to the Desc column in the destination table. Choose '''OK''', and then press '''Next'''.</li></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>To map the columns correctly, on the first row, change the source to <Ignore<ins style="font-weight: bold; text-decoration: none;">> </ins>and ensure the destination is ID (the identity column). On the second row, change the source from <Ignore<ins style="font-weight: bold; text-decoration: none;">> </ins>to Desc (the description column) and match it to the Desc column in the destination table. Choose '''OK''', and then press '''Next'''.</li></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Choose a name for your DTS package, press '''Next''' and the import process is now complete.</li></ol></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Choose a name for your DTS package, press '''Next''' and the import process is now complete.</li></ol></div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/246310&diff=297159&oldid=prev
X010: Text replacement - "<" to "<"
2020-07-21T08:00:24Z
<p>Text replacement - "<" to "<"</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 08:00, 21 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l127">Line 127:</td>
<td colspan="2" class="diff-lineno">Line 127:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.<br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>To map the columns correctly, on the first row, change the source to <del style="font-weight: bold; text-decoration: none;">&lt;</del>Ignore&gt; and ensure the destination is ID (the identity column). On the second row, change the source from <del style="font-weight: bold; text-decoration: none;">&lt;</del>Ignore&gt; to Desc (the description column) and match it to the Desc column in the destination table. Choose '''OK''', and then press '''Next'''.</li></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>To map the columns correctly, on the first row, change the source to <ins style="font-weight: bold; text-decoration: none;"><</ins>Ignore&gt; and ensure the destination is ID (the identity column). On the second row, change the source from <ins style="font-weight: bold; text-decoration: none;"><</ins>Ignore&gt; to Desc (the description column) and match it to the Desc column in the destination table. Choose '''OK''', and then press '''Next'''.</li></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Choose a name for your DTS package, press '''Next''' and the import process is now complete.</li></ol></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><li>Choose a name for your DTS package, press '''Next''' and the import process is now complete.</li></ol></div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/246310&diff=135270&oldid=prev
3155ffGd: importing KB archive
2020-07-18T16:13:00Z
<p>importing KB archive</p>
<p><b>New page</b></p><div><div id="nsbanner"><br />
<br />
<div id="bannerrow1"><br />
<br />
{|<br />
| Knowledge Base<br />
|<br />
|}<br />
<br />
<br />
</div><br />
<div id="TitleRow"><br />
<br />
= <span id="KB246310"></span>INF: How to Use DTS Wizard to Insert Data into SQL Tables with an Identity Column =<br />
<br />
<br />
</div><br />
<br />
</div><br />
<div id="nstext" valign="BOTTOM"><br />
<br />
Article ID: 246310<br />
<br />
Article Last Modified on 10/27/2000<br />
<br />
<br />
-----<br />
<br />
APPLIES TO<br /><br />
<br /><br />
<br />
* Microsoft SQL Server 7.0 Standard Edition<br />
<br />
<br />
-----<br />
<br />
<div class="notice_section"><br />
<br />
This article was previously published under Q246310<br />
<br />
</div><br />
<div class="summary_section"><br />
<br />
== SUMMARY ==<br />
<br />
The Data Transformation Services (DTS) Wizard provides a method to insert data into a table that contains an identity column from a source, provided the data contains unique integer values and does not violate constraints imposed on the destination table. For more information, see the first example in the More Information section.<br /><br />
<br /><br />
The DTS Wizard also provides a method to insert into a table containing an identity column if the source data '''DOES NOT''' contain the unique identity values. In this scenario, non-identity columns only can be the destination from a DTS source. This can be achieved by modifying the DTS package column mappings and by clearing the identity insert option. For more information, refer to the second example in the More Information section.<br /><br />
<br /><br />
Executing the package directly, in the second example, without first modifying the package column mappings, results in this error:<br /><br />
<br /><br />
DATA TRANSFORMATION SERVICES: Data Pump Exception Log<br />
<div class="errormessage"><br />
<br />
Error Source: Microsoft OLE DB Provider for SQL Server<br /><br />
Error Description:Cannot insert the value NULL into column 'ID', table 'Test.dbo.T2'; column does not allow nulls. INSERT fails.<br />
<br />
</div><br />
<br />
</div><br />
<div class="moreinformation_section"><br />
<br />
== MORE INFORMATION ==<br />
<br />
=== First Example ===<br />
<br />
This example inserts into a table containing an identity column from a DTS data source containing the unique values.<br /><br />
<br /><br />
<br />
<ol><br />
<li>Create a table named T1 in Microsoft Access 97 with these two fields:<br />
<div class="indent"><br />
<br />
<p>Field1 = ID (PK and a datatype of auto number)<br /><br />
Field2 = Desc (Text)<br /><br />
<br /><br />
-- Insert some dummy rows into the Microsoft Access table.<br /><br />
<br /><br />
</p><br />
<br />
</div></li><br />
<li><p>Create a table named T1 in SQL 7.0 as follows:</p><br />
<pre class="codesample">CREATE TABLE [dbo].[T1]<br />
[ID] [int] IDENTITY (1, 1) NOT NULL,<br />
[Desc] [char] (10) NULL<br />
) ON [PRIMARY]<br />
GO<br />
</pre></li><br />
<li>Create the DTS Package to import from the Microsoft Access 97 table.</li><br />
<li>Go to the '''Tools''' menu, choose '''Data Transformation Services''', '''Import Data''', and then select '''Next'''.</li><br />
<li>Choose a Data Source screen. Select Microsoft Access, and fill in the path and name of the Access database that contains table T1.<br /><br />
<br /><br />
Click '''Next''' and choose a destination database in SQL. Click '''Next''' again. Make sure that C'''opy Table(s) from Source Database''' is selected and then choose '''Next'''.<br /><br />
<br /><br />
The DTS Import Wizard screen named '''Select Source Tables''' prompts you to select the source table from Microsoft Access 97 and choose a destination table in SQL named T1.</li><br />
<li>On the same screen press the '''Build ...''' tool in the transform column and make sure the ENABLE IDENTITY INSERT option is selected. This allows you to insert the rows, provided they are unique and are allowed by the constraint or the datatype chosen in the SQL Table. Make sure that '''Append Rows to destination table''' is selected. Choose '''OK''', and then press '''Next'''.</li><br />
<li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li><br />
<li>Choose a name for your DTS package and press '''Next'''. The import process is now complete.</li></ol><br />
<br />
=== Second Example ===<br />
<br />
This example inserts DTS data into non-identity columns only, and allows the SQL Server to automatically populate the identity column with the next value.<br />
<ol><br />
<li>Create a table named T2 in Microsoft Access 97 with 1 field:<br />
<div class="indent"><br />
<br />
<p>Field = Desc (Text)<br /><br />
<br /><br />
-- Insert some dummy rows into the Microsoft Access table.</p><br />
<br />
</div></li><br />
<li><p>Create a table named T2 in SQL 7.0 as follows:</p><br />
<pre class="codesample">CREATE TABLE [dbo].[T2]<br />
[ID] [int] IDENTITY (1, 1) NOT NULL,<br />
[Desc] [char] (10) NULL<br />
) ON [PRIMARY]<br />
GO<br />
</pre></li><br />
<li>Create the DTS Package to import from the Microsoft Access 97 table.</li><br />
<li>Go to the '''Tools''' menu, choose '''Data Transformation Services''', '''Import Data''' and then click '''Next'''. Choose a Data Source screen, choose Microsoft Access, and fill in the path and name of the Access database containing table T2.</li><br />
<li>Choose a Data Source screen. Select Microsoft Access and fill in the path and name of the Access database containing table T1.<br /><br />
<br /><br />
Click '''Next''' and choose a destination database in SQL. Click '''Next''' again. Make sure that '''Copy Table(s) from Source Database''' is selected and then choose '''Next'''.<br /><br />
<br /><br />
The DTS Import Wizard screen named '''Select Source Tables''' prompts you to select the source table from Microsoft Access 97 and choose a destination table in SQL named T2.</li><br />
<li>On the same screen select the '''Build...''' tool in the transform column and make sure the ENABLE IDENTITY INSERT option is NOT selected. This allows SQL Server to insert the rows automatically into the identity column. Make sure that '''Append Rows to destination table''' is selected.</li><br />
<li>The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.<br /><br />
<br /><br />
To map the columns correctly, on the first row, change the source to &lt;Ignore&gt; and ensure the destination is ID (the identity column). On the second row, change the source from &lt;Ignore&gt; to Desc (the description column) and match it to the Desc column in the destination table. Choose '''OK''', and then press '''Next'''.</li><br />
<li>Select '''Run Immediately''' and also select '''Save DTS Package'''. Press '''Next'''.</li><br />
<li>Choose a name for your DTS package, press '''Next''' and the import process is now complete.</li></ol><br />
<br />
<br />
</div><br />
Keywords: kbinfo KB246310<br />
<br />
<div class="footer"><br />
<br />
<br /><br />
<br />
<br />
-----<br />
<br />
[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]<br />
<br />
<span>© Microsoft Corporation. All rights reserved.</span><br />
<br />
<br />
</div><br />
<br />
</div></div>
3155ffGd